vba byref 参数引用不可用

Question

鉴于被调用的过程实际上设置了传递的 Range 对象 ByRef，我希望该对象引用在调用过程中可用。但仍然什么都没有。

解决办法是什么？

调用

Private Sub Specs_TryGetRangeFromDefinedName_CanFindGlobalName()

Dim Specs As New SpecSuite
Dim rngResult As Excel.range
Dim bResult As Boolean

bResult = TryGetRangeFromDefinedName(rngResult, "LongTermTaxRate", ThisWorkbook.Name)
With Specs.It("should return refersTo range when the name is global")
    .Expect(bResult).ToEqual True
    .Expect(rngResult.address).ToEqual "$B$2" ** FAIL (object is Nothing)2
End With

SpecRunner.RunSuite Specs

End Sub

已调用

Public Function TryGetRangeFromDefinedName(ByRef aRange As Excel.range, _
                                       ByRef aName As String, _
                                       ByRef aWkbName As String, _
                              Optional ByRef aSheetName As String = vbNullString) As Boolean

Dim rngResult As Excel.range

If IsValued(aName) And IsValued(aWkbName) Then
    On Error Resume Next
    If IsValued(aSheetName) Then
        ' local name (presumably)
        Set rngResult = Workbooks(aWkbName).Worksheets(aSheetName).range(aName)
    Else
        ' global name (presumably)
        Set rngResult = Workbooks(aWkbName).Names(aName).RefersToRange
    End If
    TryGetRangeFromDefinedName = (err.Number = 0)
    On Error GoTo 0
End If

End Function

Answer 1

参数aRange从未被使用，rngResult被赋值但从未被引用。摆脱 rngResult 并分配 aRange 代替。

此外，如果通过 ByVal 传递 String 参数，则方法的意图会更清晰，因为它们未分配。

<小时/>

_{这些观察结果都是Rubberduck代码检查结果(免责声明:我共同拥有这个项目)。}