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.

 

[shg]

Sub demo()
  Const sFrm   As String = _
     "=IF(AND(RC[-12]:RC[-1]=""""),""No RoB assessments performed""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""no""),""HIGH""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""High Risk""),""HIGH""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""UNCLEAR""),""UNCLEAR""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""Unclear Risk""),""UNCLEAR"",""LOW"")))))"

  Debug.Print InsertArrayFormula(Range("A1"), sFrm, xlR1C1)
End Sub

Function InsertArrayFormula(r As Range, _
              sFrm As String, _
              iRef As XlReferenceStyle, _
              Optional ByVal sFmt As String = "") As Boolean
  ' shg 2009, 2012

  ' Inserts the A1 or R1C1 array formula sFrm into r
  ' The VBE CANNOT have focus when this runs!

  Dim iRefSav As XlReferenceStyle   ' current ref style
  Dim rSel    As Range              ' current selection


  If r.Worksheet.ProtectContents Then Exit Function
    
  Set rSel = ActiveWindow.RangeSelection
  
  With Application
    iRefSav = .ReferenceStyle
    .ReferenceStyle = iRef
    
    On Error GoTo Oops
    .ScreenUpdating = False

    With r.Areas(1)
      ' Can't put an array formula in cells that are
      ' not either all locked or all unlocked, so ...
      .Locked = .Cells(1).Locked

      ' Cache the number format, set to text, insert formula, restore format
      If Len(sFmt) = 0 Then sFmt = .NumberFormat
      .NumberFormat = "@"
      .Value = sFrm
      .NumberFormat = sFmt

      Application.Goto .Cells
    End With

    DoEvents
    .SendKeys "{F2}^+~"
    DoEvents
    .Goto rSel
    InsertArrayFormula = True

Outtahere:
    .ReferenceStyle = iRefSav
    .ScreenUpdating = True
    Exit Function
  End With

Oops:
  Resume Outtahere
End Function

 

[rdilipk]

pgc

I was told to escape the 255 char limit, any property can be filled strings that appear to have an embedded ampersand character precisely like the one I posted above:

s = """" & part1 & """&""" & part2 & """"

Here part1 and part2 can each be, say, 200 chars.

This actually did work when you try to set the RefersToR1C1 property of a named constant. Usually it allows only 255 chars but writing the way above, I managed to make the property accept a string with length 516.

 

[pgc01]

You mean you tried and it worked?

That is interesting.

Can you post the code where you build the string with more than the 255 characters and the .FormulaArray accepts it?

 

[rdilipk]

Please take a look at the first reply in this link (by Tushar Mehta):
String limit for Range.FormulaArray property

That is what I was referring to.

 

[rdilipk]

I may have miscommunicated. I am only talking about the *concept*. I posted a link just above this reply. If the RefersToR1C1 property of the named constant can be fooled into accepting such a long string by the mere presence of an embedded ampersand, I was wondering why not fool FormulaArray similarly. The code I posted was my attempt to test out that hypothesis.

 

[pgc01]

This is interesting. I cannot test it now but I will test it tonight (GMT) if no one else does it.

 

[rdilipk]

Please do post your findings. I would be very interested.
For the moment, I am just going to go down the multiple Replace route.

 

[pgc01]

I will, but don't put much faith on it. Just by browsing it seem that it's just to replace a big string in a formula, not the formula itself.

 

[pgc01]

Hi

As I wrote yesterday when I skimmed through your link, it's a solution to use a big string inside a formula, not to the formula instead. It means that if you had a formula like

=AVERAGE(IF(A1:A10=" *** String with 300 characters *** ",B1:B10))

you'd have a solution using a named formula that would generate the string.

This is not a solution for the general problem that you posted where you cannot replace part of the expression of the algorithm with a string.

Notice that Tushar wrote this himself, at the end of the second post in your link:

Of course, if your formula itself has > 255 characters, i.e., =MyVeryLongUDF1(...)+MyVeryLongUDF2(...)+... then you might have another set of problems. ;-