Daily Dose of Excel

This report comes from Charles Williams of DecisionModels.com fame. Charles knows a thing or two about performance in Excel.

For the last few weeks I have been trying to find out why clearing a large range of cells caused Excel to hang on some PCs but not on others. This does not happen with Excel 97, but does with Excel 2000, 2002, 2003 and 2007.

It turns out that its not just Clear, but also Delete or transferring data from a variant to a range, or even just selecting a large range of cells. The further down the sheet and to the right the slower it gets. And the more recent the Excel version the slower it gets.

Someone on the newsgroups discovered that, when using VBA, you could bypass this problem by switching off EnableEvents, and then someone else discovered that the culprit was Google Desktop Search.

The problem actually turns out to be the Google Desktop Office Com Addin. When you deactivate this you get a miraculous speedup.

With Excel 2007 it is fairly easy to deactivate:
Office Button–>Excel Options–>Addins–>Com Addins and deselect Google Desktop Office Addin

With earlier versions of Excel you have to customise a toolbar and add the Com Addins dialog to it.
View–>Toolbars–>Customise–>Commands tab–>Tools then about halfway down you will find Com Addins, select and drag to the toolbar of your choice.
Then you can uncheck Google Desktop Office addin.

If you have multiple versions of Excel installed you only have to do this once.

Presumably this COM addin sets up one or more application-level events to monitor things like Selection Change and Worksheet change and then tries to trap the change in order to index it.

If you want to measure this effect you can download a Variant Benchmark Timer from my website that allows you to run a read and write benchmark with and without EnableEvents.

DecisionModels.com/Downloads

This represents an interesting new twist in the Google-Microsoft wars!

Thanks Charles.

Dave Gainer writes:

Two weeks ago, we posted about an issue involving the calculation of numbers around 65,535 and 65,536. As of today, fixes for this issue in Excel 2007 and Excel Services 2007 are available for download from the following locations:

Via Slashdot:

It seems that any formula that should evaluate to 65,535 will act strangely.

Slashdot article references this newsgroup post.

I'm trying to run this code:

and gsSTAN is a global constant set to 'Standards.xls'. I forgot to create the folder gsTANDFLDR and, of course, the file is not in there either. I get this error message:

The third bullet point is what strikes me as strange. Why is the file name in there? You may have noticed that it's not a typical message box. The error is run through a error handler, but the message is unchanged.

Hi all

For the users of a non English version of Excel 2007 maybe this information is useful.
http://www.rondebruin.nl/atp.htm

Have a nice day

Ron de Bruin
http://www.rondebruin.nl/tips.htm

As many of the frequenters of this great place will know, Charles Williams and I created The Excel Name Manager. The # 1 (FREE) tool on the web to work with defined names in Excel.

Well, we do occasionally get feedback on this tool (which is always appreciated!) and in all these years, we got two reports stating the tool didn't work at all, showing an "Invalid Picure" error which effectively prevented the tool from working.

So this appears to be a rather obscure error.

Colin Delane was kind enough to help me troubleshoot this problem (as he was the one experiencing it).

After some detective work, we nailed down the problem to one particular commandbutton on the NM's main form. This button has a picture (as the others on the top have too):

Screenshot of Name Manager buttons. I circled the offending button. Note that this button only shows in the FastExcel version of NM.

The odd thing is, that it appeared to be the format of the picture that was causing havoc.

Both Meta and BMP are OK (no error on user's system), but when we loaded a GIF picture format, the error occured reproducably.

Has anyone seen this before?

Regards,

Jan Karel Pieterse
www.jkp-ads.com