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.
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.