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?

Solution 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)

Solution 2

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

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

Comments

[1dolinski]

nice update, the only other thing I have come across is that the formula does not reset after page re-calculation... not sure why this might be

[Jook]

this is because, if you use SheetYX!A1, then excel doesn't get that there was a change, until you change A1. So use SheetXY!A:IV and it should react on any change.

[I'm with Monica]

Instead of using a hard coded string to reference a worksheet, you can just access it using its CodeName. Thus ThisWorkbook.Sheets("Sheet1").Cells could just be Sheet1.Cells, also hard coded, but much quicker to read and understand.