How to use in UDF the Evaluate function for an Excel array formula string longer than 255 character

Question

For a time I use an UDF which is similar to LAMDA function, where the input formula is a string. This works always without any issue. Within the UDF the function string is processed with the Excel EVALUATE function and returns the correct result for single and array values too. But now I have a formula which is longer than 255 character natively. Reserch results advise to set an unused cell via the UDF implicitely with the formula, calculate it and obtain the result for further processing. Now this works with the long formulas too, but the issue is that if the formula returns an array (as an array function), the formula should be also an array formula in the cell (curly braces wrapped). This is what I couldn't achieve.

This is the simple working demo code of the trial:

Function one()

Evaluate "two()"
one = "DONE"
End Function

Function two()

a = "=Len(""this is a very very very very very long long long string which must be resolved in a value which is over 255 vcharacter""&" & """ length but i need the total length of the value. My problerm is that I have to do it with a user defined function and cannot separate the length of it. It will works for me? This was my question."")"
Range("a1").Formula = a
Debug.Print a

End Function

On the worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$6" Then
Calculate
End If
End Sub

In the sheet:

A	B	C	D	E
	14	14
	54
	34
	2
	134
	87			=one()

Cell E6 is the UDF caller and cell A1 is for the result. In this case return 315.

A	B	C	D	E
315	14	14
	54
	34
	2
	134
	87			DONE

The issue is if I change the line

Range("a1").Formula = a

to

Range("a1").FormulaArray = a

the code runs without error, but cell A1 remains empty.

How can set the cell formula as an array formula. This is in Excel which use CSE.(Ctrl+Shift+Enter)

Answer 1

At least found a workaround. Within the sheet change event handler place the command which converts the Formula to FormulaArray sg. like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$6" Then
  Calculate
  Range("A1").FormulaArray = Range("A1").Formula
End If
End Sub

After this the cell became an array formula.

This solves that part to set a cell's array formula if the length of it is not longer than 255.

The remaining issue is this (Hope that found an easy way to reveal)

This is the sheet after ENTER cell E6 with the original Change event handler. If I insert the new line the code breaks Runtime error 1004. Unable to set the FormulaArray property of the Range class This how could be resolved. Meanwhile the max. length of a formula in a cell is 8192 (at least in my Excel version). But if I simply click on cell A1 and press CSE the formula immediately change to an array formula (with curly brackets).

I go on searching.

Answer 2

Not sure I completely follow the issue but here's one workaround for "string in formula is too long". Break the string into chunks and concatenate them in the formula.

Sub Tester()
    
    Dim f1 As String, f2 As String, longString As String
    
    longString = "this is a very very very very very long long long string " & _
       " which must be resolved in a value which is over 255 vcharacter" & _
       " length but i need the total length of the value. My problerm is that" & _
       " I have to do it with a user defined function and cannot separate " & _
       "the length of it. It will works for me? This was my question." & _
       String(1000, "x") '## extra long! ##
    
    'fails
    'Range("A1").Formula2 = "=LET(str,""" & longString & """,MID(str,ROW(OFFSET(A1,0,0,LEN(str),1)),1))"
    
    'works"
    Range("A1").Formula2 = "=LET(str,""" & TextSplit(longString) & """,MID(str,ROW(OFFSET(A1,0,0,LEN(str),1)),1))"
    
End Sub

Function TextSplit(txt) As String
    Const MAX_CHUNK As Long = 200
    Dim s As String, i As Long, sep As String
    If Len(txt) > MAX_CHUNK Then
        i = 1
        Do While i < Len(txt)
            s = s & sep & Mid(txt, i, MAX_CHUNK)
            i = i + MAX_CHUNK
            sep = """ & """
        Loop
    End If
    TextSplit = s
End Function

That doesn't directly help with Evaluate but it allows you to place the formula in a cell on the sheet so it can be calculated.

Answer 3

The argument to the EVALUATE function cannot be longer than 255 characters, but the result can be longer. You can replace portions of the argument in the EVALUATE function with defined names that can refer to both ranges and formulas.
Examples: In B1 formula: =REPT("XYZ",100) In B2 formula: =REPT("KLMN",65) I named cells B1 and B2 a and b, and the formulas aa and bb, respectively. Both of the following functions work in a worksheet:

Function MyEval()
    MyEval = Application.ThisCell.Parent.Evaluate("a & b")
End Function
Function MyEval1()
    MyEval1 = Application.ThisCell.Parent.Evaluate("aa & bb")
End Function

In cells B7:B9 and B10:B12 I have placed long texts.
The macro inserts an array formula:

Sub ArrayForm()
    Range("B14:B16").FormulaArray = "=B7:B9 & B10:B12"
End Sub

The EVALUATE function can return an array result:

Function MyEval2()
    MyEval2 = Application.ThisCell.Parent.Evaluate("B7:B9 & B10:B12")
End Function