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

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

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

"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