Global Variables in Excel VBA

  • Thread starter Thread starter shumwaymeister
  • Start date Start date
S

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?
 
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)
 
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
 
If worst comes to worst, you could always store any such data in the
workbook itself. Something akin to a "Settings" worksheet.
 
Put this in a module:

Public Const GLOBAL_VAR as String

Then GLOBAL_VAR is available in any module.
 
If worst comes to worst, you could always store any such data in the
workbook itself.
Click to expand...

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)
 
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:
 
Back
Top