PDA

View Full Version : Optional argument in a function error



next
01-05-2011, 08:07 AM
I'm trying to write a function to retrieve last used cell, but get "Object variable or with block not defined" error...

Function GetLastCell(Optional my_sheet As Worksheet)
Dim Cell As Integer

If IsEmpty(my_sheet) Then
GetLastCell = ActiveSheet.UsedRange.Rows.Count
Else
GetLastCell = my_sheet.UsedRange.Rows.Count
End If

End Function

This is what I use to test it:
Sub test3()
MsgBox GetLastCell()
End Sub

It works well if I define the argument in the function, but screws me with default value, can anyone help?

Tinbendr
01-05-2011, 08:16 AM
My_Sheet can't be optional. You'll have to pass it to be able to use it in the function.

next
01-05-2011, 08:23 AM
Thanks, it works.

mikerickson
01-05-2011, 08:25 AM
To test if an optional function argument has been passed, one should either use a Variant argument and use IsMissing or should test the argument against a null.
Function GetLastCell(Optional my_sheet As Worksheet)
Dim Cell As Integer

If my_sheet Is Nothing Then
GetLastCell = ActiveSheet.UsedRange.Rows.Count
Else
GetLastCell = my_sheet.UsedRange.Rows.Count
End If

End Function