Calling custom functions from Excel

[David]

Ladies & Gentlemen,

I'm trying to automate a sheet ___ of ___.
{This occupies four cells} I've added the following two
custom functions.

Function fnSheet()
fnSheet = ActiveWorkbook.ActiveSheet.Index
End Function

Function fnSheets()
fnSheets = ActiveWorkbook.Sheets.count
End Function

Then in Excel I can use sheet =fnSheet of =fnSheets.
At first it appears to work fine, however, copying two, or
three sheets it produces errors. Any ideas how to correct
the problem? Currently these functions reside in a Module
and I'm assuming adding the functions to the individual
worksheet would solve the problem, however, I would think
there is a better way?

Thanks in advance,

David

 

[Frank Kabel]

Hi
problem is the referencing of the active sheet. One way:
Function fnsheet(rng As Range)
fnsheet = rng.Parent.index
End Function

Enter on your worksheet the formula:
=fnsheet(A1)

For the second one try

Function fnSheets(rng as range)
fnSheets = rng.parent.parent.Sheets.count
End Function