Using constants in VBA - VBA For Excel

Sometimes it is convenient to name a data item that is used in a macro as a fixed or constant value. For example, the speed of light has a constant numerical value of 186,000 miles per second. If we were writing a macro that referenced this constant, we could name it in the same way that we name variables. However, it would clearly differ from a variable in that its value remains constant. You can do this by using the syntax shown below. Examples of the way in which constants are declared are also shown below:

Const ConstName As Integer = value
Const Speed_Of_Light As Integer = 186000
Const Min_Age_For_Alcohol As Integer =18
Const Pie As Single = 3.142
Const Pound_Euro_Exchange_Rate As Single = 1.4

There are good reasons for using named constants rather than direct numerical values in VBA programs:

  • M Any changes made to a constant only have to be made once in the program.For example,if a program has the constant Pound_Euro_Exchange_Rate and the actual rate rises to 1.5,then you only need change the 1.4 to 1.5.
  • M The greater readability of the code makes program maintenance easier.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

VBA For Excel Topics