Function SheetNme(Optional ChkCell As Range = Nothing) As String
On Error GoTo error_handler

If ChkCell Is Nothing Then
SheetNme = Application.Caller.Parent.Name
Exit Function
Else
SheetNme = ChkCell.Parent.Name
End If
Exit Function

error_handler:
MsgBox Err.Number & vbLf & Err.Description

End Function

worked for me.


=================
here is a version with ISMISSING
the argument must be specified as variant to use Ismissing (see Excel vba
help for details)

Function SheetNme(Optional ChkCell As Variant) As String
On Error GoTo error_handler

If IsMissing(ChkCell) Then
SheetNme = Application.Caller.Parent.Name
Exit Function
Else
SheetNme = ChkCell.Parent.Name
End If
Exit Function

error_handler:
MsgBox Err.Number & vbLf & Err.Description

End Function

That worked as well.

--
Regards,
Tom Ogilvy



"mikelee101" wrote:

Hello,
I'm trying to write (what I assumed would be) a relatively simple function.
I want it to return the sheet name of a cell or range included in an argument
or, if no argument is supplied, return the sheet name that the cell is on
that the function resides in. However, I can't seem to make the function
recognize whether the argument has been supplied or not. Here's what I have:

Function SheetNme(Optional ChkCell As Range) As String
On Error GoTo error_handler

If IsMissing(ChkCell) = True Then
SheetNme = ActiveSheet.Name
Exit Function
Else
SheetNme = ChkCell.Worksheet.Name
End If
Exit Function

error_handler:
MsgBox Err.Number & vbLf & Err.Description

End Function

I've also tried IsEmpty, IsNull, IsObject and IsError...none of which make a
distinction between a supplied variable or not.
I tried assigning a value to the variable at declaration, e.g. Optional
ChkCell as Range = ActiveCell.AddressLocal; and various permutations thereof,
but I always got an error that it had to be a constant. Assigning a constant
didn't work well with the range object.

There has to be something that I'm missing, but I couldn't find it in help.
If anyone has any ideas, I'd love the input.

Thanks,
Mike