Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
What would you want to do if the user has:
=IF(<somecondition,HPVAL(<stuff),<other stuff) and <somecondition is "false"? Should the value be frozen or not ? Tim. "Johnny Meredith" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
Thanks for the reply. I really hadn't considered the RAND() issue, but
generally speaking, the formulas that are being frozen are wrappers for queries into an accounting system. The creators of these sheets often times have to send their results to people who do not have access to the accounting system, so the values of the subexpressions in this application are readily determinable. I'm trying to avoid parsing strings altogether. That's what I'm doing now. The problems with this methodology just seems to keep snowballing. I want to know if there's a way to use Excels built-in formula evaluations algoritims to solve this problem. I'm quite certain that Excel does not parse strings to identify formulas in a cell, but then again, I could be wrong. I'm probably way out in left field here, but you never know! Thanks for the quick response and excellent observations, Johnny |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
Yes it should be forzen. It's a wrapper for queries in an accounting
system, and the ultimate recipient of the sheet (who does not have the addin that makes the account system query wrappers possible) will get a #NAME? error. See also my new post below in response to John Coleman. Thanks, Johnny |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
Johnny Meredith wrote: Thanks for the reply. I really hadn't considered the RAND() issue, but generally speaking, the formulas that are being frozen are wrappers for queries into an accounting system. The creators of these sheets often times have to send their results to people who do not have access to the accounting system, so the values of the subexpressions in this application are readily determinable. I'm trying to avoid parsing strings altogether. That's what I'm doing now. The problems with this methodology just seems to keep snowballing. I want to know if there's a way to use Excels built-in formula evaluations algoritims to solve this problem. I'm quite certain that Excel does not parse strings to identify formulas in a cell, but then again, I could be wrong. I'm probably way out in left field here, but you never know! Thanks for the quick response and excellent observations, Johnny It doesn't sound quite so bad. Excel undoubtably parses formulas to some sort of tree representation, but the later is not exposed by VBA. If the expressions you want to replace have the property that you can use VBA to determine their value then it might be possible to use regular expressions to pick them out and do the substitution. Are you familar with VBScript's RegExp object? It can be used from Excel by setting an appropriate reference. I'm off to the parents now for a Christmas dinner. Good luck with your problem. -John Coleman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
Johnny,
I think you're stuck with parsing the formula. RegExp may help, as suggested by John C. On the other hand, can you just copypastevalues the whole sheet? If you freeze only some of the formulas then how does the user know which values are "live" and which are not (so they know which inputs they can change and still get correct values)? Tim "Johnny Meredith" wrote in message oups.com... Yes it should be forzen. It's a wrapper for queries in an accounting system, and the ultimate recipient of the sheet (who does not have the addin that makes the account system query wrappers possible) will get a #NAME? error. See also my new post below in response to John Coleman. Thanks, Johnny |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Specific Formula Programmatically
I am not familiar with the RegExp object. I will check it out and
report back. Thanks, Johnny PS - Don't eat too much! I just did, and I'm paying for it now! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically add a Formula in a worksheet? | Excel Worksheet Functions | |||
Formula in only one of many cells not updated when row programmatically inserted above | Excel Programming | |||
Changing formula programmatically | Excel Programming | |||
Programmatically increase the row # in a formula | Excel Programming | |||
Formula produces wrong result when data cells filled programmatically | Excel Programming |