Excel Workbook Public Variables 2

[GreyBox]

I just can't figure out how to use a public variable in Excel that can be shared between procedures throughout the workbook. I'm using Excel 2000 9.0 SR-1 which has VBA 6.0

I've tried declaring a public variable at workbook level:

Public PubOne as Integer

and then incrementing it in Subs both in sheets and in a module. Neither worked - the variable PubOne within each Sub (undeclared) didn't increment and was local to each Sub.

What *did* work was declaring the variable as public in the declarations part of the worksheet. But that only shared it between procedures in that worksheet.......

What am I doing wrong?

 

[RobBroekhuis]

If you're declaring it on the Workbook code sheet, move it to a regular code module instead. Placed anywhere in the declarations area (i.e., above subs) in a regular code module, the variable will be accessible to ALL subs in your VBAproject (including workbook/worksheet code objects, userform code, etc)
Rob

 

[Stretchwickster]

As Rob advised, I have a module called Mod_Global which stores all my global variables and constants (which are used in many other modules).

e.g.

Public Const INT_ITEMS_PER_CHART As Integer = 20
Public Const SGL_LOWER_THRESHOLD As Single = 0.95
Public Const SGL_UPPER_THRESHOLD As Single = 1.06

Clive