为何无法用Application.Evaluate()调用含该方法的VBA自定义函数？

你遇到的是VBA中一个已知的限制：当通过Application.Evaluate()调用内部同样包含Application.Evaluate()的自定义函数（UDF）时，嵌套的Evaluate会失效，导致UDF无法返回预期结果。你的示例代码复现了这个问题：

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

以下是几种无需完全避免在UDF中使用表达式求值的解决方法：

方法1：使用指定工作表的Evaluate替代全局Application.Evaluate #

在UDF内部切换为调用具体工作表的Evaluate方法，而非全局的Application.Evaluate，可以规避嵌套调用的冲突：

Public Function some_function() As Variant
    ' 指定任意存在的工作表（比如Sheet1）来执行Evaluate
    some_function = ThisWorkbook.Worksheets("Sheet1").Evaluate("0.0003")
End Function

方法2：使用ExecuteExcel4Macro执行表达式 #

ExecuteExcel4Macro是VBA中调用Excel 4.0宏函数的接口，它可以独立于Application.Evaluate执行表达式，不会触发嵌套冲突：

Public Function some_function() As Variant
    some_function = ExecuteExcel4Macro("0.0003")
End Function

这个方法支持大多数Excel公式表达式，兼容性较好。

方法3：直接转换/计算简单表达式 #

如果你的表达式只是简单的数值或基础运算，可以直接用VBA原生函数替代Evaluate，效率更高：

Public Function some_function() As Variant
    ' 对于数值字符串直接转换
    some_function = CDbl("0.0003")
    ' 对于简单公式，比如"2+3"可以直接写2+3，或者用Val等函数
End Function

内容的提问来源于stack exchange，提问作者Johannes Lanzersdorfer