help with worksheet refactoring

[David]

Hi

I am looking at excel worksheets with a view to making
them less verbose.

For instance there are lots of formulae of that repeat
variables
eg
= Sum(A6*U6, B6*V6...)
then the next cell
= Sum(A6*U7, B6*V7...)

I would like to refactor these into a Function
eg
=MyMethod(U6,V6)

Is there any way to store A6 and B6 as worksheet variables
within a VBA module?

thanks

David

 

[Jan Karel Pieterse]

Hi,

Putting these into a user defined function would have no
real advantages:

- calc speed is slower
- spreadsheet becomes less easy to maintain
- macros need to be enabled

What is the problem with the current formulas?

Note that to ease copying you should:

= Sum($A$6*U6, $B$6*V6...)

So copying down gives

= Sum($A$6*U7, $B$6*V7...)

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[david]

Hi

Thanks for your reply.

The problem with the current formulae is that they repeat
themselves across a large number of cells, and I would
like the logic in one place, so that if the formula
changes I just make one change in one place, not 40 (and
there are 20 different formulae). The formulae are also
reasonably complex - therefore difficult to read and edit
in a single line.

After looking over what VBA info I could find I thought
the best idea was to create Functions so that the formula
internals were not exposed to users and all that was
required was a list of arguments.

Given that some inputs were constants from elsewhere in
the worksheet I had hoped to store them as variables and
access them in my functions, rather than pass them in as
arguments.

I will suggest your idea to my customer however - many
thanks for your time.

david

 

[Jan Karel Pieterse]

Hi david,

The problem with the current formulae is that they repeat
themselves across a large number of cells, and I would
like the logic in one place, so that if the formula
changes I just make one change in one place, not 40 (and
there are 20 different formulae). The formulae are also
reasonably complex - therefore difficult to read and edit
in a single line.

Another thing you could do is use defined names that contain the
formula.

- highlight the FIRST cell of a range with a formula (e.g. =
Sum($A$6*U6, $B$6*V6...)
- Copy the formula itself (highlight it in the formula bar and press
control-c)
- Choose Insert, name, define
Name: FirstFormula (or a more descriptive one)
Refersto: = Sum($A$6*U6, $B$6*V6...) (control-v that into the refersto
box)
- OK your way out.
- now in the cell, simply write this formula:
=FirstFormula

Download my name manager to ease editing the names. Find it at
www.bmsltd.co.uk/mvp or at www.decisionmodels.com

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[david]

Hi

Excellent, thanks for your help

Stephen

-----Original Message-----
Hi david,


Another thing you could do is use defined names that contain the
formula.

- highlight the FIRST cell of a range with a formula (e.g. =
Sum($A$6*U6, $B$6*V6...)
- Copy the formula itself (highlight it in the formula bar and press
control-c)
- Choose Insert, name, define
Name: FirstFormula (or a more descriptive one)
Refersto: = Sum($A$6*U6, $B$6*V6...) (control-v that into the refersto
box)
- OK your way out.
- now in the cell, simply write this formula:
=FirstFormula

Download my name manager to ease editing the names. Find it at
www.bmsltd.co.uk/mvp or at www.decisionmodels.com

Regards,

Jan Karel Pieterse
Excel TA/MVP
.