Static variable

[VBA beginner]

How can I save the value of the variable so, that it keeps its value also
after the application has closed?

I have module, where I have:
Sub counting()
Static counter As Integer

counter = counter +1

End sub

But if I save and close the program, and open it again, the value of the
counter is again 0. Any suggestions to fix the problem?

 

[Bob Phillips]

You could save it to registry on closing, and read it from there on opening.
See SaveSetting and GetSetting in VBA help

 

[Mike H]

Hi,

You could write it somehere out of the way and then read it in next time the
workbook is opened

Sheets("Sheet1").Range("IV65536").Value = counter

and on opening
counter = Sheets("Sheet1").Range("IV65536").Value

Mike

 

[Charlotte E.]

First of all, unless you specific need an Integer variabel, you should
define your Counter as Long.


Second, this should do the trick:

Sub SaveCounter()

Dim Counter As Long
Counter = GetSetting("This_Is","My_Counter","So_Far", 0)

Counter = Counter + 1

... some code here ...

END_OF_SUB:

SaveSetting "This_Is","My_Counter","So_Far"

End of Sub

 

[scooper]

If the variable name is numvar then just use a basic text file like this in
a button on the worksheet:

numvar=Range("A1").Value
open "C:\excel\numstore.csv" for output as #1
print #1,numvar
close #1

then to retrieve another button or use the Worksheet.activate event:

open "C:\excel\numstore.csv" for input as #1
read #1,numvar
Range("A1").Value=numvar
close #1

or something

scooper