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 |
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 |