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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

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

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Create Addins > 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.