Thoughts on Workbook Optimization Tools

[Greg Truby]

[keywords to help future searchers hit this thread: reduce calculation recalculation optimize tools formulas]

[I've run through all the threads that search turned up and that I thought looked promising; but I didn't come across a thread that invited an open discussion of this topic. If I missed a key thread, please post a link to it. -- Greg]

Here's the deal. Using knowledge gained by reading books and mostly learning from the formula wizards around here I have created a monster. The workbook has all sorts of amazing array formulae, dynamic named ranges/named formulae, dynamic validation (dependent lists), VBA [userforms (ever programmed a treeview control? that's fun); forcing macros enabled; class modules] -- you name it; it's probably in this feller. It is quite a marvel. And it is slow. Not grinding to a halt slow. But it's mission is to allow for analysis of various segments of leadtime to various overseas customers and with each change of customer/segment/order class the recalcs take several seconds. It makes it a royal PITA to really hunt down specific logistics bottlenecks.

My question is not about specifics on how to improve this. There are so many places to look, I literally don't know where to start. My question is on getting help on learning "where to start". So -->

Who sells the best toolkit for quickly identifying the most effective steps one can take to reduce re-calc times?

I have seen FastExcel by Decision Models mentioned many times here. (But they do not list MrExcel.com among their favorite Excel websites which, of course, makes me wonder!) I would like to hear feedback or recommendations from folks who have used workbook analysis tools. My impression is that such tools can really be helpful; but I would most definitely not enjoy going out on a limb to talk my boss into investing in the software only to yield modest gains in speed. Your thoughts please!

Much obliged,

 

[just_jon]

FWIW, Greg, I know Aladin has FastExcel.

I'm still knuckle-dragging about XL/VBA myself, so I'm surely not going to be of help in this area.

You already have checked out the free advice at DecisionModels about calc times, I presume, and that's about the limit of my knowledge.

Be interesting to see what everybody thinks...

 

[NateO]

Hi Greg,

I assume that FastExcel is your animal for testing worksheet function bottlenecks. I have yet to use it, but... In addition to that, there's some high-quality information at Charles William's site for auditing your application itself. E.g.,

http://www.decisionmodels.com/optspeed.htm

And some free downloads:

http://www.decisionmodels.com/downloads.htm

And, I assume Charles likes our forums, he does post here:

http://www.mrexcel.com/board2/profile.php?mode=viewprofile&u=28417



If it's VBA, there probably are many ways to optimize, but it's going to be process-specific. Generally speaking, iterative and recursive procedures are worth looking at first.

 

[Aladin Akyurek]

Geez, Greg, you are on Excel 2003, aren't you? If so, you can ditch most of your range names defined with Offset, Indirect, or even with Index.

 

[Greg Truby]

Geez, Greg, you are on Excel 2003, aren't you? If so, you can ditch most of your range names defined with Offset, Indirect, or even with Index.

For what? This "list" thing I've seen in the menus but haven't had time to play with? Or something else?

And since you're here (glad you stopped by, BTW) don't you have FastExcel? Would you say it's worth the 80 bucks?

 

[Aladin Akyurek]

Geez, Greg, you are on Excel 2003, aren't you? If so, you can ditch most of your range names defined with Offset, Indirect, or even with Index.

For what? This "list" thing I've seen in the menus but haven't had time to play with? Or something else?

Yes, the list thing. This allows you to work with ordinary ranges that get updated in formulas they are referred to. I have a few posts that mention/propose using it.

And since you're here (glad you stopped by, BTW) don't you have FastExcel? Would you say it's worth the 80 bucks?

In my opinion, the short answer is yes. It allows temporal profiling and, if you have many sheets, reorders them in a optimized sequence.

 

[Greg Truby]

I have a few posts that mention/propose using it.

Actually, Mr. 25,100 posts (as of 16:38 CST) -- a quick search indicates that you have _110_ threads in which you mention both "lists" and "2003". But in looking at a few I now see what you mean. Due to your previous post on this thread, I read Excel's help files on lists for twenty or thirty minutes. Not once did those dipsticks at MS who wrote the help files mention that the addresses of Named Ranges that refer to cells contained inside a list are automatically updated when data is added to the list. Interactively copying and pasting multiple rows into the bottom of the list works just fine. I'll play with VBA-based copying and pasting of multi-row ranges into lists tomorrow to make sure, but at least I have finally learned something useful about this new List feature. Like I said, I'd seen it in the menu and so prior to today, I'd perused the help files briefly and played with the toolbar briefly and hadn't seen anything that really got me to enthused about the things, so I didn't delve into them too deeply. Thanks for the info.

 

[Aladin Akyurek]

...the addresses of Named Ranges that refer to cells contained inside a list are automatically updated when data is added to the list. Interactively copying and pasting multiple rows into the bottom of the list works just fine.

Quite. If the list contains formulas, the downwards formula copying occurs also automatically. BTW, the feature allows for autofiltering.

I'll play with VBA-based copying and pasting of multi-row ranges into lists tomorrow to make sure,...

Great. I'm very curious whether the automatic formula copying will still function when you add/remove records en masse thru code.

Lets get on other performance issues:

1) SumProduct And CSE-Formulas: You can turn the formulas for multiconditional counts/sums into CountIf/SumIf formulas or reduce the number of ranges these formulas test by concatenating the relevant ranges. Concatenation would take place automatically in a list area.

2) Lookup Formulas: Sort the tables if you can and switch to formulas with LOOKUP or INDEX/MATCH with match-type=1. If you have to work with unsorted data, invoke an INDEX/MATCH formula with match-type=0 instead of a VLOOKUP formula.

3) Mega-formulas: Avoid them for they are hard to maintain and more often than not inefficient.

4) Volatile Functions: Try to replace formulas with volatile functions with non-volatile equivalents. That is, INDEX instead of INDIRECT or OFFSET.

Maybe we should proceed per issue...

 

[Greg Truby]

Aladin,

Thank you very much for the tips. I'll take any wisdom you care to share. However that was not the main reason I started this thread. I had already seen the tips section on Charles Williams site; I had not yet walked through them one-by-one, checking my WB for each. My main question - is FastExcel as good as advertised? Are there other products like FastExcel that are as good or better? You've already given your "thumbs up" for FastExcel. Are there other products like FastExcel that you would recommend taking a look at?

And while I am very thankful for input from someone with your level of expertise; I do not feel that I can in good conscience ask you to provide specifics or "proceed per issue" until I have gone through checklists like Mr. Williams provides or like you have provided above. However this particular workbook is key to a major project and I was hoping that FastExcel could help me dial in on where I'd get the most "bang for my buck" as far as tuning up formulae that are dragging down performance rather than trying "hit or miss" methods of trying to figure out which formulas are the most expensive. Will FastExcel help me do that?

 

[Aladin Akyurek]

Aladin,

Thank you very much for the tips. I'll take any wisdom you care to share. However that was not the main reason I started this thread. I had already seen the tips section on Charles Williams site; I had not yet walked through them one-by-one, checking my WB for each. My main question - is FastExcel as good as advertised? Are there other products like FastExcel that are as good or better? You've already given your "thumbs up" for FastExcel. Are there other products like FastExcel that you would recommend taking a look at?

As far as I know there are no profilers like you have for some programming language systems like Lisp and C. That said, worksheet sequence optimization of FastExcel does a good job.

And while I am very thankful for input from someone with your level of expertise; I do not feel that I can in good conscience ask you to provide specifics or "proceed per issue" until I have gone through checklists like Mr. Williams provides or like you have provided above.

Issues mentioned are well-known to people who are not willing to dismiss them to the backseat. I firmly believe that spending time on such issues yields spreadsheet models with better performance. It's certainly not a "hit or miss" dilemma.

However this particular workbook is key to a major project and I was hoping that FastExcel could help me dial in on where I'd get the most "bang for my buck" as far as tuning up formulae that are dragging down performance rather than trying "hit or miss" methods of trying to figure out which formulas are the most expensive. Will FastExcel help me do that?

FastExcel as system doesn't pinpoint to specific formulas which degrade performance. It just gives you a global assessment on recalc and full call times, average formula speed (if there are many), memory usage, etc.