EXCEL VBA non-volatile INDIRECT

[aareding]

I have a spreadsheet that I have been working on that involves usin
INDIRECT to get the address of the last cell in order to calculat
database formulas. It was originally hardcoded and took about 1 1/
minutes to run. But I kept having to change the formulas. When
changed to indirect so that I wouldn't have to keep changing formulas
it now takes about 5 1/2 minutes to run. I know that everyone says no
to use INDIRECT much, but I don't know another way. Has anyone writte
a non-volatile INDIRECT? Or does anyone have another suggestion?
I would appreciate any help!

 

[Charles Williams]

If you are using INDIRECT or OFFSET to dynamically calculate the address of
the last cell then it has to be volatile for it it to function correctly.

(I always use OFFSET but its mostly a question of taste).

You can save some calculating time by using a helper cell to count the
number of rows once, and then referring to that cell in your dynamic range,
rather than counting the number of rows in every dynamic range.

You may also want to look at my website pages on speeding up calculations.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[aareding]

For my purposes, INDIRECT doesn't need to be volatile. I enter all o
my data at the start of the day and it needs to calculate once and the
be used in all of my database formulas