Public declaration

[Greg]

I have a series of macros that depend upon counting a number of
spreadsheets. I wish to determine the exact number prior to runing
the macros. I currently update the variable Numsites manually as a
constant. I tried to automate the process so that prior to to running
any other macros I would know the number of spreadsheets I was dealing
with.

The code below had the following error Compile error: Invalid outside
procedure.

Can you please assist with some options.


Option Explicit
Option Private Module

Public Const NumSites As Integer = 20

Dim NumSites As Integer
For Each Worksheet In Worksheets
Do While Not Worksheet.Name = "File names"
NumSites = NumSites + 1
Exit Do
Loop
Next Worksheet

 

[Bob Phillips]

Option Explicit
Option Private Module

Function NumSites(Optional wb)
If IsMissing(wb) Then wb = ActiveWorkbook.Name
NumSites = Workbooks(wb).Worksheets.Count
Next Worksheet

You use like so

myNum = NumSites()

or

myNum = NumSites(ThisWorkbook.Name)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

Greg,

The error pops because you're using the same name
for a variable and a constant.

Try like:

Public Const cNumSites as integer = 20
Public NumSites as Integer


Sub MyProc()
dim wks as worksheet
on error resume next
set wks = worksheets("file names")
on error goto 0
NumSites = worksheets.count - iif(wks is nothing,1,0)
if NumSites > cNumSites then msgbox "Too many sheets!"
End Sub


HTH
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Greg wrote :