VBA - Excel Terms

In this chapter, you will acquaint yourself with the commonly used excel VBA terminologies. These
terminologies will be used in further modules, hence understanding each one of these is important.

Modules

Modules is the area where the code is written. This is a new Workbook, hence there aren't any
Modules.

To insert a Module, navigate to Insert → Module. Once a module is inserted 'module1' is created.

Within the modules, we can write VBA code and the code is written within a Procedure. A
Procedure/Sub Procedure is a series of VBA statements instructing what to do.

Procedure

Procedures are a group of statements executed as a whole, which instructs Excel how to perform a
specific task. The task performed can be a very simple or a very complicated task. However, it is a
good practice to break down complicated procedures into smaller ones.
The two main types of Procedures are Sub and Function.

Function

A function is a group of reusable code, which can be called anywhere in your program. This
eliminates the need of writing the same code over and over again. This helps the programmers to
divide a big program into a number of small and manageable functions.

Apart from inbuilt Functions, VBA allows to write user-defined functions as well and statements
are written between Function and End Function.

Sub-Procedures

Sub-procedures work similar to functions. While sub procedures DO NOT Return a value, functions
may or may not return a value. Sub procedures CAN be called without call keyword. Sub
procedures are always enclosed within Sub and End Sub statements.

VBA - Macro Comments

Comments are used to document the program logic and the user information with which other
programmers can seamlessly work on the same code in future.

It includes information such as developed by, modified by, and can also include incorporated logic.
Comments are ignored by the interpreter while execution.

Comments in VBA are denoted by two methods.

• Any statement that starts with a Single Quote (') is treated as comment. Following is an
example.

' This Script is invoked after successful login

' Written by : TutorialsPoint
' Return Value : True / False

• Any statement that starts with the keyword "REM". Following is an example.
REM This Script is written to Validate the Entered Input
REM Modified by : Tutorials point/user2

VBA - Message Box

The MsgBox function displays a message box and waits for the user to click a button and then an
action is performed based on the button clicked by the user.

Syntax
MsgBox(prompt[,buttons][,title][,helpfile,context])
Parameter Description

• Prompt − A Required Parameter. A String that is displayed as a message in the dialog box.
The maximum length of prompt is approximately 1024 characters. If the message extends to
more than a line, then the lines can be separated using a carriage return character (Chr(13))
or a linefeed character (Chr(10)) between each line.
• Buttons − An Optional Parameter. A Numeric expression that specifies the type of buttons
to display, the icon style to use, the identity of the default button, and the modality of the
message box. If left blank, the default value for buttons is 0.
• Title − An Optional Parameter. A String expression displayed in the title bar of the dialog
box. If the title is left blank, the application name is placed in the title bar.
• Helpfile − An Optional Parameter. A String expression that identifies the Help file to use
for providing context-sensitive help for the dialog box.
• Context − An Optional Parameter. A Numeric expression that identifies the Help context
number assigned by the Help author to the appropriate Help topic. If context is provided,
helpfile must also be provided.

The Buttons parameter can take any of the following values −

• 0 vbOKOnly - Displays OK button only.

• 1 vbOKCancel - Displays OK and Cancel buttons.
• 2 vbAbortRetryIgnore - Displays Abort, Retry, and Ignore buttons.
• 3 vbYesNoCancel - Displays Yes, No, and Cancel buttons.
• 4 vbYesNo - Displays Yes and No buttons.
• 5 vbRetryCancel - Displays Retry and Cancel buttons.
• 16 vbCritical - Displays Critical Message icon.
• 32 vbQuestion - Displays Warning Query icon.
• 48 vbExclamation - Displays Warning Message icon.
• 64 vbInformation - Displays Information Message icon.
• 0 vbDefaultButton1 - First button is default.
• 256 vbDefaultButton2 - Second button is default.
• 512 vbDefaultButton3 - Third button is default.
• 768 vbDefaultButton4 - Fourth button is default.
• 0 vbApplicationModal Application modal - The current application will not work until the
user responds to the message box.
• 4096 vbSystemModal System modal - All applications will not work until the user responds
to the message box.

The above values are logically divided into four groups: The first group (0 to 5) indicates the
buttons to be displayed in the message box. The second group (16, 32, 48, 64) describes the style
of the icon to be displayed, the third group (0, 256, 512, 768) indicates which button must be the
default, and the fourth group (0, 4096) determines the modality of the message box.

Return Values

The MsgBox function can return one of the following values which can be used to identify the
button the user has clicked in the message box.

• 1 - vbOK - OK was clicked

• 2 - vbCancel - Cancel was clicked
• 3 - vbAbort - Abort was clicked
• 4 - vbRetry - Retry was clicked
• 5 - vbIgnore - Ignore was clicked
• 6 - vbYes - Yes was clicked
• 7 - vbNo - No was clicked

Example
Function MessageBox_Demo()
'Message Box with just prompt message
MsgBox("Welcome")

'Message Box with title, yes no and cancel Butttons

int a = MsgBox("Do you like blue color?",3,"Choose options")
' Assume that you press No Button
msgbox ("The Value of a is " & a)
End Function
Output

Step 1 − The above Function can be executed either by clicking the "Run" button on VBA Window
or by calling the function from Excel Worksheet as shown in the following screenshot.
Step 2 − A Simple Message box is displayed with a message "Welcome" and an "OK" Button

Step 3 − After Clicking OK, yet another dialog box is displayed with a message along with "yes,
no, and cancel" buttons.

Step 4 − After clicking the ‘No’ button, the value of that button (7) is stored as an integer and
displayed as a message box to the user as shown in the following screenshot. Using this value, it
can be understood which button the user has clicked.

VBA - InputBox
The InputBox function prompts the users to enter values. After entering the values, if the user
clicks the OK button or presses ENTER on the keyboard, the InputBox function will return the text
in the text box. If the user clicks the Cancel button, the function will return an empty string ("").

Syntax
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])
Parameter Description

• Prompt − A required parameter. A String that is displayed as a message in the dialog box.
The maximum length of prompt is approximately 1024 characters. If the message extends to
more than a line, then the lines can be separated using a carriage return character (Chr(13))
or a linefeed character (Chr(10)) between each line.
 • Title − An optional parameter. A String expression displayed in the title bar of the dialog
box. If the title is left blank, the application name is placed in the title bar.
• Default − An optional parameter. A default text in the text box that the user would like to be
displayed.
• XPos − An optional parameter. The position of X axis represents the prompt distance from
the left side of the screen horizontally. If left blank, the input box is horizontally centered.
• YPos − An optional parameter. The position of Y axis represents the prompt distance from
the left side of the screen vertically. If left blank, the input box is vertically centered.
• Helpfile − An optional parameter. A String expression that identifies the helpfile to be used
to provide context-sensitive Help for the dialog box.
• context − An optional parameter. A Numeric expression that identifies the Help context
number assigned by the Help author to the appropriate Help topic. If context is provided,
helpfile must also be provided.

Example

Let us calculate the area of a rectangle by getting values from the user at run time with the help of
two input boxes (one for length and one for width).

Function findArea()
Dim Length As Double
Dim Width As Double

Length = InputBox("Enter Length ", "Enter a Number")

Width = InputBox("Enter Width", "Enter a Number")
findArea = Length * Width
End Function
Output

Step 1 − To execute the same, call using the function name and press Enter as shown in the
following screenshot.
Step 2 − Upon execution, the First input box (length) is displayed. Enter a value into the input box.

Step 3 − After entering the first value, the second input box (width) is displayed.

Step 4 − Upon entering the second number, click the OK button. The area is displayed as shown in
the following screenshot.

VBA - Variables
Variable is a named memory location used to hold a value that can be changed during the script
execution. Following are the basic rules for naming a variable.

• 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, you need to declare the variables before using them.

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

Data Types

There are many VBA data types, which can be divided into two main categories, namely numeric
and non-numeric data types.

Numeric Data Types

Following 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 use

Decimal
+/- 7.9228162514264337593543950335 (28 decimal places).
Non-Numeric Data Types

Following table displays 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

Boolean True or False

Object Any embedded object

Variant (numeric) Any value as large as double

Variant (text) Same as variable-length string

Example

Let us create a button and name it as 'Variables_demo' to demonstrate the use of variables.
Private Sub say_helloworld_Click()
Dim password As String
password = "Admin#1"

Dim num As Integer

num = 1234

Dim BirthDay As Date

BirthDay = DateValue("30 / 10 / 2020")

MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
num & Chr(10) & "Value of Birthday is " & BirthDay
End Sub

Output

Upon executing the script, the output will be as shown in the following screenshot.