Professional Excel Development has a chapter on optimization that discusses the PerfMon utility (available on the companion CD). I used it for the first time on a 40 second process and I thought I would share the results. Thrilling, I know.
One hundred fifty thousand calls to class properties? Yikes! Noting that FillFinals was the biggest culprit, I manually added some perfmon calls inside that procedure to see what I could see.
Inserting the final reports consists of adding sheets to the final report workbooks, among other things. In this case it adds nine sheets to six different workbooks. I decided to break up that block of code even further. Specifically, I wanted to isolate the Sheets.Add line.
I guess adding sheets takes a lot of time. Maybe I should create a report with some ‘final reports’ already in it so I can limit the amount of sheets that I have to add. Of course I’ll have to delete extraneous sheets, so I’ll have to weigh the costs of that. Well, nothing earth shattering here. It was just the first time I used it on a real program and it was fun.
A couple of bugs I noted in the utility:
It puts PerfMonProcEnd statements before any Exit Sub statements, but when it deletes them it doesn’t respect my original tabbing.
My manual lines looked like PerfMonProcEnd “FireAssay.MProcess.FillFinals.HeaderData”. I don’t think I was supposed to put a period after FillFinals (the procedure name) because the output file added another column. That’s OK, but it didn’t adjust the headers. In the screen shots above, I manually adjusted the headers and added a Section header. It’s probably user error rather than a bug.