excel - 我无法通过从 Excel 中的单元格调用 vba 函数来获得正确的范围（使用 currentRegion）

Question

更新2：解决方案是在函数调用中从用户那里获取范围

    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"
       
       'if any error occured, initially set
       findMatrixValue = errorMsg
            
            
        If (TypeName(rangeAsParam) = "String") Then
           Set myRange = range(rangeAsParam)
        End If
        If (TypeName(rangeAsParam) = "Range") Then
           Set myRange = rangeAsParam
        End If
        
'        Debug.Assert Not myRange Is Nothing
'        Debug.Assert TypeName(myRange) = "Range"
'        Debug.Assert RowHeader <> ""
'        Debug.Assert ColHeader <> ""
 
    If (Not myRange Is Nothing And TypeName(myRange) = "Range" And RowHeader <> "" And ColHeader <> "") Then
            
            'Find matrix coordinates
            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
                    'set matrix coordinates
                    findMatrixValue = Cells(mycellRow.row, mycellCol.Column)
        End If
        End If

Errorhandler:
    If (Err.Number <> 0) Then
     End If
End Function

发现微软的文档....这证实了下面的评论

我认为约翰提到的“无法追踪的隐藏连接”问题很重要，要理解为什么有些事情不起作用，这似乎是一个很好的理由。

更新：

由于许多函数似乎不起作用，我现在正试图获取参数内的范围：

 Function gettingTheRange(rangeAsParam As range) As Integer

   Dim myArr As Variant
   myArr = rangeAsParam.value 'The array is filled!

   'What is not working... as mentioned in the comments, is e.g.

   Dim res As range
   res = rangeAsParam.find("start")

   'All code after ".find" is being ignored

   gettingTheRange = 42

End Function

---

我正在（第一次）在 Excel 单元格中使用我自己用 VBA 编写的函数的函数调用。

问题：
下面从 VBA 中的 testcall 工作正常，但是从单元格调用“gettingTheRange”有问题，无法识别范围，您可以看到从范围复制的数组仅包含单元格的值。

消息框即将出现，并且测试值已正确写入单元格。但我真正需要的是范围，因为在我要构建的函数中，我想在范围内找到一个字符串。这在 VBA 中工作已经很好了，但是从单元格调用它会导致我在这里解决的问题....

 Sub testcall()
    MsgBox gettingTheRange    
End Sub

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

Dim ws As Worksheet
Set ws = ListOfAllNames_ALL  'just adjust
Dim myArr As Variant
myArr = ws.Range("A1").CurrentRegion
    
gettingTheRange = 5

MsgBox "At the end..."

结束功能

标签： excelvba