Excel formula to check cell contents

Question

Hi everyone,

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

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 2

Try write some function in VBA and use it from worksheet.

But, you will need Application.Volatile in this function.