vba - 使用工作表名称作为参数的自定义函数

Question

我有这样的功能:

Function GetLastRowOnSheet(ByVal SheetName As Worksheet) As Long
    On Error Resume Next
    GetLastRowOnSheet = SheetName.Cells.Find(what:="*", after:=SheetName.Cells(1),     searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    On Error GoTo 0
End Function

假设我有一张名为“Sheet1”的工作表，在我的 Excel 工作表中， 我想说 =GetLastRowOnSheet('Sheet1') 或使用 命名范围

我可以使用上面的函数以及包含以下内容的子例程或函数在 vba 中轻松完成此操作:

Dim Sheet1 As Worksheet
Dim LastRow as Long
Set Sheet1 = ThisWorkbook.Sheets("Sheet1")
LastRow = GetLastRowOnSheet(Sheet1)

' last row then returns the last filled in cell on the sheet

想法？

Answer 1

您将需要改用此代码:

Function GetLastRowOnSheet(ByVal SheetName As String) As Long
    Dim wks As Worksheet

    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(SheetName)
    GetLastRowOnSheet = wks.Cells.Find(what:="*", after:=wks.Cells(1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    On Error GoTo 0
End Function

我不是 100% 确定，但现在，我非常怀疑是否可以将 Worksheet-Object 作为 Worksheet-Function-Parameter 传递。这就是我改用字符串的原因。

因为您使用的是 Resume Next ，您不需要检查工作表是否确实存在，但如果不存在，则必须这样做。

但是，您现在可以轻松地使用 NAMED-Range，只要它引用 Worksheet-Name。

编辑

好的，找到了一种更好的方法来执行此操作，因为动态获取工作表名称作为此工作表功能的输入会很痛苦。没有内置函数可以直接执行此操作 - 至少我找不到。 Cell("address")将是最接近的。

Function GetLastRowOnSheet(ByVal SheetName As Range) As Long    
    On Error Resume Next
    With SheetName.Worksheet
      GetLastRowOnSheet = .Cells.Find(what:="*", after:=.Cells(1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    end with
    On Error GoTo 0
End Function

现在您可以使用 GetLastRowOnSheet(SheetXY!A1)或 GetLastRowOnSheet(NAMEDRANGE) ，这很容易，并且已经对错误输入提供了一些保护。

要将它与 VBA 一起使用，您可以像这样使用它:

Dim LastRow as Long
LastRow = GetLastRowOnSheet(ThisWorkbook.Sheets("Sheet1").Cells)