Walkenbach J. Excel 2007 Power Programming with VBA / Optimizing the Performance of Add-Ins

Computers & Technology
Education & Reference
Business & Investing
Science & Math

Excel 2007 Power Programming with VBA
Authors: Walkenbach J.
Published year: 2007
Pages: 219-220/319

<< Previous page
Table of contents
Next page >>

Optimizing the Performance of Add-Ins

If you ask a dozen Excel programmers to automate a particular task, chances are that you'll get a dozen different approaches. Most likely, not all these approaches will perform equally well.

Following are a few tips that you can use to ensure that your code runs as quickly as possible. These tips apply to all VBA code, not just the code in add-ins.

Set the Application.ScreenUpdating property to False when writing data to a worksheet or performing any other actions that cause changes to the display.
Declare the data type for all variables used and avoid variants whenever possible. Use an Option Explicit statement at the top of each module to force yourself to declare all variables.
Create object variables to avoid lengthy object references. For example, if you're working with a Series object for a chart, create an object variable by using code like this:
```
Dim S1 As Series
Set S1 = ActiveWorkbook.Sheets(1).ChartObjects(1). _
    Chart.SeriesCollection(1)
```
Whenever possible, declare object variables as a specific object type - not As Object .
Use the With-End With construct, when appropriate, to set multiple properties or call multiple methods for a single object.
Remove all extraneous code. This is especially important if you've used the macro recorder to create procedures.
If possible, manipulate data with VBA arrays rather than worksheet ranges. Reading and writing to a worksheet takes much longer than manipulating data in memory. This is not a firm rule, however. For best results, test both options.
Avoid linking UserForm controls to worksheet cells . Doing so may trigger a recalculation whenever the user changes the UserForm control.
Compile your code before creating the add-in. This could increase the file size , but it eliminates the need for Excel to compile the code before executing the procedures.

Special Problems with Add-Ins

Add-ins are great, but you should realize by now that there's no free lunch . Add-ins present their share of problems - or should I say challenges? In this section, I discuss some issues that you need to know about if you'll be developing add-ins for widespread user distribution.

Ensuring that an add-in is installed

In some cases, you might need to ensure that your add-in is installed properly: that is, opened using the Add-Ins dialog box and not the Office Open command. This section describes a technique that determines how an add-in was opened and gives the user an opportunity to install the add-in if it is not properly installed.

If the add-in isn't properly installed, the code displays a message (see Figure 21-8). Clicking Yes installs the add-in. Clicking No leaves the file open but doesn't install it. Clicking Cancel closes the file.

image from book
Figure 21-8: When attempting to open the add-in incorrectly, the user sees this message.

The code that follows is the code module for the add-in's ThisWorkbook object. This technique relies on the fact that the AddInInstall event occurs before the Open event for the workbook.

Dim InstalledProperly As Boolean

Private Sub Workbook_AddinInstall()
    InstalledProperly = True
End Sub

Private Sub Workbook_Open()
   Dim ai As AddIn, NewAi As AddIn
   Dim M As String
   Dim Ans As Integer
   'Was just installed using the Add-Ins dialog box?
   If InstalledProperly Then Exit Sub

   'Is it in the AddIns collection?
   For Each ai In AddIns
      If ai.Name = ThisWorkbook.Name Then
         If ai.Installed Then
             MsgBox "This add-in is properly installed.", _
               vbInformation, ThisWorkbook.Name
             Exit Sub
         End If
      End If
   Next ai

    'It's not in AddIns collection, prompt user.
    M = "You just opened an add-in. Do you want to install it?"
    M = M & vbNewLine
    M = M & vbNewLine & "Yes - Install the add-in. "
    M = M & vbNewLine & "No - Open it, but don't install it."
    M = M & vbNewLine & "Cancel - Close the add-in"
    Ans = MsgBox(M, vbQuestion + vbYesNoCancel, _
      ThisWorkbook.Name)
    Select Case Ans
        Case vbYes
            ' Add it to the AddIns collection and install it.
            Set NewAi = _
              Application.AddIns.Add(ThisWorkbook.FullName)
            NewAi.Installed = True
        Case vbNo
            'no action, leave it open
        Case vbCancel
            ThisWorkbook.Close
    End Select
End Sub

The procedure covers the following possibilities:

The add-in was opened automatically because it's an installed add-in, listed (and checked) in the Add-Ins dialog box. The user does not see a message.
The user uses the Add-ins dialog box to install the add-in. The user does not see a message.
The add-in was opened manually (by using Office Open), and it's not a member of the AddIns collection. The user sees the message and must take one of the three actions.
The add-in was opened manually, and it's a member of the AddIns collection - but it is not installed (not checked). The user sees the message and must take one of the three actions.

By the way, this code can also be used as a way to simplify the installation of an add-in that you give to someone. Just tell them to double-click the add-in's filename (which opens it in Excel) and respond Yes to the prompt. Better yet, modify the code so the add-in is installed without a prompt.

CD-ROM

This add-in, named check addin.xlam , is available on the companion CD-ROM. Try opening it using both methods (the Add-Ins dialog box and by choosing Office Open).

Referencing other files from an add-in

If your add-in uses other files, you need to be especially careful when distributing the application. You can't assume anything about the storage structure of the system that users will run the application on. The easiest approach is to insist that all files for the application be copied to a single directory. Then you can use the Path property of your application's workbook to build path references to all other files.

For example, if your application uses a custom help file, be sure that the help file is copied to the same directory as the application itself. Then you can use a procedure like the following to make sure that the help file can be located:

Sub GetHelp()
    Application.Help ThisWorkbook.Path & "\userhelp.chm "
End Sub

If your application uses Application Programming Interface (API) calls to standard Windows DLLs, you can assume that these can be found by Windows. But if you use custom DLLs, the best practice is to make sure that they're installed in the Windows\System directory (which might or might not be named Windows\System ). You'll need to use the GetSystemDirectory Windows API function to determine the exact path of the System directory.

Detecting the proper Excel version for your add-in

As you may know, those who use an earlier version of Excel can open Excel 2007 files if they've installed Microsoft's Compatibility Pak. If your add-in uses any features unique to Excel 2007, you'll want to warn users who attempt to open the add-in with an earlier version. The following code does the trick:

Sub CheckVersion()
    If Val(Application.Version) < 12 Then
        MsgBox "This works only with Excel 2007 or later"
        ThisWorkbook.Close
    End If
End Sub

The Version property of the Application object returns a string. For example, this might return 12.0a . This procedure uses VBA's Val function, which ignores everything after the first alphabetic character.

CROSS-REFERENCE

See Chapter 26 for additional information about compatibility.