CODEWITHSUNDEEP
vbaexcel
Serge Kashlik
Serge Kashlik

Reputation: 413

Understanding the syntax of "special cells" in Excel VBA

I have a part of my code that looks like this:

With Selection
    Set ConstantCell = .SpecialCells(xlCellTypeConstants, xlNumber)
    Set FormulaCell = .SpecialCells(xltypeformulas, 21)
End With

It is part of a macro to color cells with different types of data in them. I understand what this part does but what I want to ask is what does the 21 represents? I found that in the syntax of this formula it represents some value and I thought that it would only select the cells which have formulas and equal to 21 but that was not the case when I ran the whole macro.

Upvotes: 3

Views: 8393

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

Looks like:

Set FormulaCell = .SpecialCells(xlCellTypeFormulas, 21)

is the same as:

Set FormulaCell = .SpecialCells(xlCellTypeFormulas, xlErrors + xlLogical + xlNumbers)

because:

If Type is either xlCellTypeConstants or xlCellTypeFormulas , this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type.

and:

XlSpecialCellsValue constants

xlErrors     +16

xlLogical     +4 

xlTextValues  +2

xlNumbers     +1

at:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel

(also, xlCellTypeFormulas is misspelled in your example.)

Upvotes: 5

Related Questions