Global variables in Excel VBA 1

[Nelviticus]

How do I declare a global variable in Excel VBA that's accessible by all modules and all procedures? Also, which module would I place it in?

I have a spreadsheet that launches a userform from its Workbook_Open event. The userform has several procedures in its own code module and also makes calls to procedures in other modules. I'm using Office XP.

I know how to declare a variable that's sort of global, i.e. global within the module it's declared in, but I need one that's truly global across all modules.

Thanks

 

[JohnAcc]

Hi There:

You can use the Public statement to declare public module-level variables.

Public IntNumber As Integer

Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared.

Hope this helps,

Rgds, John

 

[Nelviticus]

Hmm, that's what it says in the help file, but I get a 'Compile error: Variable not defined' whenever I try to reference it.

I have the line

Public blnCompleted As Boolean

in the Declarations section of my userform module (although I've tried it in the ThisWorkbook module as well) and the line that gives the error is

blnCompleted = True

in another module.

I'm using

Option Explicit

in every module.

Any ideas?

N.

 

[JohnAcc]

Have you tried putting it in a seperate module ? I try to declare all my global variables in a declarations module.

That way all of the procedures and subs that are run can "see" this variable.

Is all of your code contained within 1 workbook ?

Rgds, John

 

[TLady]

You create Global module and declare :

Public Const ContractStartDate = #7/1/2002#
Public ReportStartDate As Variant

Then you need to write function Initialize those variables.
Very important. Untill Initialized - they asleep !!!
I suppose this is why isn't working for you ...

Function GetReportStartDate() As Variant
GetReportStartDate = ReportStartDate
End Function

Need more help, let me know.
TIA

 

[TonyJollans]

Hi Nelviticus,

Public variables are, as Help says, available to all procedures. But you must uniquely reference them. Public variables in (ordinary) modules are unique and so present no problem. Public variables in Class Modules (which includes Forms) must be qualified in some way because there may be multiple instances of the class to which they belong.

So, if you define a public variable in a form you refer to it using FormInstance.VariableName. More often than not, of course, you only have a single instance of a form and can reference your public variable simply as FormName.VariableName.

Enjoy,
Tony

 

[Nelviticus]

Ahaaaaa. Thanks Tony. Thanks to everyone else too, but I think Tony deserves the star.

N.