Question re best practice

[anny]

hello XLers

I have a general question about best practice in Excel . My workbook has
about 40 sheets. On each sheet, there is a very lengthy formula dragged
over a range of about 120 cells. Everything works fine, but it occurs to me
that this long formula is repeated 4800 times, making the file kind of big.

Is it better practice to use code to populate the cells with formulas, say
when a sheet is activated? Should I then depopulate the cell's formulas
when the sheet closes? Is it better to use code to populate the cells with
values calculated in code, rather than write the formulas to the cells? Are
there speed issues?

Any comments are appreciated
anny

 

[Harlan Grove]

anny wrote...

I have a general question about best practice in Excel . My workbook has
about 40 sheets. On each sheet, there is a very lengthy formula dragged
over a range of about 120 cells. Everything works fine, but it occurs to me
that this long formula is repeated 4800 times, making the file kind of big.

Is it better practice to use code to populate the cells with formulas, say
when a sheet is activated? Should I then depopulate the cell's formulas
when the sheet closes? Is it better to use code to populate the cells with
values calculated in code, rather than write the formulas to the cells? Are
there speed issues?

Large file sizes are unavoidable with Excel. If file size is a critical
concern, you shouldn't be using Excel in the first place. If not a big
concern, then leaving the formulas as entered is best because event
handlers are comparatively unreliable for repeatedly entering formulas,
and you defeat the main purpose and benefit of spreadsheets - automatic
recalculation.

What do these formulas look like? It may be possible to shorten them.

 

[Bob Phillips]

Surely, unless you have a very small HDD, the size should not be an issue.
Performance may well be, but you do not mention that. I certainly wouldn't
do what you suggest juts to make the saved file size smaller.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Richard Buttrey]

hello XLers

I have a general question about best practice in Excel . My workbook has
about 40 sheets. On each sheet, there is a very lengthy formula dragged
over a range of about 120 cells. Everything works fine, but it occurs to me
that this long formula is repeated 4800 times, making the file kind of big.

Is it better practice to use code to populate the cells with formulas, say
when a sheet is activated? Should I then depopulate the cell's formulas
when the sheet closes? Is it better to use code to populate the cells with
values calculated in code, rather than write the formulas to the cells? Are
there speed issues?

Any comments are appreciated
anny

I suppose it depends how big the file is and whether you think it's
unneccesarily taking too much space on your local drive. Also whether
having the formulae slows down things like filtering large data
tables.

As a general habit I've tended to keep things like Vlookup and
SumProduct Formulae in a 'master' row at the top of tables, and use
code to populate the data area before converting all the formulae to
numbers. I've done this as a matter of habit ever since Lotus 123 days
back in the early 90s, when it was my experience that Vlookup was
memory hungry, although it's probably much less of a problem these
days.

I do find however when toggling various filters on large databases,
particularly with SumProduct and to a lesser extent Vlookup, if these
are left in place they do seriously affect the speed of updating the
database and SubTotals(9,A:B) style functions, when toggling various
filters.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[CLR]

I dunno about "best", but I have one book with 38 sheets and about 1200
VLOOKUP's on each sheet, and I did as you describe, delete the formulas when
the sheet is deactivated and put them in place with a macro when the sheet
is "updated" (not just opened, as some editing of ths sheet is desirous
without the formulas being activated).....seems to help the recalc time....

Vaya con Dios,
Chuck, CABGx3