VBA code to modify VBA code

[KevBotes]

Hello all,

I would like to be able to use code to modify code, specifically a constant held in my macro. e.g; if I enter a name as a parameter "Ms Scarlet" i would like the current value held in the constant to be updated for future use:

From:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

The change must be saved, so tha the next time the add-in is run, the new value is still relevant.

Again, any assistance s appreciated.

[Richard Buttrey]

Why complicate it?
If you want to change something then by definition it's not a constant!
Why not just hold your variables on a dedicated sheet, protected and hidden if necessary.

[KevBotes]

Thanks, I asked that question in another thread, but i see no spreadsheet available to store my data in my add-in.

The value is constant until such time I choose to change it.

I have a pop up form that asks which sales rep and which customer I am running a report for, once I am done with all sales rep A's reports, I start with the next rep. I would like my pop up form to remember the name of the sales rep since I last ran the macro.

[Kyle123]

The value is constant until such time I choose to change it.

I think you've got the wrong idea about constants, the point is you don't change them - otherwise they wouldn't be constant

Why can't you do as Richard suggests and store the data on a sheet? - Writing code with code is hacky, it requires users to lower their security settings and can be blocked by antivirus software.

[KevBotes]

I did not know this

it requires users to lower their security settings and can be blocked by antivirus software.

Thanks, I will research more on storing the data in a worksheet.

[Richard Buttrey]

Holding variables (as well as constants) on a dedicated sheet is an exceedingly common approach. I use it all the time.