Public variables keep forgetting set value

[the_gunslinger]

This one has been annoying me for a little bit now, and i hate the idea of having to try and do it by passing values to hidden cells to retrieve later, it should be possible completely from within the VBA module.

so here's the objective.... open excel, change a whole heap of settings to what i want, from excel general settings to workbook specific ones, that part is easy, returning the users default settings on close is proving less easy

I've got a module with all of my public declarations and functions in, so all of my values are Dim'd as Public and in the correct reference, be it Boolean or String etc etc

On opening the workbook, a good number of things are called and run, from within the workbook_open event, high up on the list are all of those settings defaults stored to the various variables for save keeping, or so i thought!
The workbook is not simple by any means, and has a large number of things going on with macros and objects and user forms

Upon close, all of the altered settings are supposed to be reset to the stored defaults, but, the dam variables keep dropping their values.


Now i know, i can flip all the values to a hidden worksheet, and then load them back at close, but, it's not very elegant, and to be quite honest, this "forgetting of data" by the declared variables is annoying as hell, so, is there a way to fix this ?

[the_gunslinger]

ok, so it seems that even if you declare it Public in scope in a code module, ANY run-time error will set it to null, causing the values to be lost, not sure if this includes any errors that you might have skipped using on error resume next, or just untrapped errors, but, as i have been working on and testing this workbook, i've triggered a lot of errors, making this likely to be my main culprit


I think i might have to take the annoying steps to save the data to a second worksheet to import it later