×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Can I save variables as part of and Excel file
2

Can I save variables as part of and Excel file

Can I save variables as part of and Excel file

(OP)
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?

RE: Can I save variables as part of and Excel file

Create a VeryHidden worksheet and store your data there.

RE: Can I save variables as part of and Excel file

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

RE: Can I save variables as part of and Excel file

2
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.

CODE

' example variable initialization
double MyVariable
MyVariable = 43.1

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

Store your variable as follows:

CODE

'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

CODE

' 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.

RE: Can I save variables as part of and Excel file

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

RE: Can I save variables as part of and Excel file

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources