Excel formula to check cell contents
I'm trying to create some conditional formatting at runtime (huzzah) for an add-in (double huzzah), and have found that, apparently, some functions cannot be used as they would in a normal worksheet. (I just get an invalid procedure call error 5 when trying to create the CF referencing a VBA function I could call in a cell, even though it's in the add-in and not the workbook; I can create the CF fine with a built-in function.) The clearest confirmation I've found for this is here, but it doesn't really explain what the problem is; that's the esoteric part, would love to hear more about what I can expect with this.
The rubber-meets-road part is: can I avoid VBA altogether, and use a series of Excel-only, built-in functions to verify whether a given cell contains a constant (i.e. a value entered by a user), a formula (i.e. some kind of calculation, logical operation, etc.--pretty much starts with an =), or a link (i.e. a reference to a cell in another worksheet or another workbook)? I know Excel has this determination at its fingertips; witness the uses and speed of GoTo/Special. How can I get at it though?
Thanks in advance for your help.
Answer
For Office versions 2013 and higher, the ISFORMULA¹ function is available. Combining this with the NOT function, AND function and either the COUNTBLANK, ISBLANK or LEN function can produce a formula to determine whether a cell contains a constant.
The standard formulas in E2:F2 are,
=ISFORMULA(D2)
=AND(NOT(ISFORMULA(D2)), LEN(D2))
If further information on the nature of the cell value is required the TYPE function can be used to determine if the cell contents are a number, text, boolean, error or array.
When used in concert the native worksheet functions discussed here can reproduce the results available from VBA's Range.SpecialCells method and its xlCellTypeConstants or xlCellTypeFormulas xlCellType enumeration.
¹ The ISFORMULA function was introduced with Excel 2013. It is not available in earlier versions.