TABLE - Array formula slowing down save process

[Rho77]

Hi everyone

I have had a table array formula set up for many years in my main spreadsheet. I also always have my xls set up with "automatic except tables", so the tables only calculate when I want them to, or when I save.

A few weeks ago I added several (60-ish) columns to a different tab and all of a sudden my PC started to run very slowly. (8 minute save versus 20 seconds). I removed the columns (scratching my head) but then noticed that it was not the "processor" or the calculation that was taking the time but the "TABLES" (I have three TABLES and they now take almost 3 minutes each). I have tested this on a colleagues super-powerful PC and the TABLES take the same time.

I removed the sheet with the TABLES and it saved in seconds. (as expected)
I pasted of the whole sheet with values only and got the same result. (as expected)

I need the array formula so tried to put it back in, with of course the usual Ctrl+shift+Entr, but I now get "That function is not valid". It was last week, so why not today?

The array formula I am using is rather simple: '=TABLE(,B33)' (plus C+S+E to give the {} of course!)
The Columns I added (not that I am sure this is the trigger) are not directly related to what the TABLEs are looking at, so I think this was a red-herring...

I'm on the verge of going demented!

Any ideas? I have had this xlsm (new version saved every day just about) for years and have travelled through from 2003, 2007 and now on 2010.

Thanks. X

 

[iliace]

To the best of my knowledge: if the formula is =TABLE(,B33) that means it was generated using the Data Table feature (under Data->What-if Analysis), and not by using a C+S+E formula.

 

[Rho77]

Thanks iliace and Happy New Year!

I've now got the formula back, just as you said below and it is functioning perfectly, but I am back to an 8 minute save (or a compete crash) . Now when it saves it tells me not compatible with xlsm format. Am I correct in thinking I need to live in a world of Macros or arrays, but not both?

To the best of my knowledge: if the formula is =TABLE(,B33) that means it was generated using the Data Table feature (under Data->What-if Analysis), and not by using a C+S+E formula.

I'm begining to think that I need to delete the arrays altogether and just write a few macros instead... Ideas?

 

[iliace]

It's difficult to say without looking at the actual spreadsheet, but here are some general thoughts.

1 - xlsm should not have a conflict saving both arrays and macros, in fact it should be able to save everything unless you are using some very obsolete feature.

2 - deciding which is faster (array or macro/UDF) a lot of time depends on exactly what's being calculated. Many times, using built-in functions is quicker simply because they are fully compiled and execute faster than VBA ever will. However, there might be workarounds and efficiencies introduced through VBA code that makes a macro faster than a formula.

3 - here is an in-depth article that details how you can speed up certain calculations: Excel 2010 Performance: Improving Calculation Performance. The authors' web site also has a number of valuable resources to optimize your spreadsheets: Advanced Microsoft Excel Consultancy and Solution Development -Decision Models


4 - you can test what is causing your bottlenecks by using a calculation timer. Here is some code that I use, for example:

(note - this assumes 32-bit Windows)

Declare Function GetTickCount Lib "kernel32.dll" () As Long


Public Function recalcTimer(rngRecalcRange As Excel.Range, _
                              reps As Long) As Long
  'returns recalc time of rngRecalcRange
  'over reps number of calculations
  
  Dim calcTime As Long
  Dim rep As Long
  Dim calcs As Excel.XlCalculation
  
  calcs = Application.Calculation
  Application.ScreenUpdating = False
  Application.Calculation = Excel.XlCalculation.xlCalculationManual
  
  calcTime = GetTickCount()
  For rep = 1 To reps
    rngRecalcRange.Calculate
  Next rep
  
  calcTime = GetTickCount() - calcTime
  recalcTimer = calcTime ' / reps
  
  Application.Calculation = calcs
  Application.ScreenUpdating = True
End Function


Public Sub doRecalc()
  Dim i As Variant
  
  i = Application.InputBox("Number of reps?", "Recalc Timer", 100, , , , , 1)
  
  If i <> False Then
    Call MsgBox(i & " calculations of selected range took " & _
      recalcTimer(Selection, CLng(i)) & " milliseconds.", _
      vbOKOnly, "Recalc Timer Result")
  End If
End Sub

Select a range, and run the doRecalc procedure. Enter number of calculations to perform - for quick formulas, enter a larger number (such as 10,000), for slower ones use a small number. This way, you can go through and calculate just portions of your worksheet, and it will tell you which ones are fast and which are slower. Hopefully that will help.

Or, post back with some specific examples of what kind of models your TABLE functions are working with, and we can talk about specific optimization.

 

[Rho77]

Thanks iliace

I have only just got around to testing this and it has worked a treat!

Having a focussed tidy-up thanks to this! I appreciate your help!

Rho

 

[iliace]

Cool - glad it worked out!