Using a Worksheet to Store Workspaces

As an alternative to using type structures, you can add a worksheet to your project and record all the values that you wish to save. Ideally, you would give each cell a descriptive range name. You then read these values when you need to restore the workspace. In theory, it's much slower to read and write values from a worksheet rather than storing values in an array. In practice, there is almost no difference, as the number of items being stored is relatively small.

The advantage of using a worksheet is that the workspace settings will not be lost if something causes your variables to lose their initialization when a project is reset (also known as state loss). If you do use variables to store your workspace settings, make sure that your add-in includes reasonable defaults to fall back on if state loss occurs.

The user of your add-in should rarely experience state loss at run time, but during development this occurs every time a module is edited and/or compiled. Microsoft Excel will warn you before state loss occurs if you check the box labeled Notify Before State Loss in the Tools/Options/General dialog box under the VBE (shown below).

If your add-in is performance-oriented and you need every millisecond, you can implement a dual storage routine. When in the debug mode, write the values to a worksheet. When your add-in is ready for distribution, have your code store the workspace values in an array.