Storing Variables

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

Hey guys,

I always use cells in a sheet to store variables, usualy the active sheet.
However, I am finding that as I create more and more programs and attempt to
expand them for more usability, that I am running into problems where my
variables are getting in the way. In most cases, in order for me to expand
usability/features of my programs, I am having to move my variables so the
code I write does not overwrite them. This takes time because I have to
then go all through the entire code and specifiy the new cell location of
the variable. This increases the probability that I will make a mistake,
and then my code will not work.

There has to be some other way or alternative...
Is there anyway I can store variables in a module or something in the actual
VBA code instead of cells in a worksheet. I still will use some cells in a
worksheet to store variables(theres no way around that), but I would also
like to have them stored in a module or something in my actual VBA code.

For instance, I have a value in cell A1 that changes based on certain
conditions, and I use this value as a reference for other functions in my
VBA code. Is there a way to have this value(variable) stored somewhere in a
module where I can simply reference that module variable instead of always
having to reference the actual cell to find the value? This way if I need
to put something in cell A1, I can without having to move the variable in
cell A1 and then having to go all throughout my code and re-specifiy where
to look for that variable.

I hope this is clear.

Thanx in advance
Todd Huttenstine
 
I'd recommend adding a worksheet to your workbook and store the
variables there. You can set the worksheet's .Visible property to
xlVeryHidden so that users won't know about it.

Then when your workbook starts, load the variables from the
worksheet using the Workbook_Open() event and write them back before
closing with the Workbook_BeforeClose event (or more frequently).
 
Todd,

I put all the variables I need to store in a worksheet I call "System".
I only use this sheet for variables and (nearly)constants
(like the list of authorised users) that I want to store between sessions.
I keep the sheet hidden, so that prying eyes can't play with it easily.
That way, it never gets overwritten by the data as all the data is on other
sheets.
When you need to change one of these, its much easier to overwrite a cell
than to have to trawl through the program finding out where they are.

HTH
Henry
 
So is there not a way to store them in a module?

J.E. McGimpsey said:
I'd recommend adding a worksheet to your workbook and store the
variables there. You can set the worksheet's .Visible property to
xlVeryHidden so that users won't know about it.

Then when your workbook starts, load the variables from the
worksheet using the Workbook_Open() event and write them back before
closing with the Workbook_BeforeClose event (or more frequently).
Click to expand...
 
Todd,

The first thing you do with a variable in a module is to Dim it.
When the module is reopened, it Dims the variable, setting it to zero or ""
So there is no way that you can store variables in a module.

HTH
Henry
 
Alright thanx, I will start doing that.

Todd

Henry said:
Todd,

I put all the variables I need to store in a worksheet I call "System".
I only use this sheet for variables and (nearly)constants
(like the list of authorised users) that I want to store between sessions.
I keep the sheet hidden, so that prying eyes can't play with it easily.
That way, it never gets overwritten by the data as all the data is on other
sheets.
When you need to change one of these, its much easier to overwrite a cell
than to have to trawl through the program finding out where they are.

HTH
Henry

attempt in in
Click to expand...
 
Back
Top