Jon's Programming Blog

Scribble Filter Now Available and Other Muses

I got the sale software all installed so I can sell the Scribble Filter. There’s still a few changes I wanted to make with it. I wasn’t entirely thinking when I added the ability to filter ranges directly when I add (among others). I should have made it more compliant to what Microsoft does. I’ll be working on changing that around now. It will probably be a little while before I incorporate all of that into the filter.

Scribble Filter (For Excel)

Points discussed in video: The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities. Scribble Filter Home Scribble Filter Help Overview Part 1 Overview Part 2 Overview Part 3 Overview Part 4

Scribble Filter: Installation & Opening

The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities. Points discussed in video: Scribble Filter Home Scribble Filter Help

Scribble Filter (For Excel)

Points discussed in video: The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities. Scribble Filter Home Scribble Filter Help Overview Part 1 Overview Part 2 Overview Part 3 Overview Part 4

Scribble Filter (For Excel) Overview Part 3

Points discussed in video: The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities. Special Functions Defined @00:04 Top or bottom (also purpose for chaining) @00:30 Top or bottom by percent @02:10 Above or below average @02:52 Scribble Filter Home Scribble Filter Help Overview Part 1 Overview Part 2 Overview Part 3 Overview Part 4

Scribble Filter Overview Part 1

Points discussed in video: The scribble filter is a wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities. To get the date spans right click on the left calendar button. Use tab or enter to get the first line from the drop down menu. Right click on buttons to get more options (e.g., right click on report button to set type of report to create: On separate sheet, preview, in place, chain).

Sheet Exists

I was looking at Siddharth Rout’s new blog (Adding/Deleting Sheets to the Excel File) and realized that I haven’t posted my version of sheet exists. Mine probably does too much per Code Complete standards, but it is nice not to have to think of multiple. If I were to refactor this code I would take out the code for the charts and probably just make it work only for checking if the sheet exists and adding a new sheet if it doesn’t.

List of Excel Functions

For the advanced filter I need a list of Excel functions. There are lists, unfortunately I needed the list with the function parameters (syntax). I also wanted it by date. I have lists of just the functions for Excel 2003, 2007, and 2010 without the syntax. So I did some screen scraping of Microsoft’s list of Excel functions where I was able to get the function name, category, description, syntax, and website URL for that function.

XML Comments

Comment headers above procedures can be convenient when you want to know what a function does and what the parameters are for – especially if they weren’t named with enough description. Luckily Visual Studio provides a way to do these comments easily and it gives you intellisense for your own functions! They are called XML comments. All you need to do is put in three comment markers (‘) and then it will fill in everything for you (if you already have the procedure/parameters filled in).

AdvancedFilter: Parsing the Search String

One of the most difficult parts of creating an advanced filter class is figuring out how to parse the search data and turn it into a 2D array, which Excel can understand. I figured my user interface will be a string. So I started there. In my original implementation of the search algorithm it wasn’t very robust, so this time around I figured I would do it “right.” Well, right is a relative term.

5