Quicker calculations -- storing huge equations or cutting and past

[pdberger]

Good afternoon --

I built a workbook used to calculate medical fees and productivity. It's
based on several reference lookups, and lots of internal calculations. For
each item, there are three calculations, each of which consists of a formula
60-70 keystrokes long. When the user does this work, (s)he may do it for
100-200 items at a time. Right now, it can take 1-2 minutes to calculate the
entire workbook so I leave calculation set to manual.

My question -- I can either have all the equations located inside the cells,
or create a little VB code to copy and paste them into cells on rows where
they'll be used. That is, if cell A15 has the "part number" to be looked up,
it would paste the lookup and calculation formulas into the appropriate cells
on that row, and then do summary calculations when all done.

Does anyone know which would be faster?

Thanks in advance,

 

[T. Valko]

Hard to say without knowing what kind of formulas you're using.

See this for tips on improving efficiency:

http://www.decisionmodels.com/

 

[JLatham]

In general Excel functions perform faster than equivalent VB code, but I
realize you aren't asking whether to continue using formulas, but whether to
have them in place already or have a VB routine put them in place for you.

As Biff said, "it depends". I presume your users are not having to type in
those long formulas; that they are either already in place in unused rows or
they are filling them down the sheet(s) as required?

Your basic problem is that when they do enter data, there's a long delay
before they can enter another entry because the workbook is recalculating
after each data entry?? Is that correct?

If that's the case, I'd probably leave the workbook set to Manual
calculation and put a button on needed/appropriate sheets that would
recalculate the workbook when clicked. I'd probably also put code in the
Workbook_Open() and Workbook_Activate() events to set calculation to manual,
and I'd put code into the Workbook_Deactivate() event to set calculation to
automatic. This would prevent other workbook's from getting set to manual
calculation because they were opened after this one was opened but hadn't
been closed yet.