Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Create add-ins > Initialise
Deze pagina in het Nederlands

Creating an addin from an Excel macro.

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.