Excel Add-In (XLA) Best Practices and Design Techniques
This article contains information regarding best practices for developers creating add-ins using Excel's VBA editor in Office 2003. They may also apply to other Excel versions. I will update this page from time to time.
Create a separate procedure to start your userform (if any)
If your add-in is driven by a userform interface, use a separate procedure to run the form. For example:
Dim frm As MyForm ' Assuming the userform is called MyForm
' assuming the VBAProject is named 'MyProject'
Set frm = New MyProject.MyForm
With frm
.Caption = "My form"
.Show
End With
End Sub
This procedure can then be called by your Workbook_Open code, a toolbar control, menu item, or another add-in.
Give unique names to your modules and VBA Project
Your add-in may not be the only one installed on a user's computer. It may also have to compete with other products using similar functionality as yours. It's important, therefore, that your VBA Project, modules and methods have unique names that distinguish them from others. A common practice is to prefix everything with the initials of the particular product being produced, or the company that is commissioning the product. This is usually agreed upon by the program's developers during scoping. The chances of a method-clash with another product, already small, are infinitely smaller when this naming convention is used. This might be overkill, however, so usually just renaming the project (and fully qualifying your method calls with the project name) should be sufficient for most purposes.
Don't leave your project as the default 'VBA Project'. Rename it according to the specifications above to avoid ambiguity. It's as simple as clicking the name of the project, pressing F4 and then typing a new name.
Modules should be named according to the types of procedures they store, and different types of procedures should be grouped into different modules. For example, a typical Excel add-in might have code for handling errors, automating other Office applications, global variables, enumerations, types, miscellaneous helper routines, and so on. Each of these should be grouped into its own module, appropriately named so the contents are obvious. I prefer to use nouns without any prefix – 'Globals' for all the globals, 'ErrorHandling' for the error handling and logging routines, etc.
Fully qualify your global variable and method calls
To avoid ambiguity when calling global constants, methods in other modules, and userforms, they should be fully qualified. The reason for this is that you never know what other add-ins the end user might have. Making an unqualified method call may lead to an ambiguous reference which VBA cannot resolve. See above for reasons why you should use unique names for your objects.
Separate different types of code into different modules
Code that handles errors should be placed into its own module. Globals and enums should also be placed in separate modules, unless they are directly related to, and only used by, specific functions.
For example, public constants (such as the name of the product) can be placed into a module called Globals, but the constants used by the error handling code can be placed directly into the error handling module.
Add file properties to create a professional look
Make sure you fill out the properties section of the file. This is found by going to File » Properties. The information you enter into the Title and Comments fields will appear in the Add-Ins window when you go to Tools » Add-Ins…. Your user base will be able to see the name of the app, as well as a short description. You can also include a copyright statement and a (text) link to your website in these fields.
Avoid direct method calls
It's the "deadly dot notation" that will make your code harder to read. I'm referring to code that has objects with multiple "dots" leading to method calls, i.e. Excel.Application.Worksheets(1).Range("A1"). Yuck.
Instead of calling a method directly (i.e. Application.ScreenUpdating = False), create a function in a standard module which does the work for you, and call that instead. You can call it from multiple places throughout your code, and if you ever need to change the way it works, you only need to change it in one place.
The applications for this technique are endless. Instead of calling the Enabled Property for a form control, write a function that takes the control as a parameter, and a boolean variable to set the Enabled Property. For example,
Optional isEnabled As Variant)
' toggles Enabled property for whatever control passed to it
' isEnabled is declared as Variant so we can use IsMissing
' if boolean is missing, do a simple toggle, else use boolean
If IsMissing(isEnabled) Then
ctl.Enabled = Not ctl.Enabled
Else
ctl.Enabled = isEnabled
End If
End Function
Instead of calling the SetFocus method, write a function that takes the control as a parameter and sets focus to it.
' gives focus to whatever control is passed
ctl.SetFocus
End Function
To empty out the value of certain controls (perhaps as part of a Reset button), instead of placing all the code in the form's event handler, write a procedure that loops through the form's controls and clears their value.
Dim ctl As MSForms.Control
Dim ctlArray() As Variant
ctlArray = Array("ComboBox", "TextBox", "ListBox")
' clear controls
For Each ctl In frm.Controls
If IsInArray(ctlArray, ControlType(ctl)) Then
ctl.Value = ""
End If
Next ctl
End Function
Function IsInArray(arr() As Variant, valueToCheck As Variant) As Boolean
' returns true if value is found in array
IsInArray = (UBound(Filter(arr, valueToCheck)) > -1)
End Function
In short, the code in the body of your form's event handlers should largely be method calls to other procedures located outside the form that take parameters you can pass to the function to perform the operations you need.
Protect your code
To prevent casual users from seeing or touching your code, go to Tools » MyProject Properties… (assuming the VBAProject is named 'MyProject'). Go to the Protection tab and enter a password. Make sure you memorize or write the password down, as there is no way to recover it if you lose it. Please don't write me to tell me how insecure VBA projects are, I know that.
Code for the lowest version
Use objects and methods found in the lowest version your add-in will support. For example, if your users have Excel 2003, but some have Excel 2002 (XP) or 2000, write the add-in using objects and methods found in Excel 2000 through 2003. Don't use anything that was introduced in Excel 2003. See Excel 2003 Review for new features. You don't have to write the program in Excel 2000, just that you should only use methods that are available to all versions.
Obviously, this precludes Excel 2007 or 2010, since they use a different file format. The FileSearch object has been deprecated in those versions, so if your add-in will be used in previous versions as well, you'll need to code differently. The following functions will help you determine the current user's Excel version. Call IsValidVersion() to check if the end user is running a version prior to Excel 2007.
If most people just upgraded to Excel 2007, this wouldn't be as much of an issue. But I've never seen another application that had usage spread across versions this way. When a new version of FileZilla or Shockwave comes out, I just upgrade without question. Due to the Ribbon introduced in Excel 2007, many users have been loathe to adopt it, therefore it's important that you write code that takes this into account.
Public Const EXCEL_2000 As Integer = 9
Public Const EXCEL_2002 As Integer = 10
Public Const EXCEL_2003 As Integer = 11
Public Const EXCEL_2007 As Integer = 12
Public Const EXCEL_2010 As Integer = 14
Function GetVersion() As Long
' returns application version
GetVersion = Val(Application.Version)
End Function
Function IsValidVersion() As Boolean
' returns true if application version is higher
' than XL 2000 (9.0) but less than 2007 (12.0)
Dim vers As Long
vers = GetVersion
IsValidVersion = ((vers >= EXCEL_2000) And (vers < EXCEL_2007))
End Function
Use constants instead of hardcoded strings
I add the following section to my Globals module, and use these constants in place of hardcoding.
Public Const CHAR_COPYRIGHT_SYMBOL As String = "©"
Public Const CHAR_TRADEMARK_SYMBOL As String = "™"
Public Const CHAR_REG_TRADEMARK_SYMBOL As String = "®"
Public Const CHAR_LAQUO As String = "«"
Public Const CHAR_RAQUO As String = "»"
These are the more popular characters I find myself using in my add-ins. Notice the space character, so I don't have endless quotation marks sprinkled throughout my code that I need to debug.
Some functions or properties (ThisWorkbook.Path) require you to append a backslash to path names. Instead of "\", use Application.PathSeparator.
Abstract out your Application Name
Another constant you should use is your add-in's name.
To reference the application name throughout your project (as dialog box titles or label captions), use MyProject.APP_NAME (assuming the VBAProject is named 'MyProject'). This will make it much easier to change the application name, for example if you include a version number in the name, you can change it in one place and the changes will propagate throughout your code.
Use late bound automation
You never know what version of a program or file a user may have on their system. Late binding allows you to use automation with another program without (for the most part) having to worry about version dependencies. Use the lists of enumerated constants and write your code early bound, then convert your objects to Object type using the approach found at Take advantage of Intellisense when writing late bound code.
It will be easier to automate another program because you can check if the program is installed before attempting to use it. With early bound, you depend on the existence of the program in a way that can render your add-in unusable should the program not be installed. With late bound code, you can check this and exit gracefully or provide an error message to the end user.
For example, this code sets a late-bound reference to the Outlook.Application object.
On Error Resume Next
Set GetOutlookApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set GetOutlookApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
End Function
If GetOutlookApp returns Nothing, you can let the end user know. If your code was early bound, and Outlook was not present on the machine, your code will simply bomb.
