Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Maintaining VBA variable values
How can I get my variables to keep their values when I close my Excel file?
In other words, I have some Dim statements at the top of my code so the variables keep their values while I run different sub routines; however, when I close and re-open the file, the values are lost -- is there a way to save them? (As you can probably tell, I am a novice so your not missing anything -- this is probably a very easy question.) Thanks |
#2
|
|||
|
|||
Adam,
One way is to tuck them into cells in a sheet. And unless you have a compute-intensive iterative nightmare that runs for a long time, just use those cells as variables. Dim MyValue5 As Range Set MyValue5 = Sheets("Sheet1").Range("A1") MsgBox MyValue5 MyValue5 = MyValue5 + 1 MsgBox MyValue5 Or use names. Insert - Name - Define. Put the variable name (MyValue5) in the name box, and =5 (to initially set it to 5) in the "refers to" box. this doesn't use a cell. Or use this code once: ActiveWorkbook.Names.Add Name:="MyValue5", RefersToR1C1:="=5" Now use it like you would a variable MsgBox MyValue5 MyValue5 = MyValue5 + 1 MsgBox MyValue5 Either way, they get stored with the workbook when saved. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Adam1 Chicago" wrote in message ... How can I get my variables to keep their values when I close my Excel file? In other words, I have some Dim statements at the top of my code so the variables keep their values while I run different sub routines; however, when I close and re-open the file, the values are lost -- is there a way to save them? (As you can probably tell, I am a novice so your not missing anything -- this is probably a very easy question.) Thanks |
#3
|
|||
|
|||
Save them in a range name, and retrieve them later. Make sure the variables
are declared as Public variables in a general module, and use something like Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Names.Add name:="myVar1", RefersTo:=myVar1 End Sub Private Sub Workbook_Open() myVar1 = Evaluate(ActiveWorkbook.Names("myVar1").RefersTo) End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing direct) "Adam1 Chicago" wrote in message ... How can I get my variables to keep their values when I close my Excel file? In other words, I have some Dim statements at the top of my code so the variables keep their values while I run different sub routines; however, when I close and re-open the file, the values are lost -- is there a way to save them? (As you can probably tell, I am a novice so your not missing anything -- this is probably a very easy question.) Thanks |
#4
|
|||
|
|||
"Adam1 Chicago" wrote in message ... How can I get my variables to keep their values when I close my Excel file? In other words, I have some Dim statements at the top of my code so the variables keep their values while I run different sub routines; however, when I close and re-open the file, the values are lost -- is there a way to save them? (As you can probably tell, I am a novice so your not missing anything -- this is probably a very easy question.) Thanks You can define a name similar to the name of your variable and refer to the value of your variable. /Fredrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Grabbing recods based on date and shift values | Excel Worksheet Functions | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Variable values in Index function | Excel Worksheet Functions |