Excel formula to check cell contents

Question

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 1

Updated for Excel 2013:

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

excel-2013

Answer 2

Not sure if this is what you want, but it seems to do what you are asking, at least some of it.

http://www.ozgrid.com/VBA/special-cells.htm

It's the range.specialcells method.

It returns a range that contains only constants, or only formulas, etc.

An example of how this code would be used is shown below:

Sub CheckForConstants()
    Dim x As Range
    Set x = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
    MsgBox "address of cells that contain numbers only is " & x.Address
    Set x = Selection.SpecialCells(xlCellTypeConstants)
    MsgBox "address of cells that contain constant of any type is " & x.Address
End Sub

You select a range and then execute this macro and it will return the address of those cells that meet the requirements.

The first x looks for cells that contains numbers only. The second x looks for cells that contains any constants

The range in this case was selection, but you can set to what you want, i.e. range("a1:b5"), etc.

I went back to the worksheet and used the goto special method.

Apparently it also uses the range.special method.

I used the record macro option and this is what I got.

Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Range("M7").Select
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Range("I6:J16").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Range("L9").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("CP").Select
    Application.CutCopyMode = False
    Range("I21").Select
    ActiveSheet.DrawingObjects.Select
    Application.Goto Reference:="GoToSpecialRoutine"

The goto special feature on the worksheet uses the special cells method for some of what it does.

It also uses others as well. In the last 5 lines of codes I changed worksheet and asked it to go to objects.

It doesn't really go to them. It just selects them.

worksheet CP contained objects and it used the code in the last 3 lines to select all the objects on the worksheet.

Best bet to see the code behind goto special is to record a macro and then use the goto / special feature in the worksheet.

When finished, Stop recording and view the macro you recorded.

I don't know of any other features to select by type of cell, but I'm just a newby so it could be there very easily and not be known by me.

Answer 3

I don't think you can avoid VBA altogether but you can create a simple UDF and use it within Excel

Eg

Function IsFormula(Check_Cell As Range)
  IsFormula = Check_Cell.HasFormula
End Function

and

Function IsLink(Check_Cell As Range)

  If InStr(1, Check_Cell.Formula, "!", vbTextCompare) Then
   IsLink = Check_Cell.HasFormula
  End If
End Function

=IsFormula(A1) will return TRUE if there is a formula in A1 and FALSE otherwise =IsLink(A1) will return TRUE if there is a formula in A1 containing '!' otherwise FALSE

You could combine these and create a string output "Formula","Link","Value"