Excel从单元格调用VBA自定义函数无法正确获取Range及Find失效问题

首次尝试在Excel单元格中调用自行编写的VBA自定义函数时，先后遇到两类运行问题：

• 初始尝试在函数内通过CurrentRegion属性获取目标Range范围，始终返回错误的区域结果
• 调整实现逻辑后，采用传入Range参数的方案，仍存在功能失效问题

初步验证的可行实现方向 #

经多轮测试，初步确认在函数调用时由用户显式传入Range参数是可落地的实现方向，对应的findMatrixValue函数实现如下：

Function findMatrixValue(RowHeader As Variant, _
                     ColHeader As Variant, _
                     rangeAsParam As Variant) _
                     As Variant

'=findMatrixValue($A$17;$D$1;"A1:E20")
'=findMatrixValue($A$17;$D$1; A1:E20)
'=findMatrixValue($A$17;$D$1;Table7[#All])

On Error GoTo Errorhandler:

   Dim myRange As range
   Dim mycellRow, mycellCol As range
   Dim errorMsg As String
   errorMsg = "Value not found in the given range, pls. proove your input parameter"
   
   '初始化默认返回值
   findMatrixValue = errorMsg

    If (TypeName(rangeAsParam) = "String") Then
       Set myRange = range(rangeAsParam)
    End If
    If (TypeName(rangeAsParam) = "Range") Then
       Set myRange = rangeAsParam
    End If

If (Not myRange Is Nothing And TypeName(myRange) = "Range" And RowHeader <> "" And ColHeader <> "") Then
        
        '定位矩阵行列坐标
        Set mycellRow = myRange.find(RowHeader)
        Set mycellCol = myRange.find(ColHeader)
        
        If (Not mycellRow Is Nothing And Not mycellCol Is Nothing) Then
                   Debug.Print "Matrix Coordinates:" _
                        & "Found at Row:" & mycellRow.row _
                        & " and Column: " & mycellCol.Column
                '返回对应坐标单元格的值
                findMatrixValue = Cells(mycellRow.row, mycellCol.Column)
    End If
    End If

Errorhandler:
If (Err.Number <> 0) Then
 '错误时保留初始默认返回值
 End If
End Function

查阅微软官方《Excel自定义函数的局限性说明》内容后，确认文档提及的限制与论坛讨论结论一致，核心诱因判断为自定义函数无法追踪隐式关联的计算范围，这也是部分功能无法正常运行的根本原因。

Range传参方案的现存异常 #

排查过程中发现大量自定义函数存在运行异常，因此单独编写了接收Range参数的测试函数，代码如下：

Function gettingTheRange(rangeAsParam As range) As Integer

 Dim myArr As Variant
 myArr = rangeAsParam.value '测试时数组看似已完成填充

 '实际运行到此处即中断，后续代码全部被忽略
 Dim res As range
 res = rangeAsParam.find("start")

 ' ".find" 方法之后的所有代码块均不会执行

 gettingTheRange = 42
End Function

核心异常现象说明 #

纯VBA编译环境下手动执行测试调用代码，所有逻辑均可正常运行；但直接在Excel单元格内调用自定义函数时，会出现以下异常：

• 看似传入的Range参数已正常赋值，rangeAsParam.value返回的数组实际仅包含当前调用函数的单元格的值，而非预期的完整范围
• 函数内的消息框可正常弹出、简单的测试返回值可正确写入单元格
• 实际需要的完整Range范围获取、范围内字符串查找等核心逻辑，在单元格调用场景下全部失效，但同一逻辑在纯VBA环境下运行完全正常

对应的无参Range获取测试代码如下：

Sub testcall()
 MsgBox gettingTheRange    
End Sub

Function gettingTheRange() As Integer
MsgBox "In the beginning"

Dim ws As Worksheet
Set ws = ListOfAllNames_ALL  '按实际工作表名称调整
Dim myArr As Variant
myArr = ws.Range("A1").CurrentRegion
    
gettingTheRange = 5

MsgBox "At the end..."
End Function

注：所有跳转类外链已移除，相关文档说明仅保留核心结论。

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