Dictator Add-Ins

Dictator add-ins (often called applications) are much easier to test. (IMPORTANT: "easier" does not necessarily mean "easy"; the term is relative to the committee add-in.) This is because in a dictator add-in you provide the exact functionality your add-in needs and take away all other options.

Dictator add-ins require a heavy emphasis on Microsoft Excel workspace issues. This involves removing or hiding the standard interface items (menus/toolbars) and replacing them with your own. Before doing this, your dictator add-in should save the current workspace. Your add-in can then implement one or more workspaces for its own use and restore the original workspace when exiting.

Here are the steps you take when your add-in loads:

• Take a snapshot of the user's configuration (save workspace).
• Modify interface elements as needed for your add-in.
• Allow the user to run your add-in.
• Trap exiting Microsoft Excel (and provide a way to quit your add-in).
• Return Microsoft Excel to its original state before your add-in ends.

The following is a list of workspace items that a dictator add-in may need to track:

• Workbooks currently open
• Visible CommandBars
• Formula bar
• Status bar
• Sheet tabs
• Scroll bars
• Gridlines
• Calculation mode
• Comment indicator
• Drag-and-Drop
• Link updating mode
• Reference Style
• Current Directory
• Ignore Other Applications

All of these items must be saved so that they can be restored to their original state when your add-in closes; the only question is where they should be saved. There are two commonly used approaches for saving the workspace. The first involves storing workspace settings in variables. The second involves storing them on a worksheet.

In theory, the advantage of using variables is that it makes workspace operations faster. In practice, the time differential may not be large enough to notice. Using array variables does make it much easier to implement multiple workspaces.

Multiple workspaces are useful when you are displaying several different sheets to the user, each of which needs different settings. For instance, you may have a data entry sheet where you wish to suppress the drag-and-drop function, row and column headers, sheet tabs, scroll bars, and the formula bar. You may then build a schedule sheet and an output table where you want different combinations of these settings. If you store all of these workspace settings in an array during startup, you can quickly switch among them at run time.

Most developers will implement this with a user-defined type structure, as shown below:

Option Explicit

'''    This type structure specifies all of the workspace
'''    settings that are being stored.
Type WORKSPACE_STORAGE
    bRowAndColHeaders As Boolean
    bHorzScrollBar As Boolean
    bVertScrollBar As Boolean
    bSheetTabs As Boolean
    bFormulaBar As Boolean
    bStatusBar As Boolean
    ''' Many more settings follow
End Type

'''    This variable will be used to store the workspace 
'''    settings. Note that it is declared as an array so 
'''    that multiple workspaces can be stored.
Dim muWorkspace() As WORKSPACE_STORAGE