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.