Public declaration

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

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)


"Greg" <helix2dna@hotmail.com> wrote in message
news:ccf7587a.0501210950.28ce5a92@posting.google.com...
> 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

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 :

> 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