VBA中使用Evaluate调用自定义函数返回#VALUE!问题求助

错误原因 #

你的代码中Evaluate的用法完全错误：

• 当执行Evaluate(Trim(ConcRange(ws.Range("A1:A1000"))))时，ConcRange会先返回拼接后的字符串（比如"a,b,c"），随后Evaluate会尝试把这个字符串当作Excel公式解析。显然这类字符串不是有效公式，因此返回#VALUE!错误。
• 此外，直接调用VBA自定义函数时不需要借助Evaluate，直接调用即可完成赋值。

修正方案 #

方案1：直接调用自定义函数赋值 #

去掉多余的Evaluate，直接将函数返回值赋值给单元格：

Sub Evaluate_Method()
    Dim ws As Worksheet: Set ws = ActiveSheet
     
    ws.Range("C2").Formula = "=TRIM(ConcRange(A1:A1000))"   ' 正常工作
    
    ' 修正后的代码
    ws.Range("C2").Value = ConcRange(ws.Range("A1:A1000"))
End Sub

注：ConcRange内部已经对每个单元格内容做了Trim处理，且最终结果无前置分隔符，因此不需要额外嵌套Trim。

方案2：正确使用Evaluate（若有需求） #

如果一定要用Evaluate，需要传入完整的工作表公式字符串，让它按公式逻辑解析计算：

Sub Evaluate_Method()
    Dim ws As Worksheet: Set ws = ActiveSheet
     
    ws.Range("C2").Formula = "=TRIM(ConcRange(A1:A1000))"   ' 正常工作
    
    ' 修正后的Evaluate用法
    ws.Range("C2").Value = ws.Evaluate("TRIM(ConcRange(A1:A1000))")
End Sub

这里ws.Evaluate会像Excel解析单元格公式一样执行TRIM(ConcRange(A1:A1000))，返回正确结果。

可选：优化ConcRange函数 #

原函数可做小幅优化，让逻辑更严谨（比如支持多字符分隔符）：

Function ConcRange(rng As Range, Optional Delim As String = ",")
    Dim cel As Range
    ConcRange = "" ' 初始化空字符串，避免初始值为Empty的问题
    
    For Each cel In rng.Cells
        If Trim(cel.Value) <> "" Then
           ConcRange = ConcRange & Delim & Trim(cel.Value)
        End If
    Next cel
    
    ' 移除开头的分隔符，兼容多字符分隔场景
    If Len(ConcRange) > 0 Then
        ConcRange = Mid(ConcRange, Len(Delim) + 1)
    End If
End Function

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