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

I ran into an interesting situation the other day when trying to import some data into Excel. I had a text file with words and definitions that I needed to have in two columns in Excel, one for the word and one for the definition.

The words and their definitions were separated by a hyphen (-), so I could have gone ahead and done a direct import and specified the hyphen as the delimiter. The tricky part was that the hyphen appeared in some of the definitions too, and so using it as a delimiter would have split some of the definitions into separate columns, and I would have had to go through the sheet and reunite the definitions. Given that I had over 10,000 entries, this was a less than optimal solution, and not very time efficient.

I work a lot with Excel and Excel formulas, but I am by no means an Excel guru, and I am sure there’s a pretty simple solution, maybe a macro or something, for this kind of situation. But I used a formula to solve my problem as explained below:

What I did was paste the contents of my text file into one column with no separation between word and definition, with each entry in it’s own row. I then wanted to split the column into two, one with the word and one with the definition. I then used the right, left, len, and find functions. So with my data in column A, I put the following formulas columns B and C respectively and filled down.

=LEFT(A1,FIND(”-”,A1)-1)
=RIGHT(A1,LEN(A1)-FIND(”-”,A1))

This worked perfectly, and solved my problem in seconds. Do you have any suggestions for a different way to do this?

In response to my post on importing large files into Excel by first splitting them, one of my readers, JP pointed out to me that you can bypass the splitting step by using a VBA macro to do the import.

As you may know, Excel has a cut off of 65,536 rows, and so if you want to import a csv or text file that has more rows than that, you’ll run into trouble. This is where the csv splitter I mentioned before, or this macro that JP pointed out to me will come in handy.

You can find the knowledge base article with the macro code, for a VBA solution to importing large files into Excel at http://support.microsoft.com/kb/120596.

And be sure to visit JP’s excellent website and blog VBA Code for Excel and Outlook where you’ll find a lot of useful macros and articles.

If you have a large csv file that you have tried to open in Excel, you know how troublesome that can be, because Excel is limited in the number of rows and columns of data it can handle - 65,536 rows of data and 256 columns per worksheet. Truncation of rows or columns in excess of the limit is automatic and is not configurable.

I discussed this problem before in the post Splitting large csv files - the CSV Splitter where I introduced you to Scorpion’s nifty little program, the CSV splitter, which takes large csv files and splits them into separate smaller files and you decide how many rows you want each file to be. Previously, as you will see if you read my post on Splitting large csv files, you had to register on the forums to be able to download it. Scorpion just updated me that he has now provided a direct download link so that you no longer have to register but can download the csv splitter directly.

You can now download the csv splitter directly here.

The program is easy to use and is a lifesaver if you are like me and frequently work with large csv files. You can Splitting large csv files - the CSV Splitter read more about the csv splitter, and please do leave a thank you for Scorpion.

Sometimes when working with large excel spreadsheets, you run into the problem of duplicates, and it helps to be able to find them and delete them without having to manually go through the spreadsheet. If you have two or more columns of data, and you need to find and delete the duplicates, this is one way to do it. Say you have all your data in columns A and B:

1. Create a column that concatenates the information in A and B. You can either use the “CONCATENATE” function or use the ampersand (&) by entering the formula A1&B1 to cell C1. Remember, as with all formulae, you have to use the = sign before the formula, thus =A1&B1.
2. Copy and paste the formula down the column to the end of your data. For example, if your data runs from rows 1 to 120, copy and paste your data down to cell C120. An easy way to do this is, in Windows, select from the cell where your formula is to the last cell, and press Ctrl+D, or Edit -> Fill Down. For a mac, I’m not sure there’s a short-cut, except using Edit -> Fill Down.
3. The next step is to find and mark the duplicate entries. Go to column D, and in the first cell enter the formula enter the formula =IF(COUNTIF($C$1:C1,C1)>1,”Duplicate”,”Unique”
4. Copy and paste this formula down the column to the end of your data using the same process as in step 2.
5. This column now shows you what rows have duplicates and which have uniques. You now want to delete, or do something else to just the duplicates, so you want to sort your spreadsheet by duplicates and uniques. You can’t do this with your spreadsheet as it is now because of the formula, so copy all the data to a new worksheet, but you only want to paste values, not the formulas, so that you can sort them by column C. Use Edit -> Paste Special -> Values only.
6. Sort your spreadsheet using Column C, so that you all the duplicates are at the end of your spreadsheet, and once you delete them, or do whatever you want to with them, you can then delete column C.

This leaves you with only unique entries in your worksheet. You can extend this method to multiple columns for more complex spreadsheets and it should work fine.

Happy Coding!