Introduction to Visual
Basic for Applications
Introduction 2
The Tedious Way - Manual Data Entry and The Efficient Way - Automated Analysis and Reporting with
Reporting VBA
– What is VBA? - VBA stands for Visual Basic for Applications, a programming
language that allows you to automate tasks and extend the functionality of
Excel.
– Why use VBA? - VBA can save you countless hours by automating repetitive
tasks, creating custom functions, and building interactive excel sheets.
Getting Started with VBA Tools 3
• Firstly, go to options , and
click on customize Ribbon
• In the main Tabs , Toggle the
developer tab
• Now , you will be able to see
the Developer tab
• Open it , and click on Visual
Basic
VBA Interface 4
• Right click on your
excel file name
and insert module.
• The play , pause
and stop buttons
can be used to run
your code
• The VBA Interface
is not the only way
to execute your
code , You can also
insert a Button and
assign a macro to
it.
Subroutines - Definition and Purpose 5
– A subroutine is a block of code that performs a specific task.
– Subroutines are essential for building efficient and well-structured VBA
applications.
– Subroutines begin with “Sub SubroutineName()” and end with “End Sub”.
NOTE – “‘” is used to comment in a
VBA code (comments do not get
executed when subroutine is run)
– In this particular example the Subroutine name is “example”
Variables and DIM - What and Why? 6
– What are variables? Variables are named containers that store data in
memory.
– Why use variables? Variables allow you to store and manipulate data
efficiently, making your code more flexible and dynamic.
– What is DIM? The DIM statement is used to declare variables, giving them a
name and optionally specifying their data type.
EXAMPLE:
Syntax – DIM Variablename AS Datatype
Data Types 7
– Common Data Types in VBA :
– Choosing the correct data type for your variables is important for efficient
memory usage and accurate calculations.
– VBA offers a variety of data types to accommodate different kinds of data.
Obtaining Data from Cells - Introduction 8
– VBA provides methods to retrieve data from cells in your Excel
worksheets.
– This allows you to use cell values in your code for calculations,
comparisons, and other operations.
– Two common methods are:
– Cells(row , column).Value: Uses row and column numbers to access cell values.
– Range(“Cell address”).Value: Uses cell references (e.g., "A1") to access cell values.
Obtaining Data from Cells - Examples 9
– This code retrieves the value from cell A1 (row 1, column 1) and stores it in the
variable Age.
– It then displays the value of Age in a message box.
NOTE - MsgBox is the function used to display a message box.
The message to be displayed is enclosed in double quotation marks. The "&"
symbol is used to concatenate (join) strings in VBA. EXAMPLE :
Arithmetic Calculations 10
– VBA allows you to perform various arithmetic calculations within your code.
– You can use standard mathematical operators like:
– “+” for addition
– “-” for subtraction
– “*” for multiplication
– “/” for division
Outputting to Cells 11
– VBA allows you to write calculated values or other data back to cells in your
Excel worksheets.
– This enables you to dynamically update your spreadsheet based on your
code's logic and results.
– The Range().Value property is used to assign values to specific cells.
• Note - In VBA, you can declare multiple variables of the
same data type in a single Dim statement by separating
them with commas.
• The code Dim LengthInches As Double, LengthMeters As
Double declares two variables, LengthInches and
LengthMeters, both of which are of the Double data type.
Conditional Statements - Introduction 12
– Conditional statements allow your code to make decisions based on specific
conditions.
– The If...Then...Else construct is used to execute different code blocks depending on
whether a condition is True or False.
– The ElseIf and Else blocks are optional. You can have an If statement without them.
– The conditions must be logical expressions that result in True or False.
– Indentation is not mandatory, but it greatly improves code readability.
– Basic syntax :
Conditional Statements - Example 13
– The If statement checks if Number is greater than 0. If True, it displays a
message box stating the number is positive.
– The ElseIf statement checks if Number is less than 0. If True, it displays a
message box stating the number is negative.
– The Else statement handles the remaining case where Number is 0.
For Loop - Introduction 14
– For loops allow you to repeat a block of code a specific number of times.
– They are ideal for iterating through a known range of values or performing repetitive
actions on a set of data.
– Syntax :
For Loop - Example 15
– This code declares two variables: Number and Factorial.
– It assigns a value to Number.
– The For loop iterates from 1 to Number.
– In each iteration, Factorial is multiplied by the current value of i.
– Finally, it displays the calculated factorial in a message box.
16
Thank you
