时间戳

首页 > 解决方案 > 作为可选参数的范围应默认为调用单元格的完整工作表。如何？

excel - 作为可选参数的范围应默认为调用单元格的完整工作表。如何？

问题描述

我写了一个UDF，它以一个范围作为可选参数。然后它计算正在使用的范围的最后一行。如果范围未作为参数传递，则 UDF 应默认为调用单元格的完整工作表。不知何故，直截了当的方法不起作用 - UDF 返回 0。

Public Function hrLastRow(Optional r As Range = Nothing) As Long
    If r Is Nothing Then
        hrLastRow = hrLastRow(ThisWorkbook.Worksheets(Application.Caller.Parent.Name).Cells())
    Else
        If Application.WorksheetFunction.CountA(r) <> 0 Then
            hrLastRow = 1 - r.Rows(1).row + _
                        r.Find(What:="*", _
                            After:=r.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
            hrLastRow = Application.WorksheetFunction.Max(hrLastRow, 0)
        Else
            hrLastRow = 0
        End If
    End If
End Function

正如我所说，如果我在单元格“=hrLastRow()”中调用它，结果为零，尽管到处都有值:) - 所以结果肯定是正数。

也许我没有正确使用 Application.Caller ...？任何帮助将非常感激。还是VBA不允许以某种方式进行递归调用？那么，为什么是零？

PS：在范围内工作正常。

PPS：我刚刚注意到，Excel 会警告调用单元格中的循环引用。也许这是根本问题 - 但是，如何解决它？

更新：所以目标是让这个工作没有副作用，比如迭代计算。有人建议只在 UDF 调用者下方搜索以避免循环引用，这对我来说听起来是一个聪明的想法，简单且目标明确。不知何故，我的代码似乎是错误的，结果刚刚关闭......这是 UDF 的当前状态。看看 If r is nothing 部分：

Public Function hrLastRow(Optional r As Range = Nothing) As Variant
    If r Is Nothing Then
        Dim callerRow   As Long
        Dim callerWS    As Worksheet
        Dim searchRange As Range
        Set callerWS = Application.Caller.Parent
        callerRow = Range(Application.Caller.Address).row
        With callerWS
            Set searchRange = .Range(.Cells(callerRow + 1, 1), .Cells(.UsedRange.row, .UsedRange.column))
            hrLastRow = searchRange.Find(What:="*", _
                            After:=searchRange.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
        End With
    Else
        If Application.WorksheetFunction.CountA(r) <> 0 Then
            hrLastRow = 1 - r.Rows(1).row + _
                        r.Find(What:="*", _
                            After:=r.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
            hrLastRow = Application.WorksheetFunction.Max(hrLastRow, 0)
        Else
            hrLastRow = 0
        End If
    End If
End Function

外部 if 语句的 else 部分工作正常。

标签： excelvba

解决方案

感谢所有贡献者。

特别感谢 BrakNicku 的简单而聪明的想法，即在调用单元格下方进行搜索以避免循环引用。

随意使用下面的代码。这是最终功能，按需要工作：

Public Function hrLastRow(Optional r As Range = Nothing) As Long
    Application.Volatile True
    If r Is Nothing Then
        Dim callerRow   As Long
        Dim callerWS    As Worksheet
        Dim searchRange As Range
        Set callerWS = Application.Caller.Parent
        callerRow = Range(Application.Caller.Address).row
        With callerWS
            Set searchRange = .Range(.Cells(callerRow + 1, 1), .Cells(callerRow + .UsedRange.Rows.Count, .UsedRange.Columns.Count))
            If Application.WorksheetFunction.CountA(searchRange) <> 0 Then
                hrLastRow = searchRange.Find(What:="*", _
                                After:=searchRange.Cells(1, 1), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).row
            Else
                hrLastRow = callerRow
            End If
        End With
    Else
        If Application.WorksheetFunction.CountA(r) <> 0 Then
            hrLastRow = 1 - r.Rows(1).row + _
                        r.Find(What:="*", _
                            After:=r.Cells(1, 1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
            hrLastRow = Application.WorksheetFunction.Max(hrLastRow, 0)
        Else
            hrLastRow = 0
        End If
    End If
End Function

推荐阅读