Create a settings file for storing user specific options
byronwall opened this issue · 5 comments
It would be good to create a settings file to store user specific settings. The add-in should detect this file and load it into a general Settings
class or variable. From there, pieces of code that want to behave different will have the ability to do so.
Some initial ideas of settings:
- Chart default formatting settings
- Whether to use an
InputBox
or theSelection
for formatting choices - Default values on prompts that include a default (like
Series Split
)
That's definitely an interesting way to handle saving/restoring user state. I think its strength is that it allows an individual section of code to mess with the only the parts it needs to change and return things back to the way they were found. I have used similar code within a single Sub
to store the Calculation
mode before making changes to it. For that file, there were enough instances where Calculation
would not be automatic going into the code so I explicitly set it back to whatever the user had before.
The ability to fall back to the previous state if the code errors out is also a good idea (assuming it works as expected). I have seen it happen multiple times where code fails after changing those variables, and then I get fooled later that Calculation
is not automatic.
I agree with Mat's Mug that the real test of this code is how it responds to multiple instances of the class. It's very reasonable to have a stack of calls where each one wants to change these variables.
As a side note, I would not name that main variable this
. That's an awfully confusing way to mix conventions between programming languages. It gives the impression on first glance that this
is referring to private fields on the instance where really it is a completely different type since VBA uses Me
as the instance reference.
I'll look into getting something like this into bUTL. It's a good general utility class to have. There are a number of spots where the code is changing these variables, and some consistency around it would be good.
Regarding storing settings, I am torn between two approaches.
The first is to store them outside of the add-in in an INI file or something similar. This is a good example of how that would be done.
The other approach is to use the hidden sheets inside the bUTL.xlam file to store the settings and save them. This approach has the upside that there are no external files. It also makes it possible to technically load/see/change the settings inside Excel. It makes it difficult to persist settings if there are multiple users or if the add-in is going to be upgraded. It'd be nice to just replace the xlam file and know that everyone will continue to work.
I'm probably leaning toward the INI file. I have not taken a major shot at this though yet. I have been incrementally adding/testing functionality as I use the add-in at my new job.
The INI file is probably the more robust way to go - it might also be a way to store the user's defaults if you need to bring it back in anyway.