CODEWITHSUNDEEP
vbaexcelexcel-2007excel-formulaexcel-2010
1dolinski
1dolinski

Reputation: 549

Custom Function using a Sheet name as a parameter

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?

Upvotes: 2

Views: 36315

Answers (3)

alkorya
alkorya

Reputation: 23

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

Upvotes: 2

Jook
Jook

Reputation: 4682

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)

Upvotes: 7

K_B
K_B

Reputation: 3678

The sheet NAME isn't the sheet 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.

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.

Upvotes: 0

Related Questions