ThreeWave Ensuring Macros Are Enabled, Part 2

This page describes a second technique to allow a workbook to function properly only if macros are enabled.
ShortFadeBar

Introduction

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,

=SUM(A1:A10)+Flip

If Flip has a value of 0, the formula becomes

=SUM(A1:A10)+0

and will return the proper result. If, however, the name Flip as a value of "A", the formula becomes

=SUM(A1:A10)+"A"

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
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Names.Add Name:="Flip", RefersTo:="A", Visible:=False
End Sub

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

-->