Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 12. December 2009 11:43

A few days back, I stumbled upon this page, where Frank Rice describes how to use VBA to list all VBA macros and functions a Workbook contains. I thought that was interesting: it’s not the type of VBA code most commonly seen, and the idea of VBA code interacting with VBA code is fun. So I tweeted it, and Charts GrandMaster Jon Peltier, in his own words,  could not “leave anything alone, and made some changes to how the procedure worked”. Nice changes, if I might add.

I am not one to leave anything alone, either, and wanted to check how well that would work using C#.

Disclaimer: I have done enough checking to know that the code works in non-twisted cases, but this is far from polished. This would need some handling for exceptions before making it to anything shipped to a client you care about, for instance. My goal was to provide a solid code outline, feel free to modify to fit your needs.

The class/method below takes in a fully-qualified file name (i.e. with the full path, just what you would get from an OpenFileDialog), and searches for all the procedures (sub or function) defined in VBA.

As a bonus, I added some extra code to extract the signature of the procedure, and the header comments. The signature - what arguments it takes as input, and what it returns - is a much better summary than simply its name, and I figured that if the author bothered to add comments, it was probably extracting that, too. It also illustrates nicely some of the functionalities of the API.


by Mathias 3. October 2009 15:10

The current version of Akin, my free Excel worksheet comparison application, has been out for a bit now, and people have sent me some interested suggestions on how to make it better. However, my biggest personal issue so far has been speed. Opening large file hasn’t been an issue, but displaying comparisons of large worksheets (say, 200 x 200 cells) was taking a long time. The typical user for Akin is likely to be working with large files (tracking differences wouldn’t be an issue otherwise), so I had to do something about it.

I have bitten the bullet – I changed the design, and completely re-wrote the user interface where the comparison is displayed, and I hope that you will be pleased with the performance improvement. Where a 200 x 200 cells comparison took over 20 seconds to display, a 500 x 500 cells comparison is now virtually instantaneous. While I was at it, I did some cosmetic improvements on the looks as well.

You can download the new version here. Now that this performance problem is out of the way, I can get back to implementing the features that have been suggested so far. Stay tuned!

by Mathias 29. September 2009 16:02

I had an Eureka moment today, and realized that I was trying hard to resolve the wrong problem, when what was needed was a change of frame.

I have been developing an application which tracks differences between Excel worksheets, and displays them in a layout similar to Excel itself. The user can navigate through the sheet on display by using two scrollbars, just like he would with Excel.

To achieve this, my approach so far has been to read the data, create a grid using WPF, adding one cell control to the grid for each cell in the spreadsheet, and navigate “over” that grid using the scroll bars.


This approach works well as long as the spreadsheet is small, but for larger sheets – say, 200 x 200 cells - the time needed to create the cells and add them to the grid was getting pretty long, so I focused my attention on figuring out a way to populate the grid faster, without much success.

Then yesterday, someone wrote this on StackOverflow:

I'm going to go out on a limb here and say that trying to add 40,000 controls is your real bottleneck; not so much as to how you're adding the controls.

Indeed – that’s completely correct.This didn’t answer my issue directly, but it has been liberating, and got me to rethink the question from a different angle – do I really need 40,000 cells?


by Mathias 9. August 2009 17:25

The first of Scott Hanselman’s “Top 10 Tips Working Developers Should Know about Windows 7” really made my day: Win 7 includes .NET 3.5 SP1. This probably doesn’t matter to you if you are not a .NET developer, but if you are, chances are you have had the same frustration as I did. .NET 3.5 SP1 is really what you want to be developing against, because it includes so much goodness, but in my experience, most users don’t have it on their machines. As a result, your potential user has to go through a good 15 minutes of download and a reboot – and that’s assuming the IT department is fine with that, which is not a given (personal experience).

This is of particular importance to me, because my pet project Akin, a free application that helps track down differences between Excel files, is written against .NET 3.5. I really needed WPF to create the kind of user interface I wanted, but this has proven a hurdle in getting people to try it out. You might be able to convince people you know personally that they should install .NET 3.5, but for the casual visitor who stumbles across a webpage and wants to just try out an application, asking them to download a giant file for unclear purposes first is just a killer. You lost one potential user, right there.

With Windows 7 pre-sales making a solid start, hopefully I will be relieved of that issue. This is very motivating – time to get back to it, and implement some of the great suggestions I received so far!


Comment RSS