Long Array VBA Issue

3
votes

I'm trying to enter a long-array Formula into VBA that is >255 characters. I have followed past suggestions to dim both halves of the formula and merge them later. I am still having errors getting the array to function properly and was hoping someone could help review the code.

Here's the original code that exceeds the character limit I'm trying to get working:

Sub TestMacro()

Range("AZ7").Select
Selection.FormulaArray = _
    "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],[@Diameter],[@[Year Installed (Coating)]])=CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88]),HCA!R26C[-36]:R13642C[-36]))"

End Sub

Here is my latest attempt to split the code in half following past advice: https://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Sub LongArrayFormula()
 Dim theFormulaPart1 As String
 Dim theFormulaPart2 As String
 theFormulaPart1 = "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],[@Diameter],[@[Year Installed (Coating)]])""X_X_X)"")"

 theFormulaPart2 = "=CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88]),HCA!R26C[-36]:R13642C[-36]))"

 With ActiveSheet.Range("AZ7")
     .FormulaArray = theFormulaPart1
    .Replace """X_X_X)"")", theFormulaPart2

End With

Any help is appreciated, thanks.

2
arraysvbaexcel
Do you really want to have an equals at the start of theFormulaPart2? Seems like it would error having 2 equals signs in the concatenated formula... – Wolfie
Hi @Wolfie, no, but I have tried it both ways and still end up with the same result. – William C.

2 Answers

2
votes

You should keep your truncated formula syntactically correct. Try it like this:

theFormulaPart1 = "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],
[@Diameter],[@[Year Installed (Coating)]])=X_X_X,HCA!R26C[-36]:R13642C[-36]))"
'                                          ^^^^^
theFormulaPart2 = "CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88])"

With ActiveSheet.Range("AZ7")
  .FormulaArray = theFormulaPart1
  .Replace "X_X_X", theFormulaPart2
End With

Here I inserted X_X_X (could be anything else) in the place of some "closed expression" in the formula. That keeps the truncated formula correct from a syntax point of view, so the statement .FormulaArray = theFormulaPart1 can accept it. Replacement can then proceed in the second step.

0
votes

You can also try this(
please don't run it from the VBE, try to run it from sheets environment. Go to Developer-Macros-Your Macro -Run or Run it from a button or shortcut and it will work without problem):

Range("AZ7").Select
Selection.Formula = _
    "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],[@Diameter],[@[Year Installed (Coating)]])=CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88]),HCA!R26C[-36]:R13642C[-36]))"

SendKeys "{F2}"
SendKeys "^+{ENTER}"