Evaluating formulas greater than 255 characters Recently I was receiving Error 2015 when trying to use Application.Evaluate, then I realized it was because my formula was greater than 255 characters. As a workaround, I came up with this UDF:

Function AdvancedEvaluate(eformula As String)

    ThisWorkbook.Worksheets("Scripting Worksheet").Range("A1").Formula = eformula
    AdvancedEvaluate = ThisWorkbook.Worksheets("Scripting Worksheet").Range("A1").Value
    ThisWorkbook.Worksheets("Scripting Worksheet").Range("A1").ClearContents

End Function


Which I use in place of all of my Application.Evaluate. However, I'm not satisfied with this solution... is my solution sufficient, or is there a better way to evaluate a formula of greater than 255 characters?
The function is implicitly public, takes an implicitly ByRef parameter that has no reason to not be passed ByVal, and returns an implicit Variant that should be explicit.

It's also side-effecting, which makes it unusable as an actual UDF.

ThisWorkbook.Worksheets("Scripting Worksheet") suggests the procedure is using a purposely-made bogus (hidden?) sheet just for that. So why does that sheet need to be dereferenced 3 times in the same scope? Give it a meaningful code name, and use it!

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


Now, that's the exact same logic, just more explicit and unnoticeably more efficient in the handling of object references.

To the extent that the idea is to somehow get Excel's calc engine to do the work, other than getting the Excel devs to lift the 255-char limitation in the object model, I think that's as good as it's going to get. It's not a UDF though: in Excel a User-Defined Function refers to a function that can be invoked from a cell - but this function will only ever return an error value to Excel; VBA code can merrily consume it though.