EXCEL VBA: Why is it not possible to call a UDF, which contains anywhere Application.Evaluate(), with Application.Evaluate()?

Question

It took me some time to figure out that it is not possible use the Application.Evaluate() function with a user defined function which contains also Application.Evaluate(). See the code example below.

Public Sub main()
    Dim func$: func = "some_function()"
    Debug.Print Application.Evaluate(func)
End Sub


Public Function some_function() As Variant
    some_function = Application.Evaluate("0.0003")
End Function

This seems to be a VBA bug. However, I appreciate any idea how to handle this aside avoiding Application.Evaluate() within the code of the UDF. Thanks!

Answer 1

You should use Application.Run. Just remove the parenthensis when specifying the function name

Public Sub main()
    Const func = "some_function"
    Debug.Print Application.Run(func)
End Sub

Or, when used as UDF

Public function myUDF()
    Const func = "some_function"
    myUDF = Application.Run(func)
End Sub