Create a settings file for storing user specific options

[byronwall]

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 the Selection for formatting choices
• Default values on prompts that include a default (like Series Split)

[RaymondWise]

just putting this here as reference for me.

[RaymondWise]

This was interesting as well

[byronwall]

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.

[byronwall]

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.

[RaymondWise]

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.