VBA code doesn't replace a string inside FormulaArray

Question

A column in my Excel has a long function in FormulaArray format in the VBA code. My VBA code copies this long array function to the cell in this column.

Since the characters in the array function are more than 255, I broke down the formula string into two pieces as:

StrFormis the string for the whole formula, StrInner1 is the first inner piece of this StrForm and StrInner2 is the second inner piece of StrForm.

tmp1 and tmp2are two temporary parameters, and I replace them with StrInner1 and StrInner2 in the next step.

I don't get any error, this code copies the StrForm successfully to the cell D2. But it doesn't replace the "tmp1" and ;"tmp2" with StrInner1 and StrInner2. Can anyone see what is wrong with this cause I can't...

Sub test()
    Dim StrForm, StrInner1, StrInner2 As String

    StrInner1 = "$A$2=Sheet2!B2;$A$3=Sheet2!B2;$A$4=Sheet2!B2;$A$5=Sheet2!B2;$A$6=Sheet2!B2;$A$7=Sheet2!B2;$A$8=Sheet2!B2"        

    StrInner2 = ";$A$9=Sheet2!B2;$A$10=Sheet2!B2;$A$11=Sheet2!B2;$A$12=Sheet2!B2;$A$13=Sheet2!B2;$A$14=Sheet2!B2;$A$15=Sheet2!B2" 

    StrForm = "=IF(IF(OR(""tmp1"";""tmp2"");Sheet2!A2;"""")=0;"""";IF(OR(""tmp1"";""tmp2"");Sheet2!A2;""""))"

    Sheets("Sheet1").Range("D2").FormulaLocal = StrForm   

    Sheets("Sheet1").Range("D2").FormulaArray = Sheets("Sheet1").Range("D2").Formula
    Sheets("Sheet1").Range("D2").Replace What:="""tmp1""", Replacement:=StrInner1, lookat:=xlPart
    Sheets("Sheet1").Range("D2").Replace What:=";""tmp2""", Replacement:=StrInner2, lookat:=xlPart

    ' StrInner1 and StrInner2 will be next to each other, so I also remove the semicolon between tmp1 and tmp2
End Sub

we have long moved away from 16bit computing. String in VBA should accept more than 256 char :) — Krish
– Krish, Commented Feb 18, 2016 at 16:49
@krishKM when you send the formula with regular Formula, then over 255 character is not problem. but it is something special with FormulaArray, it gives error. Maybe because array function is a multidimensional calculation and just to limit this multidimensional calculation. — user3714330
– user3714330, Commented Feb 18, 2016 at 18:02

BrakNicku · Accepted Answer · 2016-02-18 16:56:33Z

1

Change those two lines:

StrInner1 = "$A$2=Sheet2!B2,$A$3=Sheet2!B2,$A$4=Sheet2!B2,$A$5=Sheet2!B2,$A$6=Sheet2!B2,$A$7=Sheet2!B2,$A$8=Sheet2!B2"

StrInner2 = "$A$9=Sheet2!B2,$A$10=Sheet2!B2,$A$11=Sheet2!B2,$A$12=Sheet2!B2,$A$13=Sheet2!B2,$A$14=Sheet2!B2,$A$15=Sheet2!B2"

You are replacing text in FormulaArray and it is not local version, so comma is always field separator.

Also change:

Sheets("Sheet1").Range("D2").Replace What:="""tmp2""", Replacement:=StrInner2, lookat:=xlPart

Same problem - you are trying to replace ; and it does not exist. Best way to avoid it is to remove it from search pattern and from StrInner2, simply replace the placeholder.

Interesting thing is that when you replace text in FormulaArray and the result is not a valid formula, there is no error message, but FormulaArray is left unchanged

answered Feb 18, 2016 at 16:56

BrakNicku

6,0313 gold badges28 silver badges40 bronze badges

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

1 Comment

user3714330 Over a year ago

thanks a lot :) i am very surprised that why Microsoft developed Excel different for different languages. It should be universal comma or semicolon.

Collectives™ on Stack Overflow

VBA code doesn't replace a string inside FormulaArray

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related