Creating an addin from an Excel macro

Pages in this article

  1. Preparations
  2. Book or add-in
  3. Menus
  4. Toolbars
  5. Limit access
  6. Protect code
  7. Initialise
  8. Save Settings
  9. UI Languages
  10. Setup
  11. Conclusion

Initialization Of Variables

Most utilities will have some settings which are needed throughout their usage and are often read during their initialization phase.

These could include user settings (as discussed in chapter 8), but application settings like the Excel version are often used in various places of the code. Often this kind of information is kept in public variables, which are meant to retain their value during the entire Excel session.

If however the code gets into trouble and causes a runtime error, the user may press "End" and all public variables will loose their values. This is a risky situation, because hitting End will not make menu items inoperable and the utility might be started without the proper settings.

A simple check to ascertain whether everything is still in proper working condition is to add an extra public variable (at the top of a normal module):

Public bVarsOK As Boolean

Then at the top of each entry routine (routine that is started by an immediate user action):

If Not bVarsOK Then InitVars

As soon as the utility has been terminated by an error, or when the utility hasn't been initialized properly, the public variable bVarsOK will be False and the sub InitVars will be started.