Hi,
I am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple.
Some of my variables are local to the subs they're used in.
Some are global as they're used by subs in several sheets.
A third type of variable is used by several subs all belonging to the same sheet.
Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?
Cheers,
Lotte
Just declare them at the top of the module (after Option Explicit) using Dim.
You can only declare public variables in a code module, not in a sheet, workbook, forms, or class module.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Hi Shg,
Thanks for your reply.
The subs which use these variables are not in a module but in the code that corresponds to individual sheets. Is there a way of declaring these variables so they are known to all the subs in that sheet (but not the subs in other sheets, or modules)?
Cheers,
Lotte
As shg said declare the variable at the top of the relevant module.
Code Module
Code:Public X Sub Test() X = 1 Sheet1.X = 2 MsgBox X, , "Project Level X" MsgBox Sheet1.X, , "Sheet Level X" Sheet1.TestMe MsgBox X, , "Project Level X" MsgBox Sheet1.X, , "Sheet Level X" End Sub
Sheet Code Module
Code:Public X Public Sub TestMe() X = X + 2 MsgBox X, , "Sheet Level X" End Sub
Shg and Andy,
Thanks!
Lotte
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks