Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public declaration
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" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public declaration
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook Declaration | Excel Discussion (Misc queries) | |||
which declaration to use | Excel Programming | |||
Declaration? | Excel Programming | |||
public declaration | Excel Programming | |||
Variable Declaration?? | Excel Programming |