Return number of current sheet

I'm looking for a way to create a function to return the current sheet you’re
working in. Not it’s name (I’ve found that function), but the number of the
sheet. Example: if you have a total of 8 sheets, and you’re working on the
3rd sheet, I’d like to return the number 3 in a certain cell on the third
sheet, 4 on the fourth sheet, 5 on the fifth sheet, etc., and have those
numbers update if sheets are added or deleted. Is that possible?

One way:

Public Function SheetNum(Optional rng As Range) As Variant
Application.Volatile
If rng Is Nothing Then
If TypeName(Application.Caller) = "Range" Then
Set rng = Application.Caller.Cells
Else
SheetNum = CVErr(xlErrRef)
Exit Function
End If
End If
SheetNum = rng.Parent.Index
End Function

usage:

=SheetNum()

or

=SheetNum(MySheet!A1)


note that this will not automatically update when a sheet is moved
within a workbook - Application.Volatile will cause it to update the
next time a calculation is made.


In article <4D3C9244-E9A0-4B0B-881C-B7ADFC03EF4A@microsoft.com>,
"Brandon" <Brandon@discussions.microsoft.com> wrote:

> I'm looking for a way to create a function to return the current sheet you’re
> working in. Not it’s name (I’ve found that function), but the number of the
> sheet. Example: if you have a total of 8 sheets, and you’re working on the
> 3rd sheet, I’d like to return the number 3 in a certain cell on the third
> sheet, 4 on the fourth sheet, 5 on the fifth sheet, etc., and have those
> numbers update if sheets are added or deleted. Is that possible?

Thanks very much. That worked just fine!

"JE McGimpsey" wrote:

> One way:
>
> Public Function SheetNum(Optional rng As Range) As Variant
> Application.Volatile
> If rng Is Nothing Then
> If TypeName(Application.Caller) = "Range" Then
> Set rng = Application.Caller.Cells
> Else
> SheetNum = CVErr(xlErrRef)
> Exit Function
> End If
> End If
> SheetNum = rng.Parent.Index
> End Function
>
> usage:
>
> =SheetNum()
>
> or
>
> =SheetNum(MySheet!A1)
>
>
> note that this will not automatically update when a sheet is moved
> within a workbook - Application.Volatile will cause it to update the
> next time a calculation is made.
>
>
> In article <4D3C9244-E9A0-4B0B-881C-B7ADFC03EF4A@microsoft.com>,
> "Brandon" <Brandon@discussions.microsoft.com> wrote:
>
> > I'm looking for a way to create a function to return the current sheet you’re
> > working in. Not it’s name (I’ve found that function), but the number of the
> > sheet. Example: if you have a total of 8 sheets, and you’re working on the
> > 3rd sheet, I’d like to return the number 3 in a certain cell on the third
> > sheet, 4 on the fourth sheet, 5 on the fifth sheet, etc., and have those
> > numbers update if sheets are added or deleted. Is that possible?
>

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote...
....
> If TypeName(Application.Caller) = "Range" Then
> Set rng = Application.Caller.Cells
....

Why TypeName(x) = "y" rather than TypeOf x Is y? Also, when the condition is
true, won't Application.Caller.Parent resolve to the same worksheet as
Application.Caller.Cells.Parent? If so, why use .Cells?