HKCareers Free Resource

❏ Explicitly: Below is an example of variable declared Explicitly. You can use "Dim"
keyword in syntax
❏ Dim Num As Integer
❏ Dim password As String

Below is the Syntax:-

Dim <<variable_name>> As <<variable_type>>

Data Types
Since the computer cannot differentiate between the numbers (1,2,3..) and strings (a,b,c,..),
Data Types are used to make this differentiation. There are many VBA data types, which can
be segregated into two main categories:
❖ Numeric Data Types
❖ Non-numeric Data Types

Numeric Data Types

This table displays the numeric data types and the allowed range of values.

Type Range of Values

Byte 0 to 255
Integer -32,768 to 32,767
Long -2,147,483,648 to 2,147,483,648
-3.402823E+38 to -1.401298E-45 for negative values
Single 1.401298E-45 to 3.402823E+38 for positive values

-1.79769313486232e+308 to -4.94065645841247E-324 for negative values

Double 4.94065645841247E-324 to 1.79769313486232e+308 for positive values
Currency -922,337,203,685,477.5808 to 922,337,203,685,477.5807
+/-79,228,162,514,264,337,593,543,950,335. if no decimal is used
Decimal
+/- 7.9228162514264337593543950335 (28 decimal places)

Non-Numeric Data Types

The table below represents the non-numeric data types and the allowed range of values.

Type Range of Values

String (fixed length) 1 to 65,400 characters

String (variable length) 0 to 2 billion characters

Date January 1, 100 to December 31, 9999

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

Boolean True or False

Object Any embedded object

Variant (numeric) Any value as large as double

Variant (text) Same as variable-length string

In VBA, if the data type is not specified, it will automatically declare the variable as a
Variant.

Example

Let us create a button and name it as 'Variables_demo' to demonstrate the use of variables.

*Source: Google

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

Private Sub Variables_demo_Click()

Dim password As String

password = "Admin#1"

Dim num As Integer

num = 1234

Dim BirthDay As Date

BirthDay = 30 / 10 / 2020

MsgBox "Password is" & password & Chr(10) & "Value of num is" & num &
Chr(10) & "Value of Birthday is" & BirthDay

End Sub

Output
Once the script is executed, the following output shall be displayed:-

Password is Admin#1
Value of num is 1234
Value of Birthday is 12:02:08 AM

VBA─ Constants
Data whose values do not change within a certain scope should be declared as constants by
using the Const modifier.
The value of a constant is specified when it is declared (this process is called initialization).
Any attempts to alter the value of a constant then results in a compilation error.
Using constants instead of hard-code literal values is an excellent programming practice. This
makes your code more readable and easier to be modified later on if needed.

The rules for naming a constant are same as those for creating variables. The constant must
have a valid symbolic name and an expression composed of numeric or string constants and
operators (but no function calls).

➢ Only a letter must be used as the first character

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

➢ No space, period (.), exclamation mark (!), or the characters @, &, $, # can be used in
the name
➢ The name cannot 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. An error is thrown, if we try to
change the value of the constant.

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

<<constant_value>>
Example
Here is how you can create a button "Constant_demo" to understand how to work with
constants:

Private Sub Constant_demo_Click()

Const MyInteger As Integer = 42

Const myDate As Date = #2/2/2020#

Const myDay As String = "Sunday"

MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is " &
myDate & Chr(10) & "myDay is " & myDay

End Sub

Output

This is what you will see upon execution of the script:

*Source: Google

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

VBA ─ Operators
The built-in VBA operators consist of mathematical operators, string operators, comparison
operators and logical operators. The different types of Operators are discussed individually
below. VBA supports following types of operators:

❖ Arithmetic Operators
❖ Comparison Operators
❖ Logical (or Relational) Operators
❖ Concatenation Operators

The Arithmetic Operators

In the expression: 9 minus 5 is equal to 4; 9 and 5 are called operands and - is called operator.

Following arithmetic operators are supported by VBA:

Operato
r Description
+ Adds the two operands

- Subtracts the second operand from the first

* Multiplies both the operands

/ Divides the numerator by the denominator

% Modulus operator and the remainder after an integer division

^ Exponentiation operator

Arithmetic Operators ─ Example

You can add a button and try the example below to understand all the arithmetic operators
available in VBA.

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

Private Sub Constant_demo_Click()

Dim a As Integer
a=5

Dim b As Integer
b = 10

Dim c As Double

c=a+b
MsgBox ("Addition Result is " & c)

c=a-b
MsgBox ("Subtraction Result is " & c)

c=a*b
MsgBox ("Multiplication Result is " & c)

c=b/a
MsgBox ("Division Result is " & c)

c = b Mod a
MsgBox ("Modulus Result is " & c)

c=b^a
MsgBox ("Exponentiation Result is " & c)

End Sub

Once executed, the above script will display the following results

➔ Addition Result is 15
➔ Subtraction Result is -5
➔ Multiplication Result is 50
➔ Division Result is 2
➔ Modulus Result is 0
➔ Exponentiation Result is 100000

The Comparison Operators

Comparison operators compare two numbers or strings and return a logical (True or False)
result. The main Excel VBA comparison operators are listed in the table below:

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

Assume variable A holds 10 and variable B holds 20, then -

Operato Exampl
r Description e
Checks if the value of the two operands are equal or not. If yes, then the
condition ( A == B)
==
is true. False.

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

Comparison Operators ─ Example

These examples discussed here will be helpful towards developing your understanding of
comparison operators:-

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

Private Sub Constant_demo_Click()

Dim a: a = 10
Dim b: b = 20
Dim c

If a = b Then
MsgBox ("Operator Line 1 : True")
Else
MsgBox ("Operator Line 1 : False")
End If

If a<>b Then
MsgBox ("Operator Line 2 : True")
Else
MsgBox ("Operator Line 2 : False")
End If

If a>b Then
MsgBox ("Operator Line 3 : True")
Else
MsgBox ("Operator Line 3 : False")
End If

If a<b Then
MsgBox ("Operator Line 4 : True")
Else
MsgBox ("Operator Line 4 : False")
End If

If a>=b Then
MsgBox ("Operator Line 5 : True")
Else
MsgBox ("Operator Line 5 : False")
End If

If a<=b Then
MsgBox ("Operator Line 6 : True")
Else
MsgBox ("Operator Line 6 : False")

End If

End Sub

Once executed, this script will generate the following results:-

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

Operator Line 1 : False

Operator Line 2 : True

Operator Line 3 : False

Operator Line 4 : True

Operator Line 5 : False

Operator Line 6 : True

The Logical Operators

Logical operators compare Boolean expressions and return Boolean results. Hence these also
return a logical (True or False) result. The main Excel VBA logical operators are listed in the
table below:

Assume variable A holds 10 and variable B holds 0, then:-

Operator Description Example

AND If both conditions evaluate to True then the a<>0 AND b<>0 is False
expression is True

OR Performs logical disjunction or inclusion a<>0 OR b<>0 is True

on two Boolean expressions. If either
expression evaluates to True, or both
evaluate to True, then Or returns True. If
neither expression evaluates to True, Or
returns False.

NOT Performs logical negation on a Boolean NOT(a<>0 OR b<>0) is False

expression. It yields the logical opposite of
its operand. If the expression evaluates to
True, then Not returns False; if the
expression evaluates to False, then Not
returns True

XOR Performs logical exclusion on two Boolean (a<>0 XOR b<>0) is False
expressions. If exactly one expression
evaluates to True, but not both, Xor returns
True. If both expressions evaluate to True
or both evaluate to False, Xor returns
False.

Logical Operators ─ Example

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching
 HKCareers Free Resource

You can try some of the Logical operators available in VBA by creating a button and adding
the following function.

Private Sub Constant_demo_Click()

Dim a As Integer
a = 10
Dim b As Integer
b=0

If a <> 0 And b <> 0 Then

MsgBox ("AND Operator Result is : True")
Else
MsgBox ("AND Operator Result is : False")
End If

If a <> 0 Or b <> 0 Then

MsgBox ("OR Operator Result is : True")
Else
MsgBox ("OR Operator Result is : False")
End If

If Not (a <> 0 Or b <> 0) Then

MsgBox ("NOT Operator Result is : True")
Else
MsgBox ("NOT Operator Result is : False")
End If

If (a <> 0 Xor b <> 0) Then

MsgBox ("XOR Operator Result is : True")
Else
MsgBox ("XOR Operator Result is : False")
End If

End Sub

We help university students to get into Investment Banks, Banks, Property/Conglomerate and Big4s.
Prepared by HKCareers | IG: hkcareers | Facebook: hkcareers | Website: hkcareers.hk/coaching