MS- EXCEL

VBA
INTRODUCTION:

• VBA stands for Visual Basic for Applications, an event-driven

programming language from Microsoft.
• It is now predominantly used with Microsoft Office applications such
as MS-Excel, MS-Word and MS-Access.
• It helps techies to build customized applications and solutions to
enhance the capabilities of those applications. The advantage of this
facility is that you NEED NOT have visual basic installed on our PC,
however, installing Office will implicitly help in achieving the purpose.
STEPS:
• Go to developer from menu bar
• Click visual basics
 MENU BAR
TOOLBAR

VBA PROJECT:
• IT IS A PROJECT EXPLORER WHICH CONTAINS ALL OBJECTS(i.e., sheets,
workbooks, modules, user forms)
• USER FORM: IT IS USED TO DISPLAY FORM WHICH HAVE CHECK BUTTONS,
RADIO BUTTONS AND SO ON, ON WHICH YOU CAN WORK.
• MODULE: WHATEVER CODING YOU ARE WRITING WILL BE WRITTEN IN
THIS MODULE PART.

PROPERTIES WINDOW:
• IT WILL GIVE YOU ALL THE PROPERTIES AND
VALUES RELATED TO THE OBJECT YOU HAVE
SELECTED
PROJECT BROWSER:
IT IS USED LEARN ABOUT PROJECT
AND ITS PROPERTIES

IMMEDIATE WINDOW:
IF YOU ASK HER A QUESTION, IT WILL GIVE YOU THE
ANSWER.
MACRO:

TO ADD DEVELOPER TO MENU BAR,

Go to file > more options > customize ribbon > select developer > ok

TO ADD DEVELOPER TO MENU BAR(Excel-2007),

Go to file > Excel Options > popular > select “show developer tab in the ribbon” > ok

STEPS:
• Go to developer from menu bar
• Click use related reference
• Click record macro to record
• In the displayed dialogue box give name to the macro or shortcut key to run the macro
• To stop the macro click on stop recording
• To run the macro, click macro > select macro name > click run
RECORD A MACRO:
• Go to developer
• Click on use relative reference
• Click on record macro
• After recording is over, click on stop recording

HOW TO SAVE A WORKBOOK WITH A MACRO:

• Click file, save as
• Click No
• In the Save as box, in the save as type list box, choose Excel Macro-Enabled
Workbook(*.xlsm)
• Click Save.

HOW TO WRITE A SIMPLE MACRO :

• Click developer, then visual basic

• In the displayed window click insert > module
• Anything should be written in sub procedure
• After writing the code, click run from menu bar by keeping the cursor
between Sub and End sub.
EXCEL TERMS:
MODULES:
• Modules is the area where the code is written. 
• To insert a Module, Go 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.
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.
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.
•Any statement that starts with the keyword "REM".
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.
NOTE:
To add buttons click on TAB button.
After coding, click on run to execute the code.
SYNTAX:
MsgBox(prompt [, buttons] [, title] [, helpfile, context])
INPUT BOX:
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])
VARIABLE:
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.
CONSTANT:
Constant is a named memory location 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.

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.

SYNTAX:
Const<<constant_name>> As <<constant_type>>=<<constant_value>>
OPERATORS:
An Operator can be defined using a simple expression - 4 + 5 is equal to 1. Here, 4 and 5 are
called operands and + is called operator. VBA supports following types of operators −
•Arithmetic Operators
•Comparison Operators
•Logical (or Relational) Operators
•Concatenation Operators
Arithmetic Comparison Logical Concatenation
+:Adds two operands =:checks of both operands are AND : If both conditions are +: Adds two values as variable
equal or not , if yes returns true then expression is true values are numeric
true
-:Subtracts second operand >:checks of left operand Or : If any of the two &: Concatenates two values.
from the first greater than right one , if yes conditions are true, then
returns true condition is true
*:Multiplies both the operands <:checks of left operand less NOT: Used to reverse the
than right one , if yes returns logical state of its operand, if
true condition is true it will make it
false.

/:Divides the numerator by >=:checks if left operand XOR: Combination of NOT and
denominator greater than equal to right OR
one , if yes returns true
Arithmetic Comparison Logical Concatenate
%:Modulus operator and <=:checks if left operand less - -
remainder after an integer than equal to right one , if yes
division returns true

^:Exponentiation operator - - -

DECISIONS:
Decision making allows the programmers to control the execution flow of a script or one of its sections. The execution
is governed by one or more conditional statements.
Following is the general form of a typical decision making structure found in most of the programming languages.
SNO STATEMENT & DESCRIPTION
01 An if statement consists of a Boolean expression followed by one or more statements.
SYNTAX:
If(boolean_expression)
Then Statement 1
..... .....
Statement n
End If
02 An if else statement consists of a Boolean expression followed by one or more statements. If the condition is True,
the statements under If statements are executed. If the condition is false, the Else part of the script is executed.
SYNTAX:
If(boolean_expression) Then
Statement 1
..... .....
Statement n
Else
Statement 1
..... ....
Statement n
End If
SNO STATEMENT & DESCRIPTION
03 An if statement followed by one or more ElseIf statements, that consists of Boolean expressions and then followed by
an optional else statement, which executes when all the condition become false.
SYNTAX:
If(boolean_expression) Then
Statement 1
..... .....
Statement n
ElseIf (boolean_expression) Then
Statement 1
..... ....
Statement n
ElseIf (boolean_expression) Then
Statement 1
..... ....
Statement n
Else
Statement 1
..... ....
Statement n
End If
SNO STATEMENT & DESCRIPTION
05 An if or elseif statement inside another if or elseif statement(s).
SYNTAX:
If(boolean_expression) Then
Statement 1
..... .....
Statement n
If(boolean_expression) Then
Statement 1
..... .....
Statement n
ElseIf (boolean_expression) Then
Statement 1
..... ....
Statement n
Else
Statement 1
..... ....
Statement n
End If
Else
Statement 1
..... ....
Statement n
End If
SNO STATEMENT & DESCRIPTION
06 A switch statement allows a variable to be tested for equality against a list of values.
SYNTAX:
Select Case expression
Case expressionlist1
statement1
statement2
.... ....
statement1n
Case expressionlist2
statement1
statement2
.... ....
Case expressionlistn
statement1
statement2
.... ....
Case Else
elsestatement1
elsestatement2
.... ....
End Select
LOOPS:
There may be a situation when you need to execute a block of code several number of times. In general, statements
are executed sequentially: The first statement in a function is executed first, followed by the second, and so on.
SNO STATEMENT & DESCRIPTION
01 Executes a sequence of statements multiple times and abbreviates the code that manages the loop variable.
SYNTAX:
For counter = start To end [Step stepcount]
[statement 1]
[statement 2]
....
[statement n]
[Exit For]
[statement 11]
[statement 22]
....
[statement n]
Next

This is executed if there is at least one element in the group and reiterated for each element in a group.
SYNTAX:
For Each element In Group
[statement 1]
[statement 2]
....
[statement n]
[Exit For]
[statement 11]
[statement 22]
Next
SNO STATEMENT & DESCRIPTION
03 The do..While statements will be executed as long as the condition is True.(i.e.,) The Loop should be repeated till the
condition is False.
SYNTAX:
Do While condition
[statement 1]
[statement 2] ...
[statement n]
[Exit Do]
[statement 1]
[statement 2] ...
[statement n]
Loop
04 WHILE-WEND :
This tests the condition before executing the loop body.
SYNTAX:
While condition(s)
[statements 1]
[statements 2] ...
[statements n]
Wend
STRINGS:
Strings are a sequence of characters, which can consist of either
alphabets, numbers, special characters, or all of them. A variable
is said to be a string if it is enclosed within double quotes " ".
SYNTAX:
Variablename = “string”
SNO FUNCTION & DESCRIPTION SNO FUNCTION & DESCRIPTION
01. InStr: Returns the first occurrence of the specified 08. Ltrim: Returns a string after removing the spaces on the
substring. Search happens from the left to the right. left side of the specified string.

02. InstrRev: Returns the first occurrence of the specified 09. Rtrim: Returns a string after removing the spaces on the
substring. Search happens from the right to the left. right side of the specified string.

03. Ucase: Returns the upper case of the specified string. 10. Trim: Returns a string value after removing both the
leading and the trailing blank spaces.

04. Lcase: Returns the lower case of the specified string. 11. Len: Returns the length of the given string.
05. Left: Returns a specific number of characters from the 12. Replace: Returns a string after replacing a string with
left side of the string. another string.

06. Right: Returns a specific number of characters from the 13. Space: Fills a string with the specified number of spaces.
right side of the string.

07. Mid: Returns a specific number of characters from a 14. String: Returns a string with a specified character for
string based on the specified parameters. specified number of times.
DATE AND TIME FUNCTIONS:

VBScript Date and Time Functions help the developers to convert date and time from one format to another or to
express the date or time value in the format that suits a specific condition.

SNO DATE FUNCTION & DESCRIPTION SNO DATE FUNCTION & DESCRIPTION
01. Date: A Function, which returns the current system 08. Day: A Function, which returns an integer between 1
date. and 31 that represents the day of the specified date.
02. CDate: A Function, which converts a given input to 09 Month: A Function, which returns an integer between 1
date. and 12 that represents the month of the specified date.
03. DateAdd: A Function, which returns a date to which a 10. Year: A Function, which returns an integer that
specified time interval has been added. represents the year of the specified date.
04 DateDiff: A Function, which returns the difference 11. MonthName: A Function, which returns the name of
between two time period. the particular month for the specified date.
05. DatePart: A Function, which returns a specified part of 12. WeekDay: A Function, which returns an integer(1 to 7)
the given input date value. that represents the day of the week for the specified
day.
06. DateSerial: A Function, which returns a valid date for 13. WeekDayName: A Function, which returns the weekday
the given year, month, and date. name for the specified day.
07. IsDate: A Function, which returns a Boolean Value
whether or not the supplied parameter is a date.
SNO TIME FUNCTION & DESCRIPTION
01. NOW: A Function, which returns the current system
date and time.
02 Hour: A Function, which returns an integer between
0 and 23 that represents the hour part of the given
time.
03. Minute: A Function, which returns an integer
between 0 and 59 that represents the minutes part
of the given time.
04 Second: A Function, which returns an integer
between 0 and 59 that represents the seconds part
of the given time.
05. Time: A Function, which returns the current system
time.
06 Timer: A Function, which returns the number of
seconds and milliseconds since 12:00 AM.
07 TimeSerial: A Function, which returns the time for
the specific input of hour, minute and second.
08. TimeValue: A Function, which converts the input
string to a time format.
VARIABLE:
variable is a container to store a value. Sometimes, we are in a position to hold more than one value in a single
variable at a time. When a series of values are stored in a single variable, then it is known as an array variable.
Array Declaration
Arrays are declared the same way a variable has been declared except that the declaration of an array variable
uses parenthesis.
•Array Index cannot be negative.
•VBScript Arrays can store any type of variable in an array. Hence, an array can store an integer, string, or
characters in a single array variable.
Assigning Values to an Array
The values are assigned to the array by specifying an array index value against each one of the values to be
assigned. It can be a string.
Multi-Dimensional Arrays
Arrays are not just limited to a single dimension, however, they can have a maximum of 60 dimensions. Two-
dimensional arrays are the most commonly used ones.
ReDim Statement
ReDim statement is used to declare dynamic-array variables and allocate or reallocate storage space.
SYNTAX:
ReDim[Preserve] varname [subscripts] [, varname(subscripts)]
•Preserve − An optional parameter used to preserve the data in an existing array when you change the size of the
last dimension.
•Varname − A required parameter, which denotes the name of the variable, which should follow the standard
variable naming conventions.
•Subscripts − A required parameter, which indicates the size of the array.
SNO FUNCTION DESCRIPTION
LBound A Function, which returns an integer that corresponds to the smallest subscript of the given
arrays.

UBound A Function, which returns an integer that corresponds to the largest subscript of the given
arrays.
Split A Function, which returns an array that contains a specified number of values. Split based on
a delimiter.
Join A Function, which returns a string that contains a specified number of substrings in an array.
This is an exact opposite function of Split Method.
Filter A Function, which returns a zero based array that contains a subset of a string array based on
a specific filter criteria.
IsArray A Function, which returns a boolean value that indicates whether or not the input variable is
an array.
Erase A Function, which recovers the allocated memory for the array variables.
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 enables 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. In this chapter, you will learn how to
write your own functions in VBA.
Function Definition
A VBA function can have an optional return statement. This is required if you want to return a value from a function.
For example, you can pass two numbers in a function and then you can expect from the function to return their
multiplication in your calling program.
Note − A function can return multiple values separated by a comma as an array assigned to the function name itself.
Before we use a function, we need to define that particular function. The most common way to define a function in
VBA is by using the Function keyword, followed by a unique function name and it may or may not carry a list of
parameters and a statement with End Function keyword, which indicates the end of the function. Following is the
basic syntax.
SYNTAX:
Function Functionname(parameter_list)
statement1
statement 2
…….
statement n
End Function
Calling a Function
• To invoke a function, call the function using the function name.

SUB PROCEDURES:
Sub Procedures are similar to functions, however there are a few differences.
•Sub procedures DO NOT Return a value while functions may or may not return a
value.
•Sub procedures CAN be called without a call keyword.
•Sub procedures are always enclosed within Sub and End Sub statements.

Calling Procedures

To invoke a Procedure somewhere in the script, you can make a call from a
function. We will not be able to use the same way as that of a function as sub
procedure WILL NOT return a value.
EVENTS:
VBA, an event-driven programming can be triggered when you change a cell or range of cell values manually.
Change event may make things easier, but you can very quickly end a page full of formatting. There are two kinds of
events.
•Worksheet Events
•Workbook Events
Worksheet Events

WORKSHEET EVENTS:

Worksheet Events are triggered when there is a change in the worksheet. It is created by performing a right-click on
the sheet tab and choosing 'view code', and later pasting the code.
The user can select each one of those worksheets and choose "WorkSheet" from the drop down to get the list of all
supported Worksheet events.

WORKBOOK EVENTS

Workbook events are triggered when there is a change in the workbook on the whole. We can add the code for
workbook events by selecting the 'ThisWorkbook' and selecting 'workbook' from the dropdown.
ERROR HANDLING:
There are three types of errors in programming: (a) Syntax Errors, (b) Runtime Errors, and (c) Logical
Errors.

SYNTAX ERRORS
Syntax errors, also called as parsing errors, occur at the interpretation time for VBScript. For example, the
following line causes a syntax error because it is missing a closing parenthesis.

RUNTIME ERRORS
Runtime errors, also called exceptions, occur during execution, after interpretation.

LOGICAL ERRORS
Logical errors can be the most difficult type of errors to track down. These errors are not the result of a
syntax or runtime error. Instead, they occur when you make a mistake in the logic that drives your script
and you do not get the result you expected.

ERR OBJECT
Assume if we have a runtime error, then the execution stops by displaying the error message. If we want to
capture the error, then Error Object is used.

ERROR HANDLING
VBA enables an error-handling routine and can also be used to disable an error-handling routine. Without
an On Error statement, any run-time error that occurs is fatal: an error message is displayed, and the
execution stops abruptly.
EXCEL OBJECTS:

When programming using VBA, there are few important objects that a user would be dealing with.
•Application Objects
•Workbook Objects
•Worksheet Objects
•Range Objects

APPLICATION OBJECTS
The Application object consists of the following −
•Application-wide settings and options.
•Methods that return top-level objects, such as ActiveCell, ActiveSheet, and so on.

WORKBOOK OBJECTS
The Workbook object is a member of the Workbooks collection and contains all the Workbook objects currently
open in Microsoft Excel.

WORKSHEET OBJECTS
The Worksheet object is a member of the Worksheets collection and contains all the Worksheet objects in a
workbook.

RANGE OBJECTS
Range Objects represent a cell, a row, a column, or a selection of cells containing one or more continuous
blocks of cells.
TEXT FILES:

You can also read Excel File and write the contents of the cell into a Text File using VBA. VBA allows the users to
work with text files using two methods −
•File System Object
•using Write Command

FILE SYSTEM OBJECT (FSO)

As the name suggests, FSOs help the developers to work with drives, folders, and files

WRITE COMMAND

Unlike FSO, we need NOT add any references, however, we will NOT be able to work with drives, files and folders.
We will be able to just add the stream to the text file.