Evaluate function for more than 255 characters - Excel/VBA

Question

Crossposted from https://codereview.stackexchange.com/questions/261596/evaluating-strings-with-more-than-255-characters-in-vba-excel

I'd like to ask a question with regard to evaluating strings in VBA.

I am perfectly aware that there is a certain 255 character limitation in evaluating strings in VBA (Excel); similar to this:

Function testfunc2(Rng As Variant)
Dim x As String
x = "1000 * (1 * (0) - 0 * (0)) - 210000000 * (0.4 * (1 * (12 - 0) + 0 * (0 - 0)) - 0) * (1 * (0 - 0) + 0 * (0 - 0)) - 210000000 * (0.554700196225229 * (0.832050294337844 * (0 - 0) + 0.554700196225229 * (0 - 13)) - 0) * (0.832050294337844 * (0 - 0) + 0.554700196225229 * (0 - 0)) - 210000000 * (0.707106781186548 * (0.707106781186548 * (12 - 0) + 0.707106781186547 * (0 - 13)) - 0) * (0.707106781186548 * (0 - 0) + 0.707106781186547 * (0 - 0))"
testfunc2 = Evaluate(x)
End Function

As such, I searched high and low on how to evaluate such cases. I found a solution here: https://codereview.stackexchange.com/questions/236433/evaluating-formulas-greater-than-255-characters

Which contained this code:

Public Function AdvancedEvaluate(ByVal expression As String) As Variant
    With ScriptingSheet.Range("A1")
        .Formula = expression
        AdvancedEvaluate = .Value
        .ClearContents
    End With
End Function

And when I tried changing my code to use the custom function as seen:

Function testfunc2(Rng As Variant)
Dim x As String
x = "1000 * (1 * (0) - 0 * (0)) - 210000000 * (0.4 * (1 * (12 - 0) + 0 * (0 - 0)) - 0) * (1 * (0 - 0) + 0 * (0 - 0)) - 210000000 * (0.554700196225229 * (0.832050294337844 * (0 - 0) + 0.554700196225229 * (0 - 13)) - 0) * (0.832050294337844 * (0 - 0) + 0.554700196225229 * (0 - 0)) - 210000000 * (0.707106781186548 * (0.707106781186548 * (12 - 0) + 0.707106781186547 * (0 - 13)) - 0) * (0.707106781186548 * (0 - 0) + 0.707106781186547 * (0 - 0))"
testfunc2 = AdvancedEvaluate(x)
End Function

It still prints out a #VALUE! error in Excel. I do not know how to contact the person who provided the code, sadly. Also, I am aware that I cannot use the code directly to print out an answer on the spreadsheets; but I also tried this:

Function testfunc2(Rng As Variant)
Dim x As String
Dim y As Variant
x = "1000 * (1 * (0) - 0 * (0)) - 210000000 * (0.4 * (1 * (12 - 0) + 0 * (0 - 0)) - 0) * (1 * (0 - 0) + 0 * (0 - 0)) - 210000000 * (0.554700196225229 * (0.832050294337844 * (0 - 0) + 0.554700196225229 * (0 - 13)) - 0) * (0.832050294337844 * (0 - 0) + 0.554700196225229 * (0 - 0)) - 210000000 * (0.707106781186548 * (0.707106781186548 * (12 - 0) + 0.707106781186547 * (0 - 13)) - 0) * (0.707106781186548 * (0 - 0) + 0.707106781186547 * (0 - 0))"
y = AdvancedEvaluate(x)
testfunc2 = 1 + 1
End Function

And it still prints out a #VALUE! error.

In any case, is there another workaround that can be done in order to evaluate strings more than 255 characters?

Answer 1

This is directed to @Charles Williams as apparently commenting does not allow formatting, but please feel free to comment if I missed something.

@Charles Williams: Ah! This is the answer I have been looking for... I think. So I guess it's futile.

It's because I was trying to do something like this:

Function AdvancedEquation() As Variant
    With ThisWorkbook.Worksheets("ScriptingSheet").Range("A1")
        .Formula = "x ^ 2 + 4 * x + 4"
    End With
End Function

Sub AdvancedEquation2()
    With ThisWorkbook.Worksheets("ScriptingSheet").Range("A1")
        .Formula = "x ^ 2 + 4 * x + 4"
    End With
End Sub

I found it weird that the sub actually printed out the x ^2 + 4 * x + 4 string as I desired but not the function.

If then, is there another method that I can do in order to be able to evaluate strings longer than 255 characters?

Answer 2

You need to add an = on the front of your formula, otherwise its a string.

But if you are trying to use this from a worksheet cell it will not work because UDFs are not generally allowed to modify cells. You need to call AdvancedEvaluate from a SUB not a worksheet function.