First, I would like to thank Mary for giving me this opportunity to introduce my first Excel add-in on her website. It is an excellent programming resource.
My purpose in this blog entry is to describe the Data Filter Tool, an add-in I created for Microsoft Excel, as well as discuss some steps you can take to make sure your Excel add-in works properly.
This add-in does one thing: it filters a table of data and copies or moves matchingrows to a new workbook. For example, if you have a list of addresses in Excel, this add-in lets you filter by state and copy (or move) all the rows matching a specific state to a newly created workbook. Headers are always copied, even if you select the Move option, to preserve headers in the original data. It preserves formatting of the original data (bold, italic, etc) but copies values, not any underlying formulas. So I don’t recommend you use this tool if your data contains formulas; it’s mostly for tabular data like addresses, contact information, etc.
The dialog box is devastatingly simple: you just select the range to be filtered, the column and unique data item to base the filter on, select what option you want (Copy or Move) and then click ‘Apply’.
Let’s peek under the hood to see how the add-in operates, and hopefully that will give you some idea of how to create your own add-ins.
The first thing we do is some start-up checks to make sure we are in the right environment. Please note that some of the code I will demonstrate actually comes from “Microsoft Excel 2000 Power Programming with VBA”, written by John Walkenbach, ISBN 0764532634. We check the OperatingSystem Property of the Application Object to make sure we are in a version of Windows.
Function WindowsOS() As Boolean
If Application.OperatingSystem Like “*Win*” Then
WindowsOS = True
Else
WindowsOS = False
End If
End Function
Notice that even though the default value of a Boolean variable is False, I never assume that this will work properly so I always explicitly give a variable a value. We use this code in the Workbook_Open() event of the ThisWorkbook module as such.
If WindowsOS = False Then
MsgBox “This add-in is for Windows OS only.”, vbCritical
ThisWorkbook.Close False
Exit Sub
End If
This will close the add-in if someone tries to open it in a non-Windows setting. If your code is only intended for Excel 2000 or later, test for version compatibility with this code:
If Val(Application.Version) < 9 Then
MsgBox “This add-in will only work in Excel 2000 or later.”, vbOKOnly
ThisWorkbook.Close False
Exit Sub
End If
Before adding a menu item to the Data>Filter Menu, we always try to delete the menu, in case Excel did not close properly and the Workbook_BeforeClose Event was not able to run. I set an object reference to the Filter Menu like this:
Dim cb As CommandBarControl
Set cb = Application.CommandBars(“Data”).FindControl(ID:=30031)
Then we delete the menu, using an ‘On Error’ Statement to skip any errors, in case themenu item doesn’t exist, which is exactly what we want.
On Error Resume Next
cb.Controls(“Data Filter Tool”).Delete
On Error GoTo 0
If we passed all our startup checks, the add-in is installed and allowed to run.
In this post I showed you my new add-in and demonstrated some techniques for securing your own code. Hopefully you can use some of these techniques in your own add-ins.
To download the Data Filter Tool, visit http://www.codeforexcelandoutlook.com/DataFilterTool.zip
and of course check out my site at http://www.codeforexcelandoutlook.com/.
Enjoy,
JP
