Constants

If you need to use a value that never changes, it will be wise to use a constant instead of a variable. When you use the constant you can be sure that the value is always the same and you won’t accidentally change it to a different one. If you try, Excel will return an error.

You can declare constants by using the Const word at the beginning.

Const monthsInYear As Integer = 12
Const PI As Double = 3.141593

1 2	Const monthsInYear As Integer = 12 Const PI As Double = 3.141593

Excel VBA Constants

Besides user-defined constants, Excel offers many predefined ones. You can meet them when you use the macro recorder.

Look at the following code generated by the macro recorder. It fills a cell with yellow color.

Sub fillCell()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Sub fillCell()

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End Sub

Insert MsgBox(xlAutomatic) before End With.

If you execute the macro it will display the message showing the value assigned to constant xlAutomatic.

If you write .PatternColorIndex = -4105 instead of xlAutomatic it will give you the same result.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 520