Constants and Operators in VBA

Constant is a named memory location just like we make variables and is used to hold a value that CANNOT be changed during the script execution. If a user tries to change a Constant value, the script execution ends up with an error. Constants are declared the same way the variables are declared.

Following are the rules for naming a constant.

• You must use a letter as the first character.

• You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.

• Name can't exceed 255 characters in length.

• You cannot use Visual Basic reserved keywords as variable name.

Syntax

In VBA, we need to assign a value to the declared Constants. We get an error if we try to change the value of constant because constants cannot be changed throughout the program if assigned once.

Const <<constant_name>> As <<constant_type>> = <<constant_value>>

To declare a constant we need to use the Keyword Const and then we should give the name <<constant_name>> of the variable that will hold the constant value.

After that we will give the data type <<constant_type>> to the Constant variable like int, float etc. and then we will assign a particular value <<constant_value>>  to the Constant Variable. 

Example

Code to demonstrate how to work with constants.

Private Sub demo_prog() 
   Const Mynumber As Integer = 7895  
   Const Today As String = "Sunday" 
   
   MsgBox "Integer is " & Mynumber & Chr(10) & "Today is " & Today  
End Sub

Output

Operators in VBA

An Operator can be defined using a simple expression - 5 + 9 is equal to 4. Here, 5 and 9 are called operands and + is called operator. 

VBA supports following types of operators −

• Arithmetic Operators

• Comparison Operators

• Logical (or Relational) Operators

• Concatenation Operators

The Arithmetic Operators

Below are the arithmetic operators that are supported by VBA.

Assume variable A holds 8 and variable B holds 4, then −

Operator	Description	Example
+	Adds the two operands	A + B will give 12
-	Subtracts the second operand from the first	A - B will give 4
*	Multiplies both the operands	A * B will give 32
/	Divides the numerator by the denominator	A / B will give 2
%	Modulus operator and the remainder after an integer division	A % B will give 0
^	Exponentiation operator	A ^ B will give 4096

 

The Comparison Operators

Below are the comparison operators that are supported by VBA.

Assume variable A holds 12 and variable B holds 25, then −

 

Operator	Description	Example
=	Checks if the value of the two operands are equal or not. If yes, then the condition is true.	(A = B) is False.
<>	Checks if the value of the two operands are equal or not. If the values are not equal, then the condition is true.	(A <> B) is True.
>	Checks if the value of the left operand is greater than the value of the right operand. If yes, then the condition is true.	(A > B) is False.
<	Checks if the value of the left operand is less than the value of the right operand. If yes, then the condition is true.	(A < B) is True.
>=	Checks if the value of the left operand is greater than or equal to the value of the right operand. If yes, then the condition is true.	(A >= B) is False.
<=	Checks if the value of the left operand is less than or equal to the value of the right operand. If yes, then the condition is true.	(A <= B) is True.

The Logical Operators

Below are the logical operators that are supported by VBA.

Assume variable A holds 10 and variable B holds 0, then –

 

Operator	Description	Example
AND	Called Logical AND operator. If both the conditions are True, then the Expression is true.	a<>0 AND b<>0 is False.
OR	Called Logical OR Operator. If any of the two conditions are True, then the condition is true.	a<>0 OR b<>0 is true.
NOT	Called Logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true, then Logical NOT operator will make false.	NOT(a<>0 OR b<>0) is false.
XOR	Called Logical Exclusion. It is the combination of NOT and OR Operator. If one, and only one, of the expressions evaluates to be True, the result is True.	(a<>0 XOR b<>0) is true.

 

The Concatenation Operators

Below are the Concatenation operators that are supported by VBA.

Assume variable A holds 6 and variable B holds 10 then −

Operator	Description	Example
+	Adds two Values as Variable. Values are Numeric	A + B will give 16
&	Concatenates two Values	A & B will give 610

 

Assume variable A = "Microsoft Excel" and variable B = "VBA", then –

 

Operator	Description	Example
+	Concatenates two Values	A + B will give Microsoft ExcelVBA
&	Concatenates two Values	A & B will give Microsoft ExcelVBA

 

Note − Concatenation Operators can be used for both numbers and strings. The output depends on the context, if the variables hold numeric value or string value.