Can I save variables as part of and Excel file 2

[jrice174]

I'm trying to save some program data so that the next time I run a VBA program the form has some of the controls set as they were the last time it was run in that document. I know I could create a separate worksheet to store it but I'd really like it invisible.

In Word I can use ThisDocument.Variable.AddItem(VariableName) to create a place to store a varaible. In AutoCAD I can use Name = ThisDrawing.GetVariable("USERS1") as a place to get a string of data. Is there anything in Excel that is similar where I can store data that gets saved with the workbook?

 

[MintJulep]

Create a VeryHidden worksheet and store your data there.

 

[jrice174]

That's certainly a possibilty I didn't think of.

 

[electricpete]

The idea of a hidden worksheet is a good one. In a big project I often create a hidden worksheet called ProgConst (Program Constants). I sometimes use it also as an alternative to global variables. Instead of global variables I create a named range (cell) in the hidden worksheet and store the value there. One of the benefits is that they stick around.

If for some reason you don't like that there are a number of other ways. Here is one: Store your variables into named formula's.

For example let's say you have a variable MyVariable that you work with.

' example variable initialization
double MyVariable
MyVariable = 43.1

You can store and retrieve into a named formula as follows:

Store your variable as follows:

'Storing the variable:
Sheets("Sheet1").names.add Name:="MyVariableStored",  RefersToR1C1:="=" + cstr(MyVariable)
' Note that the RefersToR1C1 expects and excel type formula 
' so we created a formula by concatenting "=" with the string version of MyVariable

To retrieve your variable

' Retrieving the variable from storage
MyVariable=evaluate(Sheets("Sheet1").names("MyVariableStored").RefersToR1C1)
' Note the evaluate function converts formula back to value

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[brengine]

You could write the data to a Text (or .ini) file somewhere on the Network (or locally) or to the local computer's Registry.

Ken

 

[Denial]

Stuff written to a separate file or to the Registry can be a useful tactic in some situations, but the stuff remains with the computer rather than with the workbook file.