Woody's OFFICE 2003

Woody Leonhard has a close look at the latest Microsoft Office.

More on the hidden fix for the RAND() bug
22 January 2004 - Vol 2 No. 01
 

5Ads make Woody's Watch possible, please support our sponsors5How to advertise click here5

Excel 2003 fix released ‘sort of’

Microsoft has released a fix for the RAND() bug disclosed here in Woody’s Office 2003 late last year, though you’re all forgiven for not knowing about it since the company is doing its best to hide it away.

We’ve received no direct news of the patch, even though it was Woody’s Watch that first published details of the problem.  Instead the company has leaked the news to various tame journalists who they can trust to give them an easy ride.

A hotfix for Excel 2003 is now available if you’re prepared to go through the hoops to get it.  The details are at http://support.microsoft.com/default.aspx?kbid=833618 and contain some surprises and disappointments.

The surprise (not a big one) is that there are more bugs in Excel 2003 being fixed in this update.  Some are annoying but others are serious:

5Ads make Woody's Watch possible, please support our sponsors5Info on advertising click here5

Here's the list of fixes direct from the Microsoft list:

“The following issues are fixed in this hotfix package:

834520 The RAND function returns negative numbers in Excel 2003

832334 Excel 2003 stops responding or you receive an error message when you open an XML spreadsheet

834009 The font style of text that is contained in a Microsoft Excel Worksheet Object may change in Word and in PowerPoint

The following issues are also fixed in this hotfix package but were not previously documented in a Microsoft Knowledge Base article:

 When you use a custom list in Excel 2003 on a computer that has German (Switzerland) regional settings, the custom list may not contain German values.

When you delete cells in an Excel 2003 worksheet, and then press SHIFT+F9 to recalculate the worksheet, Excel may quit unexpectedly.

 When you use the Range.Calculate or the Selection.Calculate method in a Microsoft Visual Basic for Applications (VBA) macro to update a range of cells in an Excel 2003 worksheet, the cells may not update. This problem may occur when you open a workbook that contains links to other data sources and you click Update.

If you save and close an Excel 2003 workbook that contains null values from an OLAP cube in the PAGE field of a Pivot Chart, Excel may quit unexpectedly when you reopen the workbook.

In an Excel 2003 worksheet, if you insert a hyperlink in a cell that contains text with a line break, the text that comes after the line break is deleted.

When you calculate a range of cells in Excel 2003, some values may not change or update and you do not receive a message that explains why the update was not performed. This problem may occur if the range of cells contains a circular reference.

When you use a VBA macro to calculate your worksheet, a custom function from a different worksheet may appear to run.

When you send an Excel 2003 workbook to another user account in an e-mail message or store the file on a network share, with the print settings set to print to your mailbox or to a local printer with specialized settings, these settings may still be in effect for the other users. If they print the workbook, it may either print to your mailbox (not theirs), or it may try to print by using certain settings for your local printer.

 When you programmatically use the Worksheets.Add method to insert a saved worksheet, Excel 2003 may quit unexpectedly.

When you create multilevel subtotals for your data in an Excel 2003 worksheet, the totals may appear staggered incorrectly, and may exclude grand totals for some functions.

After you upgrade to Microsoft Office 2003, you may not be able to import a workbook into your Microsoft Windows SharePoint Services version 1.0 Web site, and you may receive the following error message:

Cannot connect to the server at this time. Your list cannot be published.

When you open an Excel 2003 workbook that contains many text boxes, the workbook may open very slowly. This problem may occur when your zoom settings are not at 100% when you open the workbook.  "

There’s all sorts of strange things about this list.

Firstly it refers to bugs that "were not previously documented" as if the RAND() bug had been previously disclosed.  To the best of our knowledge the RAND() bug been conspicuous by its absence from the Knowledge Base since the problem came to the company’s notice more than a month ago.   Even paying callers to Microsoft Tech Support have been told there is no such bug so clearly the news has not been distributed internally let alone to users.

Secondly, Microsoft is trying one of their old tricks to obscure the number of bugs in their products.  Each of the ‘extra’ fixes deserves its own Knowledge Base article, if only so customers can easily find details of a bug they have struck.  Bundling fixes into a brief list is good for Microsoft but a poor way to treat customers.

Fans of “The West Wing” may remember ‘Take out the trash day’ where embarrassing stories are released to comply with the basic disclosure requirements but in way that makes it likely they’ll be overlooked.   Bundling bug lists like this is Microsoft’s equivalent for software.

Third, looks at these ‘fine print’ fixes in detail.  Some are nuisances like Excel crashing when you do certain things but we see two critical problems where Excel does not properly update cells.  This means you could have a worksheet that isn’t showing correct results.  No wonder Microsoft is burying these into a brief list – non-updating cells is far too embarrassing.

Getting the Hotfix

You probably figure that having released a fix, Microsoft has done the right thing but sadly it’s not an easy matter to get the patch.

To get the fix you have to apply to Microsoft support and convince them that you have one of the problems resolved in the hotfix.

There’s various ways to do that, listed here.

The email method would seem to be the easiest unless you like spending time waiting on the phone.  If past experience is anything to go by, you may be asked to pay a support call fee first, then apply for a refund later.

If you have already reported the RAND() problem to Microsoft you might expect the company to contact you with news of the final fix.  Well you might wait in vain, as far as we can tell, none of the customers who have logged RAND() bug reports have been contacted with news of the patch.

Don’t bother going to the Office Update web site – despite all the hype and hoopla about this method of revising your copy of Office there’s no mention of this important patch on the automated system.

Applying the hotfix

Having gone cap in hand to Microsoft and extracted the fix for their mistakes you have to apply the patch.  That’s not as straight-forward as it should be.

To fix the “The font style of text that is contained in a Microsoft Excel Worksheet Object may change in Word or in PowerPoint” problem you not only have to apply the patch but also add a registry entry. The details here  but you might ponder why the patch doesn’t add the registry automatically.  We can’t see any reason since the patch system can easily handle adding or changing a registry key.

Suggested RAND() workaround

Over the New Year we received an impressive message from Professor Richard Engelbrecht-Wiggans, U of Illinois, Urbana-Champaign, Illinois  (which SF aficionados might recall was the ‘birthplace’ of HAL from 2001: A Space Odyssey).  Prof Engelbrecht-Wiggans suggested another workaround for the RAND() bug and backed it up with some formidable analysis – a 24MB worksheet no less.

And who are we to argue with place that ‘made’ one of the world’s most famous computers <g>.

In effect Richard has come up with a single cell way to add 1 to any negative result, which was the workaround suggested by Earl Takashi last year.

His workaround formula is  =MOD(RAND(),1) which you can use as a replacement for RAND() or in a custom formula .

Here's a summary of what Professor Engelbrecht-Wiggans has tried to achieve:

“

1. Suggested a simpler way than previously suggested to, in effect, add 1 to negative outcomes.

2. Taken a sample of 100,000 consecutive outcomes that included many negative outcomes, added 1 to each of the negative outcomes, and ran a series of standard statistical tests on the result.

a) The sample passed all the tests for uniformity, but

b) The sample dramatically failed the "runs test" for independence.  (see Law and Kelton's “Simulation Modeling and Analysis," McGraw Hill, pages 419-420 in the 3rd edition) at any level of significance greater than 0.0005775.   “

In other words, for most ordinary uses, this RAND() workaround will be sufficient, at least it won’t give negative results that could break your worksheet entirely.

But if you make heavy-duty use of RAND() in sophisticated analysis then this workaround may not be enough.

Problem – what problem?

Continuing their display of arrogance, Microsoft has released a fix for the bug in Excel 2003 but continues to promote the software as having an ‘improved’ random number feature.

Check out Knowledge Base article 828795 which we’ve referred to in the past.  This page boasts the supposedly improved features of the Excel 2003 RAND() function and has not been changed since November 2003.  You could easily read that page and think RAND() is not just OK but improved in Excel 2003.

There’s no link to the patch or belated KB article about the serious bug.  Microsoft continues to make the knowingly false statement that Excel 2003 RAND() is “A simple and effective algorithm has been implemented. The new generator passes all standard tests of randomness. ”.  As we’ve mentioned before, this improved function may have passed tests for randomness, but we wonder how since any proper test would detected the presence of negative numbers.

Our recommendation

With the greatest reluctance we have to suggest that anyone with more than trivial use of RAND() should steer clear of Excel 2003.  Better to stick with Excel 2002 – otherwise called ‘the devil you know’.

There are too many unknowns with Excel 2003 in either its patched or unpatched form.  Microsoft seems interested only in hiding the problem from customers and making it as difficult as possible to get the patch for this serious problem.  The list of potentially serious but glossed over bugs adds to our concerns about the reliability of Excel 2003, to say nothing of the credibility of Microsoft.

The company has notably not released any detailed analysis of the repaired RAND() function so that users with demanding needs can assess whether the repaired function is suitable to their needs.   All Microsoft has admitted is that the fixed RAND() won’t return negative numbers – you have to hope that the distribution of numbers is acceptable.

Oh yes, and you have to assume that RANDBETWEEN() has also been fixed – a reasonable assumption you’d think, but conspicuously absent from any mention in the Knowledge Base.

We’d like to run tests of the repaired RAND() function but we’ve yet to receive a copy of the patch ourselves.

If it sounds like we’re disappointed and disgusted with Microsoft, then you’d be absolutely right.  While we know there are well meaning and devoted programmers working for the company, the outward attitude is still one of contempt for the paying customer.  We’ve seen the attitude many times before, and in recent years heard from senior Microsoft execs saying that the company wants to improve its credibility with customers.  But those are hollow words, since, when faced with a bug the response is as poor as it has been in this case.

We hate to drag out this bad news about RAND() but Microsoft’s behavior forces us to come back to the problem again.  Once we have the patch, we’ll be able to let you know how effective it is and the results of independent tests of the randomness of the fixed function.

OFFICE 2003 TALK

We love hearing from readers, especially about new software.  Feel free to write to us about your experiences, questions, gripes and loves with Office 2003..

Send mail to office2003-talk@woodyswatch.com  - all names and personal details are kept confidential.

WOODY's OFFICE 2003
Copyright (c) 2004 Peter Deegan. All rights reserved. ISSN: 1448-2738
Advertising:  Advertising is available at reasonable rates ask Jan, our Advertising Manager for detail.