Free VB6/VBA profiler and best Excel practices

Question

We have a lot of reports that are generated via VBA & Excel. Only a small percentage of the reports are actual calculations - the majority of the work is sql calls and formatting/writing of cells. The longest of which takes several hours, the majority takes around 20-30 mins each.

The VBA/Excel code plugs into a dll that the VB6 desktop apps use - it's here that all the sql calls are made. While I am sure that there is room for improvement here, it's not this that concerns me - the desktop apps are fairly snappy.

Two VBA functions are used in abundance: These are called GetRange and SetupCell and they nearly always appear together. The GetRange function is a wrapper for the Excel.Range object. It takes a sheet, and 4 values for the extents of the range. Its main use is to pick the cell for editing. There doesn't appear to be much chance of optmising it, but is it the best way?

Its partner is SetupCell. This takes a Excel.Range object, text and a dozen parameters about the cell (font, borders, etc). Most of these parameters are optional booleans but again, it seems very wasteful. Some of these can be set posthumously but some are dependant on the values contained in the cell.

There's quite a lot of code contained in these functions, mainly if statements and work won't appreciate me posting it.

I guess I've got two questions: Is there a better way and what is it and is there are free profiler that I can use to see if the bulk of the time is here or in the dll?

Answer 1

several hours is ridiculous for a report.

If the problem is VBA buy "Professional Excel Development" (stephen Bullen, Rob Bovey et al): this has a free VBA profiler called PerfMon.

If the problem is Excel Calculation see http://msdn.microsoft.com/en-us/library/aa730921.aspx?ppud=4

But I would guess that the problem is the high overhead associated with referencing things cell-by-cell: you should always work in large blocks of cells at a time.

Answer 2

Have you thought about using an actual reporting solution? What's your backend db? If you are using MSSQL 2000 or higher there is a fairly decent reporting solution you can use free of charge. SQL Server Reporting Services.

It sounds as if the reports are spending most of their time formatting cells. This could be why the reports seem so slow and the desktop app doesn't.

Alternatively, if you know the formatting before hand and it is fairly static, you could pre-format the sheets to cut down on some of the work.

I will throw this in there as well. Most reporting solutions will allow for conditional formatting and such, but since they are designed to work as such performance will be much better than having Excel do it.

Answer 3

This isn't a profiler recommendation, but it is a suggestion for speeding up Excel macros that are spending their time updating the screen. I've had excellent results by turning off screen updating while the macro is running: set Application.ScreenUpdating= False, and also using a number of other similar settings. Just be sure to turn them back on again when the macro finishes :P