定义数组范围的问题

我在为要运行的函数定义的数组的单独工作表上定义范围时遇到问题。该函数是KVLOOKUP并且可以在这里找到。我遇到的问题是，当我尝试在其中设置范围时Sub Searched，它将不接受任何类型的已定义范围。我已经尝试过以各种方式定义范围。不知道我在哪里错了。

要调用的函数

Function KVLOOKUP(LookedValue As String, Matrix As Variant, Column As Integer) As Variant

 Dim Result() As Variant
 Dim i As Integer
 Dim Counter As Long
 Dim Column1 As Integer
 Column1 = Column + 1

 If IsObject(Matrix) Then Matrix = Matrix.Value
 On Error Resume Next
 Do
  i = i + 1
 Counter = UBound(Matrix, i)
 Loop Until Err.Number <> 0

 If Counter < Column Then KVLOOKUP = CVErr(xlErrNum): Exit Function
 Counter = 0

 For i = LBound(Matrix, 1) To UBound(Matrix, 1)
 If Matrix(i, 1) = LookedValue Then
 Counter = Counter + 1
 ReDim Preserve Result(1 To Counter)
 Result(Counter) = Matrix(i, Column) & " - " & Matrix(i, Column1)



 End If

 Next i

 On Error GoTo 0

 If Counter = 0 Then
    KVLOOKUP = CVErr(xlErrNA)
 Else
    KVLOOKUP = Result(1)
    For i = 2 To UBound(Result)
        KVLOOKUP = KVLOOKUP & ", " & Result(i)
    Next i
 End If
End Function

我的问题在下面的代码内。在单元格中，我可以KVLOOKUP像普通函数一样调用=KVLOOKUP(TextToSearch,'IDBHour1'!B2:E120,2)。当我试图通过在VBA中调用函数来定义相同的范围时，我无法定义与上面相同的范围。

子定义E_name和Rnge值

由于某种原因，如果我尝试定义这样的范围（对于普通的VLookup函数将执行此操作），则不会提供任何结果。这就是我调试问题的方式。

当我运行代码时，出现“对象变量或未设置带块变量”错误

Sub SearcherBox()
'E_name is just a name i.e., John Doe
'Rnge is the range in which KVLOOKUP is searching for "John Doe"
 Dim Rnge as Range 
  Rnge = Sheets("IDBHour1").Range("B2:E120")
  Sal = Application.WorksheetFunction.KVLOOKUP(E_name, Rnge, 2)
   MsgBox Sal
End Sub