PDA

View Full Version : Loops and formulas



Grantx
11-14-2007, 07:43 PM
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
11-14-2007, 08:00 PM
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
11-14-2007, 08:35 PM
Mike,

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

mikerickson
11-14-2007, 09:02 PM
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
11-14-2007, 09:40 PM
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.