Retaining a variable value even after macro exits
Hello,
Is there a way of saving a variable in the .xlam file that executes the macro, so that if either Excel is closed, or the macro is applied to various other workbooks, the variable value is retained?
The user must have the ability to change the variable if needed, and the new value needs to be saved. The value is not used in the sheet but used in calculations in the macro modules and presented on a userform
You could store the value in a cell in the xlam file.
Entia non sunt multiplicanda sine necessitate
Do you have a code snippet that you can share to implement this?Originally Posted by shg
Are you familiar with the Range object and how to access cells in VBA? It seems so simple, that it feels like there is more to your question. Something as simple aswould store the variable's value in cell A1 of the first sheet.
Please Login or Register to view this content.
If you are unfamiliar with how to work with cells/range objects, you might review this document: https://docs.microsoft.com/en-us/off....Range(object)
Ok, so I use the code saved in an .xlam file to link several macro modules from this file to my quick access toolbar. This allows me to perform certain functions on the data entered every time I open new .xlsx files. Will the "Thisworkbook" function access the newly opened .xlsx file or the xlam file that contains the macros? I need to save the variable values in the .xlam file....
The "ThisWorkbook" object specifically refers to the workbook that contains the code. If the code is in an .xlam file, then Thisworkbook refers to the .xlam file containing the code. https://docs.microsoft.com/en-us/off...n.thisworkbook
the ActiveWorkbook object will refer to the currently active workbook. https://docs.microsoft.com/en-us/off...activeworkbook
To access other workbooks, you use the Workbooks collection to return the specific workbook you want. https://docs.microsoft.com/en-us/off...tion.workbooks
If you are new to VBA and Excel, one of the most useful things to learn is Excel's object model. The left frame of each of those help files should contain a "tree" that will allow you to explore Excel's object model.
I have tried the ThisWorkbook option, it works but when I close the .xlsx file and open again, the variable values are not retained ...
My bad, they are saved... thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks