Excel VBA自定义函数使用Dim时返回#VALUE!的原因

你遇到的问题核心是VBA中对象类型变量（如Range）赋值必须使用Set关键字，直接赋值会触发运行时错误，导致自定义函数返回#VALUE!。

错误代码分析 #

第一个出错代码

Public Function DisplayCaller() As String
    Application.Volatile
    Row = Application.Caller.Row
    Column = Application.Caller.Column
    Dim x As Range
    x = Cells(Row, Column - 1)
    DisplayCaller = x.Value2
End Function

这里x是Range对象变量，直接用x = ...试图赋值对象引用时，VBA会默认尝试给x的默认属性（即Value）赋值，但Cells(Row, Column -1)是Range对象，无法直接赋值给Value属性，触发错误，函数返回#VALUE!。

第二个出错代码

Public Function DisplayCaller() As String
    Application.Volatile
    Dim r as Range
    r = Application.Caller
    Row =  r.Row
    Column = r.Column
    DisplayCaller = Cells(Row, Column - 1).Value2
End Function

r是Range对象，Application.Caller返回的是调用函数的单元格Range对象，必须用Set r = Application.Caller才能正确将对象引用赋值给r，直接赋值会触发错误，导致后续代码无法执行，函数返回#VALUE!。

修正后的代码 #

第一个代码修正版

Public Function DisplayCaller() As String
    Application.Volatile
    Dim Row As Long, Column As Long
    Row = Application.Caller.Row
    Column = Application.Caller.Column
    Dim x As Range
    Set x = Cells(Row, Column - 1) ' 添加Set关键字
    DisplayCaller = x.Value2
End Function

第二个代码修正版

Public Function DisplayCaller() As String
    Application.Volatile
    Dim r As Range
    Set r = Application.Caller ' 添加Set关键字
    Dim Row As Long, Column As Long
    Row = r.Row
    Column = r.Column
    DisplayCaller = Cells(Row, Column - 1).Value2
End Function

额外建议：给Row和Column加上Long类型声明，避免Variant类型的潜在问题，让代码更规范。

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