Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is a cell a formula or value?
I'm stumped, I thought there used to be a =isformula to test whether a cell
is a formula or not. How can I test for that to see if a formula got overwritten which I do want it to do if a user chooses. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is a cell a formula or value?
You can use a VBA UDF (user defined function):
Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function Then you'd use it just like any other worksheet function: =IsFormula(A1) This will return either TRUE or FALSE. You could also use this function to conditionally format the cells. To use this: Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the project explorer pane on the left. Right click the file name Select: InsertModule Copy the code above and paste into the window on the right Return back to Excel: ALT Q Set the conditional formatting... Select the cell(s) in question. Assume this is cell A1. Goto FormatConditional Formatting Formula Is: =IsFormula(A1) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "todd" wrote in message ... I'm stumped, I thought there used to be a =isformula to test whether a cell is a formula or not. How can I test for that to see if a formula got overwritten which I do want it to do if a user chooses. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is a cell a formula or value?
"T. Valko" wrote...
You can use a VBA UDF (user defined function): Function IsFormula(cell_ref As Range) * * IsFormula = cell_ref.HasFormula End Function .... This will return either TRUE or FALSE. .... Actually it could also return #VALUE! if you try to pass it anything that isn't a range reference. At the risk of slight overengineering, you could use Function isformula(c As Variant) If Not TypeOf c Is Range Then isformula = CVErr(xlErrRef) Else isformula = c.HasFormula End If End Function which would return #REF! when the argument isn't a range reference. If this udf would be used as just one term among many in longer formulas, this could provide more meaningful diagnostics. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |