8
\$\begingroup\$

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?

\$\endgroup\$

3 Answers 3

11
\$\begingroup\$

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.

\$\endgroup\$
4
  • \$\begingroup\$ Thanks Mat, this is great! And you're right, I totally butchered the verbiage using UDF here. I was knocking my head for hours trying to figure out why my Application.Evaluate was failing! \$\endgroup\$
    – dwirony
    Jan 31, 2020 at 19:22
  • \$\begingroup\$ Do you think this would be a valid use of a Sub taking outArguments ByRef, but not a tryEvaluate Function since we would rather Excel errors were propogated rather than booleanified. \$\endgroup\$
    – Greedo
    Jun 4, 2021 at 8:29
  • \$\begingroup\$ @Greedo given it's side-effecting, that's a yes =) \$\endgroup\$ Jun 4, 2021 at 13:05
  • 1
    \$\begingroup\$ You can get around the bogus sheet by loading the formula into a defined name and the using the EVALUATE function on the defined name. \$\endgroup\$
    – ChrisB
    Mar 23, 2022 at 0:35
3
\$\begingroup\$

You can get around the 255 character limit of the Evaluate function by first loading the formula into a defined name. Then use the Evaluate function on the defined name. This seems cleaner than using a bogus/hidden worksheet. @Mathieu Guindon already posted a working answer and my answer is basically a modification of that answer.

Private Function PrivAdvancedEvaluate(ByVal expression As String) As Variant    
    Const TEMPNAME As String = "THIS_IS_A_TEMP_DEFINED_NAME_FORMULA"
    With Excel.ThisWorkbook.Names.Add(Name:=TEMPNAME, RefersTo:=expression)
        PrivAdvancedEvaluate = Excel.Application.Evaluate("=" & TEMPNAME)
        .Delete
    End With
End Function
\$\endgroup\$
1
  • \$\begingroup\$ Much cleaner indeed! \$\endgroup\$ Mar 23, 2022 at 22:59
0
\$\begingroup\$

A few findings around this solution:

Trying to change the RefersTo property of an existing name gives an error (1004) if the string has more than 255 chars. (But works fine when it's done while adding the name).

One must be careful using the absolute references and explicit worksheet reference in the formula expression that goes into the RefersTo (unless you really want a relative references). Unfortunately the Application.ConvertFormula function (for converting relative to absolute references) also has the 255 char limit.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.