In .NET they have a nice settings class you can create using ApplicationSettingsBase base class. In VBA it takes a bit more work to get it working, but can be fairly elegant if done correctly.
Some criteria that is needed for a settings class in vba are:
- Well formed
- Fast lookup
- Default setting
- Single input/output procedures.
To make it well formed I used an enumeration variable type to create each setting. I think string constants could accomplish the same thing.
Doing default settings is interesting. It took me some time to figure out how to do that. But all that is needed is an optional boolean value for the Get and Let procedures.
A single point to output your values to makes it easy to trouble shoot if something isn’t working correctly. Also, the single input is just one procedure that grabs data from where ever you choose to store it and if it isn’t stored then it will just grabs the default value.
Here’s an example on how a property would look like:
'In order to get default value an optional field is needed in the Get and Let procedures.