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

Custom Function using a Sheet name as a parameter

提问by 1dolinski

I have a function like this:

我有一个这样的功能：

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

Lets say I have a sheet called "Sheet1", in my excel sheet, I would like to be able to say =GetLastRowOnSheet('Sheet1')or use a named range

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

I can do this easily in vba using the function above as well as a subroutine or function that includes this:

我可以使用上面的函数以及包含以下内容的子例程或函数在 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

Thoughts?

想法？

回答by Jook

You would need to use this code instead:

您需要改用此代码：

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

I am not 100% sure, but right now, I would highly doubt, that it is possible to pass an Worksheet-Object as a Worksheet-Function-Parameter. That is why I used a string instead.

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

Because you are using Resume Next, you do not need to check if the worksheet actually exists, but you would have to do it, if not.

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

You can now however easily use a NAMED-Range, as long as it refers to a Worksheet-Name.

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

Edit

编辑

Ok, found a nicer way to do this, because it would be a pain to dynamically get a worksheetname as input for this worksheet-function. There are no build-in functions to do this directly - at least I could not find one. Cell("address")would be the nearest to that.

好的，找到了一种更好的方法来执行此操作，因为动态获取工作表名称作为此工作表功能的输入会很痛苦。没有内置函数可以直接执行此操作 - 至少我找不到。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

Now you can use GetLastRowOnSheet(SheetXY!A1)or GetLastRowOnSheet(NAMEDRANGE), which is quite easy and already some protection against false input.

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

And to use it with VBA you could use it like this:

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

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

回答by alkorya

You need to use Variant type instead of Worksheet. Worked for me.

您需要使用 Variant 类型而不是 Worksheet。对我来说有效。

Function GetLastRowOnSheet(SheetName As Variant) 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

回答by K_B

The sheet NAME isn't the sheet OBJECT.

工作表 NAME 不是工作表 OBJECT。

To refer to the sheet OBJECT when you have the sheet NAME you can use ThisWorkbook.Sheets(SheetName) where SheetName is the function parameter and of the type String and not of the type Worksheet.

要在您拥有工作表 NAME 时引用工作表 OBJECT，您可以使用 ThisWorkbook.Sheets(SheetName) 其中 SheetName 是函数参数，并且类型为 String 而不是 Worksheet 类型。

Now for a range it will be a bit more difficult as named ranges can be of a global (whole Workbook) level or on a local (the containing Worksheet only) level.

现在对于一个范围来说，它会更困难一些，因为命名范围可以是全局（整个工作簿）级别或本地（仅包含工作表）级别。

So you would have to check for the two possibilities and either prefer one over the other (so first check locally and if it doenst exist continue globally) or allow the user to express their preference or make a 2nd parameter where the user has to specify.

因此，您必须检查两种可能性，或者更喜欢一种（因此首先在本地检查，如果它确实存在，则在全局范围内继续），或者允许用户表达他们的偏好或在用户必须指定的地方设置第二个参数。