C
Clif McIrvin
Excel / Office 2003 SP3
XP Pro SP3
I have inherited a fairly static recipie design worksheet. This
worksheet has been essentially unmodified for several years, though it
is used on a regular basis to generate new recipies -- and recently
'broke' when a 'new' design requirement was encountered.
The recipie contains fillers (two), active ingredients (normally one or
two), sweeteners (zero, one or two) and water.
The user fills in the blanks, specifying the ratio between the fillers,
the amount of each active ingredient, the amount of each sweetner, and
the amount of water.
When a specific target amount of one of the fillers exists (rather than
a known percentage) a trial and error iteration is used to determine the
nearest percentage to use.
Some sweeteners are specified in terms of total amount used, some in
terms of percentage of active ingredient -- the historical process has
been to hand calculate the correct amount in these cases.
Water has always been defined in terms of amount, but again, frequently
the desired amount of water is based on a percentage of active
ingredient; again the amount is hand calculated.
When I was given this worksheet, and observed how the designers were
using it I immediately developed some VBA code tied to the
worksheet_changed event to enable the designer to enter either the water
amount or ratio, for example; and added a new field tied to the goalseek
method to automate the manual trial and error iteration related to
calculating the filler quantities.
These new changes to the worksheet worked well, and were well received
by the designers / users ... until last week a recipie needed a third
active ingredient. This was accomodated by moving some cells on the
worksheet to make room for the third active ingredient ... which of
course broke some poorly designed formulas on the worksheet.
All this to get to the point of this post: I'm curious how other
worksheet developers would approach this. My first thought was to change
the SELECT CASE Target.Address statement in my Worksheet_Changed event
to use named ranges instead of absolute addresses --- then I remembered
that when I copy worksheets within a workbook the duplicate named ranges
break both worksheet formulas and VBA code [unless, of course, there's a
technique I've not learned for doing this.]
My next idea is to create a table in an unused region of the worksheet
using the ADDRESS worksheet function so that 1) the VBA code can detect
worksheet layout changes that would break the macro functionality and 2)
formulas used by the VBA code can be placed on the worksheet where they
can be seen (and potentially understood) by non-programmer users. [I
would like to create reasonably 'bullet-proof' code that can adapt to
worksheet layout changes without breaking; or at the least has the
ability to break gracefully enough to provide a non-programming user
with enough information to find a programmer who in turn would have
enough information to 'easily' repair a worksheet / Macro code that
(s)he has never seen before.]
My third idea was to ask the community for opinions / suggestions ... so
here is this post.
I have attempted to both keep this brief and supply sufficient
information ... probably only partially successfully <grin>.
Looking forward to hearing new perspectives and ideas,
XP Pro SP3
I have inherited a fairly static recipie design worksheet. This
worksheet has been essentially unmodified for several years, though it
is used on a regular basis to generate new recipies -- and recently
'broke' when a 'new' design requirement was encountered.
The recipie contains fillers (two), active ingredients (normally one or
two), sweeteners (zero, one or two) and water.
The user fills in the blanks, specifying the ratio between the fillers,
the amount of each active ingredient, the amount of each sweetner, and
the amount of water.
When a specific target amount of one of the fillers exists (rather than
a known percentage) a trial and error iteration is used to determine the
nearest percentage to use.
Some sweeteners are specified in terms of total amount used, some in
terms of percentage of active ingredient -- the historical process has
been to hand calculate the correct amount in these cases.
Water has always been defined in terms of amount, but again, frequently
the desired amount of water is based on a percentage of active
ingredient; again the amount is hand calculated.
When I was given this worksheet, and observed how the designers were
using it I immediately developed some VBA code tied to the
worksheet_changed event to enable the designer to enter either the water
amount or ratio, for example; and added a new field tied to the goalseek
method to automate the manual trial and error iteration related to
calculating the filler quantities.
These new changes to the worksheet worked well, and were well received
by the designers / users ... until last week a recipie needed a third
active ingredient. This was accomodated by moving some cells on the
worksheet to make room for the third active ingredient ... which of
course broke some poorly designed formulas on the worksheet.
All this to get to the point of this post: I'm curious how other
worksheet developers would approach this. My first thought was to change
the SELECT CASE Target.Address statement in my Worksheet_Changed event
to use named ranges instead of absolute addresses --- then I remembered
that when I copy worksheets within a workbook the duplicate named ranges
break both worksheet formulas and VBA code [unless, of course, there's a
technique I've not learned for doing this.]
My next idea is to create a table in an unused region of the worksheet
using the ADDRESS worksheet function so that 1) the VBA code can detect
worksheet layout changes that would break the macro functionality and 2)
formulas used by the VBA code can be placed on the worksheet where they
can be seen (and potentially understood) by non-programmer users. [I
would like to create reasonably 'bullet-proof' code that can adapt to
worksheet layout changes without breaking; or at the least has the
ability to break gracefully enough to provide a non-programming user
with enough information to find a programmer who in turn would have
enough information to 'easily' repair a worksheet / Macro code that
(s)he has never seen before.]
My third idea was to ask the community for opinions / suggestions ... so
here is this post.
I have attempted to both keep this brief and supply sufficient
information ... probably only partially successfully <grin>.
Looking forward to hearing new perspectives and ideas,