Ensuring Macros Are Enabled, Part 2
This page describes a second technique to allow a workbook to function properly only if macros are enabled.
On the Ensuring Macros Are Enabled Page, we described one technique to ensure that your workbook
or application is started with macros enabled. That technique made visible or hidden sheets critical to the application's usage. The technique
presented on this page is simpler. In short, it causes formulas to return correct values if macros are enabled or to return
#VALUE errors if macros are not enabled. Sheet visibility does not come into play here. It is based on a defined name and
formulas written to work with that defined name.
First, create a defined name called Flip as assign it the value "A". Then, rewrite
critical formulas to use the Flip name. For example,
If Flip has a value of 0, the formula becomes
and will return the proper result. If, however, the name Flip as a value of "A",
the formula becomes
and will return a #VALUE error, since you can't add the text "A" to a number.
With careful analysis of your workbook and formulas, you can use this technique on a relatively small number of cells which will cause the #VALUE errors
to cascade via caclulations through many, many cells in the workbook.
In the ThisWorkbook code module, enter the following code:
Private Sub Workbook_Open()
ThisWorkbook.Names.Add Name:="Flip", RefersTo:=0, Visible:=False
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Names.Add Name:="Flip", RefersTo:="A", Visible:=False
Note thate the name Flip is created with the Visible property set
to False so the name will not appear in the Defined Names dialog. It can be changed only with code. Also, using the
Add method of the Names collection will cause the name to be added if it does not exist or to
be replaced if it already exists.
The Workbook_Open event, which will run only if macros are enabled, sets the value of the name
Flip to 0, which will allow all of the formula that use the Flip name to properly calculate.
When the workbook is closed, the value of the name Flip is changed to "A", which will cause the formulas
to return #VALUE errors. If the workbook is opened later with macros disabled, the name Flip remains
at "A" and the formulas will return #VALUE errors. Only if macros are enabled will the name
Flip be properly set to 0 and the formula will properly calculate.
If the user opens the workbook without marcos enabled, she is confronted with a sea of #VALUE errors
and will likely give up work on the workbook. If she opens the workbook with macros enabled, Flip is properly changed to
0 and the formulas will calculate the propery values.
It is assumed that the Application.EnabledEvents property is set to True when the workbook
is opened and when it is closed. If EnableEvents is False, the workbook will behave as if
macros are disabled even if they are, in fact, enabled.
This page last updated: 16-August-2007