Maintaining VBA variable values

A

Adam1 Chicago

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
 
E

Earl Kiosterud

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

Bob Phillips

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)
 
F

Fredrik Wahlgren

Adam1 Chicago said:
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
Click to expand...

You can define a name similar to the name of your variable and refer to the
value of your variable.

/Fredrik
 
Top