Loops and formulas

[Grantx]

I have a question after reading the "Loop to populate formula" question. I posted separately to avoid hijacking the thread.

I have four tables (essentially distribution tables) in a sheet, all have six columns, three extend to almost a thousand rows.

The cells contain formulae to calculate values from data fed into the sheet throughout the day. Formulas consists of one array, with the remainder being =loookup, and basic arithmetic.

The size of the tables undermines performance - recalcualting on every change up to a few times a second.

Would setting up these tables via VBA improve performance? From my extremely limited knowledge I can't see it - the random nature of the values precludes fixed (?) values resulting from VBA initiated code/formulae.

We could use code to determine a number of events at certain value levels but this may be constantly changing throughout the day, therefore any value entered into a table would be almost obsolete. An alternative may be to refresh the cells at fixed time intervals, but again, this would seem to be somewhat burdening - wouldn't the procedure be continually in a "stop/start" mode?.

Comments welcome.

Grant.

[mikerickson]

VBA routines are almost always slower than spreadsheet functions. Excel is closer to Machine Language than VB and therefore faster.

Have you considered setting Calculation to "Automatic except for tables"?

[Grantx]

Mike,

Please read attachment. Keep thinking I can copy and paste from Word.

[mikerickson]

I’m not sure how to interpret the speed differences. The tables referred to above take data from eight columns by up to 30,000 lines per day. The initial data (simples numbers) is fed into Excel via VBA.. It may be possible to populate the columns with formula to update on a change event (?) but I reckon you’ll run out of resources first. As I said, the tables with formulae slow the sheet down; more formulae will only worsen the situation, surely?

"Automatic except for tables" won’t help as I need the values from the tables. However, it should be possible to set calculation at specific intervals, eg every minute.?

I was answering your question of whether things could be speeded up by substituting VB for formulas. (I doubt it.) Have you tried "Automatic except for tables"? I believe that it stops formulas in the tables, but doesn't effect formulas that take values from the tables.

Or perhaps I'm mistinerpreting what you meant by "Would setting up these tables via VBA improve performance?"

An OnTime routine that enabled/disabled calculation could be written, but Calculation=Manual sounds like a more certain technique. (With calculation=maunal you would know if what you are viewing was calculated with the most recent data. With "pulsing" calculation, you'd have to look at your watch to see if the spreadsheet was recently calculated.)

[Grantx]

My very limited understanding was performing calculations in VBA and returning the result to the sheet was more efficient than cell calculation. Of course, I stand to be corrected.

Interestingly, as per your suggestion, manual calculation is good; I just press F9 and the tables should update. I'll also look at the "Automatic except for tables".

Thanks for the suggestions.

Grant.