Global Variables in Excel VBA

[shumwaymeister]

I have an excel program, heavily driven by menus and buttons with
various entry-points and exits.

I am however attempting to create a global variable that could be used
to store and access data, regardless of the entry/exit point used.

Use of the public command would create a global variable which would to
this. However, given the fact that a user may come in at any point
(depending on what button or menu they use), there is no obvious way to
create a global variable.

Is there a way to actually create a global variable - that could be
used regardless of what a user presses?

 

[Chip Pearson]

I don't completely understand your question. If you declare a Public (the
new term for "Global"), in a standard code module (not in a object module
like a Userform or Class module), you can write/read to and from that
variable from any procedure in any module in the project. E.g.,

Public MyVar As Variant

Beyond that, I don't understand what your dilemma is.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[thesquirrel]

If you put the variable in a module, it will be created when the
workbook opens and if preceded by the the word Public will be available
to all your forms and modules.

Hope that answers your question.

theSquirrel

 

[Randy Harmelink]

If worst comes to worst, you could always store any such data in the
workbook itself. Something akin to a "Settings" worksheet.

 

[gimme_this_gimme_that]

Put this in a module:

Public Const GLOBAL_VAR as String

Then GLOBAL_VAR is available in any module.

 

[Chip Pearson]

If worst comes to worst, you could always store any such data in the

workbook itself.

I wouldn't characterize that technique as something one would do if "worst
comes to worst". I've done that on more than a few occasions when I needed
to store a value and couldn't run the risk of globals getting wiped out by
the user monkeying around in the VBE. If you need to be certain that a
variable won't be wiped out of memory, you need to store it in a worksheet
cell, a defined name, or the registry. As long as you're not storing huge
number of variables in a sheet, to the point where performance would be
noticeably degraded, there's really nothing wrong at all with storing temp
data in a worksheet. Just make the sheet xlVeryHidden so the user can't
screw it up, and its a perfectly legitimate technique.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Guest]

See Tom Ogilvy's response to my question "workbook-level public variables":

"declare it in a general module and initialize it in from any module

General Module:

Public MyVar as Long

in the ThisWorkbook Module

Private Sub Workbook_Open()
MyVar = 6
end Sub

--
Regards,
Tom Ogilvy"

Regards,
Stefi


„[email protected]†ezt írta: