Johnny Meredith wrote:
Hi all,

I've written an Excel add-in that allows users to input a list of
formulas that they want "frozen" (i.e. - converted to the formula's
value). For example:

Listbox control on userform contains the following values:
HPVAL
HPEA

userform has Add and Remove buttons to modify this list. userform also
has buttons to freeze the results of these formulas on the current
sheet or throughout the entire workbook.

When the user clicks to freeze button, a routine in the addin looks
through all cells to determine if the formulas in the list are
contained in the cell's formula. If so, the formula is replaced with
its value.

I currently determine if the formula exists in the cell by looking for
the string "=HPVAL(" in the case of the first formula listed above.
So, if the user has typed in "+HPVAL(", the formula is not forzen. Or,
if for some reason the user has a formula something like this:
=IF(A1="My string: =HPVAL(",1,0), then that formula would be frozen,
even though clearly HPVAL used in this context is not a formula, but a
constant string. Another issue is, if the user has the following
formula: =IF(HPVAL(<<stuff here)=1,1,0), then the ENTIRE formula is
converted to its value, and not just the HPVAL part.

All of these problems stem from the fact that I'm looking for "strings"
that represent the formula instead of looking for the formula itself.
Is there a way to examine a cell, determine if a given formula is
utilized in the cell, and freeze its value? Has anyone out there
written an add-in or VBA routine that does the same thing? Your
thoughts are greatly appreciated.

Thanks,
Johnny

It sounds like you have to do some parsing. You could first of all
write a function which checks to see if a function name occurs within
an embedded string. If not - it would be relatively easy to find
matching left and right parenthesis (but watch out for embedded
strings!) which goes with a function and thus identify the
subexpression.

*but*

How do you know what value to replace that subexpression by? The only
thing I can think of is to write it into a cell and then calculate the
spreadsheet - but some of your formulas are already frozen and some
not, so the result might be unpredictable and it might have the side
effect of changing the values of certain cells targeted for being
frozen. The problem becomes acute if RAND() is involved. I don't think
that there really is any 100% reliable way to get the current value of
a subexpression. You need to think about the semantics of what you want
a bit more.

One thought: maybe your user form could have an option to either only
freeze formulas whose "top level" is say HPVAL or to freeze all
formulas in which HPVAL occurs (as a function - not a string). You
would still have to handle the problem of embedded strings (which
shouldn't be too hard if you remember to watch out for escaped quote
marks) but would avoid the need for serious parsing.

Hope that helps

-John Coleman