How to insert a dynamic data in a macro
- Thread starter azza123
- Start date
BobBridges
Active Member
Azza, I agree with Marc—you should give more detail about what you mean by "dynamic"—but I'm going to guess that you want your program to use a value that changes frequently.
You can, of course, just create a Const value at the top of your program and go in there to change it from time to time. But there is an alternative: Create a worksheet in the .xlsm named Settings. You, or whoever's using the programs, can go to the Settings worksheet and type in new values there; have the program read that worksheet and behave accordingly.
For example, suppose you want a report to be "as of" Jan 1 2024 this time, but another time maybe you want 1999 or 2022. One row in your Settings worksheet can have "As-of date" in col 1 and the date in col 2. Your program looks through all the settings, picks out the labels it needs, grab the corresponding settings, and use those values when processing.
I do this a lot when writing things for non-programming users. Not so much for myself, but sometimes that too.
You can, of course, just create a Const value at the top of your program and go in there to change it from time to time. But there is an alternative: Create a worksheet in the .xlsm named Settings. You, or whoever's using the programs, can go to the Settings worksheet and type in new values there; have the program read that worksheet and behave accordingly.
For example, suppose you want a report to be "as of" Jan 1 2024 this time, but another time maybe you want 1999 or 2022. One row in your Settings worksheet can have "As-of date" in col 1 and the date in col 2. Your program looks through all the settings, picks out the labels it needs, grab the corresponding settings, and use those values when processing.
I do this a lot when writing things for non-programming users. Not so much for myself, but sometimes that too.