=INFO("numfile")

  • Thread starter Thread starter Squid
  • Start date Start date
S

Squid

I am using =INFO("numfile") to return the number of active
worksheets I have open. This function is returning more
worksheets that I see active. How can I return the names
of the active worksheets?

Thanks
Mike
 
Mike,

Don't forget you may have some hidden workbooks (such as Personal.xls), plus
any custom addins that mioght have worksheets.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Squid

=INFO("numfile")
It also count the sheets in Add-ins that are open

This is a function from David McRitchie that you can use

Option Explicit
Function worksheets_count() As Long
worksheets_count = Worksheets.Count
End Function

usage:
=Worksheets_Count()
=personal.xls!worksheets_count()
="Number of worksheets: " & worksheets_count()
 
...
...
This is a function from David McRitchie that you can use

Option Explicit
Function worksheets_count() As Long
worksheets_count = Worksheets.Count
End Function
Click to expand...
...

If he wrote this, he didn't test it much. It returns the number of worksheets in
the *ACTIVE* workbook. If multiple workbooks were open, say, A, B and C, with 5,
4 and 3 worksheets, respectively, and this formula were called from a cell
formula in C, then if A were active at recalculation, the function would return
5 rather than 3 to its calling cell.

As with almost everything in programming, it's best to be explicit (unless one
enjoys tracking down mysterious bugs).


Function worksheets_count(Optional r As Range) As Long
If r Is Nothing And TypeOf Application.Caller Is Range Then _
Set r = Application.Caller

If r Is Nothing Then
worksheets_count = -1
Else
worksheets_count = r.Parent.Parent.Worksheets.Count
End If

End Function
 
Back
Top