Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
VBA Excel - Permanent storage of a variable?
I want to keep a variable that does not get lost when the program is closed or the computer restarted.
So for example, I click Button1 that sets VariableY = 4 and saves the worksheet. Now I restart the computer, open my worksheet and click button2 that prints VariableY.
Under normal circumstances it will have null value.
I would like to avoid using cells on the worksheet for this permanent storage if possible.
I dumbed down my example to illustrate my needs. The application is very different. There are other ways to achieve my goal but this would be the easiest if 'permanent storage' is a simple command.
I would really prefer that the excel file is 'stand alone'. This file gets emailed frequently and having my buttons depend on seperate text files is likely to cause problems.
Devil,
Your method works while the excel window remains open.
If I save the file, close it, reopen it and run the macro again the variables have been reset.
Tested with the following code:
Static Test as Integer
Test = Test + 1
msgbox Test
This is a good 'button click' counter I didn't know about but doesn't quite get the job done
7 Answers
- Simon BelmontLv 61 decade agoFavorite Answer
I had the same request and I used my personal.xls macro. This file use hidden windows, so, it´s allways open but its sheets are hidden.
If you store a value on range("a1") on sheet1 of personal.xls file, you can keep it on memory and restore it when you want. Just remember save the file when you update the value.
I built a list of common used files for a userform and saved it on sheet1, when I want open one of those files, I type a shortcut that I made and it opens a form with my list of important files. I can handle it (add, open file, remove files, etc) and it~s allways available.
- devilishblueyesLv 71 decade ago
It's much simpler to just declare a static variable.
You're probably most common with declaring a variable like this:
Dim VariableY As Integer
When you declare a variable like this, the value of the variable gets dumped after that macro is done running.
To do what you want, you need to declare your variable like this:
Static VariableY As Integer
The Static statement makes Excel store the variable in its memory so that it recalls the number the next time you open that file.
- CozmosisLv 71 decade ago
I don't know of a "stand alone" way to have a persistent variable in VBA that will survive when you close the application. One work around could be to have some Textbox objects that are not visible and store your values in the textbox object as text.
If you want some sample code, let me know.
- Ngo Cong HuanLv 41 decade ago
You can store it to an external textfile. And load back variable when button 2 pressed
Long time I've use VB or VBA so I didn't remember any thing about file operations.
You should view help.
- Anonymous7 years ago
you can create a hidden sheet in the excel and store all variables in the cells.
There must be another way to store them by creating objects but I usually create a whole sheet to store one Boolean variable :(
- ?Lv 44 years ago
definitely, your answer would be 7 used components. you may return the style of used components in an array utilising the count selection function. as an occasion, in the adventure that your array is dimmed as 'myArray', this line will exhibit a message container with the style of used components. MsgBox utility.count selection(myArray) & " used components.", vbOKOnly, "result"
