Trick to escape 255 char limitation of Range.FormulaArray property

[rdilipk]

I was in the process of figuring out some trick to make range's FormulaArray property accept strings >255 chars. Along the way in the interwebs, I came across a link which stated one can indirectly accomplish that by setting a named constant with a value >255 chars (this is apparently possible by concatenating two strings instead of having one long string literal >255 chars) and then pointing the formulaArray at the named constant. In effect:


Names.Add Name:="SOMENAME", RefersToR1C1:="=""" & String(255, "A") & """&""" & String(255, "B") & """"


This seems to work because of the presence of the embedded """&""" character in the middle (it reports the length of the RefersToR1C1 property as 516). I guess it tells the property that I am actually trying to concatenate 2 strings together? I don't quite understand it.


I set out to test this hypothesis (i.e I wanted to first confirm that such concatenations are treated differently by properties not accepting >255 chars) by doing something like this:


Const part1 As String = "{9,9;9,9;"
Const part2 As String = "5,6;7,8}"


Names("SOMENAME").RefersToR1C1 = "=""" & part1 & """&""" & part2 & """"


Set formula_range = ActiveWorkbook.ActiveSheet.Range("D4").Resize(4, 2)
formula_range.FormulaArray = Names("SOMENAME").RefersToR1C1


This works in a way that I wasn't quite expecting. I get a single long string in all 4 rows and 2 columns like this:


{9,9;9,9;5,6;7,8} {9,9;9,9;5,6;7,8}
{9,9;9,9;5,6;7,8} {9,9;9,9;5,6;7,8}
{9,9;9,9;5,6;7,8} {9,9;9,9;5,6;7,8}
{9,9;9,9;5,6;7,8} {9,9;9,9;5,6;7,8}


I wanted to see:


9 9
9 9
5 6
7 8


had I set the FormulaArray property to:
formula_range.FormulaArray = "={9,9;9,9;5,6;7,8}"
I would have got the right output.


How can I do what I want while still using the concatenation trick? Remember, the only reason I am down this road is, in real life, I would need to split a long string into several parts (like part1, part2 above) and tie them together via concatenation to escape the 255 char limitation.

 

[pgc01]

Hi

I posted an explanation of how to write an array formula with more than 255 characters into a cell in vba here:

http://www.mrexcel.com/forum/excel-...array-formulas-visual-basic-applications.html

Hope it helps

 

[rdilipk]

Hi pgc01

Thank you very much. I was aware of this approach and did read through it completely. The approach could probably be the same but could you take one more look and let me know if it will work for my specific case? i.e Given a range:

Range r = Range("A1").Resize(4, 2)

If I have a string like this:

output = "{1,2;3,4;5,6;7,8}"

I know doing:
r.FormulaArray = "{ + output + "}"

But if output > 255 chars, it appears the partial replaceable dummy placeholder must respect the range's dimensions. IOW, I have to do:

With r
.FormulaArray = "={1,2;repl1,repl2}"
.Replace "repl1,repl2", "3,4,repl3,repl4"
.Replace "repl3,repl4", "5,6}"
End With

Is this what you were suggesting?

 

[rdilipk]

A small correction. The example I posted should be:

With r
.FormulaArray = "={1,2;repl1,repl2}"
.Replace "repl1,repl2", "3,4;repl3,repl4"
.Replace "repl3,repl4", "5,6}"
End With

 

[pgc01]

Hi

Yes, the idea is right. The implementation is, however, wrong.

As I wrote, each intermediate formula must respect the excel formula syntax. The easiest way to test it is simply to write it directly into a cell.
In this case, if you write in a cell

={1,2;repl1,repl2}

it will not accept it, because the elements of an array must be constants and repl1 or repl2 are not known.

You could, however, use the same logic you used but use valid constants, for ex. 99999, or "a". These arrays would be accepted in a formula

={1,2;99999,99999}
={1,2;"a","a"}

If you use it in your code, you'll see that it works, for ex.:

Sub Test1()
Dim r As Range

Set r = Range("A1:B3")

With r
    .FormulaArray = "={1,2;99999,99999}"
    .Replace "99999,99999", "3,4;99999,99999"
    .Replace "99999,99999", "5,6"
End With
End Sub

or, for ex., replacing the closing curly bracket to expand the array:

Sub Test2()
Dim r As Range

Set r = Range("E1:F5")

With r
    .FormulaArray = "={1,2}"
    .Replace "}", ";3,4}"
    .Replace "}", ";5,6;7,8;9,10}"
End With
End Sub

There are, as you see, many possibilities. The important is to make sure that after each step the cell has a formula that respects the syntax.

 

[shg]

I have a VBA function that inserts long array formulas if you prefer.

 

[rdilipk]

If I prefer? shg: If you were anywhere near where I work I'd buy you lunch Sure, I would like to see it.

 

[rdilipk]

Hi pgc01


Thanks very much for confirming. Much appreciated. I have one last question.. I was recently told that its possible to construct a string like this, so they give the impression of being concatenated. That is given something like:


Const part1 As String = "1,2;"
Const part2 As String = "3,4;5,6;7,8"


One can do:


dim s as string
s = """" & part1 & """&""" & part2 & """"


range.FormulaArray = "={" & s & "}"


The above doesn't seem to work. Am I making some syntactic mistake somewhere or is the concept not workable?

 

[pgc01]

Hi

It's a problem of syntax.

Try:

Sub Test3()
Dim s As String

Const part1 As String = "1,2;"
Const part2 As String = "3,4;5,6;7,8"

s = part1 & part2
Range("A1:B4").FormulaArray = "={" & s & "}"

End Sub

 

[pgc01]

P. S. Notice that this last question has nothing to do with the 255 character limit of the previous ones.