Long Array VBA Issue

Question

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.

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
– Wolfie, Commented May 3, 2017 at 22:36
Hi @Wolfie, no, but I have tried it both ways and still end up with the same result. — William C.
– William C., Commented May 3, 2017 at 22:48

A.S.H · Accepted Answer · 2017-05-04 00:42:07Z

2

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.

edited May 4, 2017 at 0:42

answered May 3, 2017 at 22:58

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

William C. Over a year ago

Hi ASH, thanks for the help! The script is running into a small problem where the script is pasting '=SUM(IF(CONCATENATE($C$3,[@Route],[@[Assumed Coating Type]],[@Diameter],[@[Year Installed (Coating)]])="X_X_X",HCA!P$26:P$13642))' in AZ7. It doesn't seem to be replacing the "X_X_X". Any ideas?

William C. Over a year ago

I believe there is a problem with the .Replace function. I've tried a few variations and have come to no success in trying to have the X_X_X replaced.

A.S.H Over a year ago

@WilliamC. the replaced form wont appear in the formula bar until you deselect then select the cell (I have no clue why). Besides, the resulting formula must also be correct, otherwise it wont succeed. It's very hard to debug this formula so I assumed it was correct and tested my code with smaller, "correct" formulas.

A.S.H Over a year ago

@WilliamC. However I am pretty sure about the essence of the issue, you can't just truncate any part of the formula, you should keep it correct from a syntax point of view or Excel wont accept it.

William C. Over a year ago

Hi ASH, thanks for the advice. Yes, the formula is accurate if entered normally. I'll tweak some things in the morning and see if I can get it to work. In the meantime, I'll mark this as answered since you've provided a lot of useful information. Thank you again.

Nikolaos Polygenis · Accepted Answer · 2017-09-20 07:41:47Z

0

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}"

edited Sep 20, 2017 at 7:41

answered Sep 19, 2017 at 12:15

Nikolaos Polygenis

7018 silver badges13 bronze badges

Collectives™ on Stack Overflow

Long Array VBA Issue

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related