作为可选参数的范围应默认为调用单元格的完整工作表.怎么做?

Question

我写了一个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 的当前状态。只要看看如果 r 什么都不是部分：

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 部分工作正常。

Answer 1

感谢所有贡献者。

特别感谢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

Answer 2

在工作簿模块上写下以下内容：

Option Explicit
Private Sub Workbook_Activate()
With Application
.Iteration = True
.MaxIterations = 1000
.MaxChange = 0.001
End With
End Sub
Private Sub Workbook_Deactivate()
Application.Iteration = False
End Sub

并将其添加到您的函数中：

Option Explicit
Public Function hrLastRow(Optional r As Range = Nothing) As Long
If Application.Iteration = False Then End
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

这样，迭代计算将仅在此工作簿上激活，如果未激活，则不会重新计算函数。