Custom Function using a Sheet name as a parameter

Question

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

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

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?

Answer 1

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.

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.

You can now however easily use a NAMED-Range, as long as it refers to a 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.

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.

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

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