My spreadsheet is large with many fields calculated with VBA functions. Until my computer was forced to restart due to an MS update, the spreadsheet was working perfectly.

My pc is fast, an AMD Athlon II quad core with 4 GB memory. The spreadsheet has been running in 2 environments [dual boot machine]: XP64 SP2 {there isnít an SP3 for XP64} with Office 2007 and Win 7 with Excel 2010. All of the data is hard coded [i.e. there are no file reads either locally or from the internet].

When the machine was rebooted, most of the fields were filled with #VALUE!. One of the fields was simply the product of 2 numbers [in the form =A*B]. Double clicking on the field shows the 2 numbers are defined without a #VALUE! In either. Occasionally, after the double click it would calculate correctly and slowly the rest of the row would calculate correctly.

Next, often A or B was zero so a function replaced the product: [essentially, if A or B = 0 return zero otherwise return the product] This allowed examination with the function key. It showed the inputs and the correct product. The value would display in the cell slowly. Generally, the rest of the row would subsequently calculate.

I have several questions:

  1. Whatís going on?
  2. Is there a solution within Excel?
  3. Is VBA too slow, would another language be better?
  4. Would the issues go away if this was rebuilt in SQL or some other environment?