VBA MACRO

https://www.tutorialspoint.com/vba/vba_loops.htm
WHAT IS VBA?
• VBA stands for Visual Basic for Applications an event-driven
programming language from Microsoft that is now predominantly used
with Microsoft office applications such as MSExcel, 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.
• You can use VBA in all office versions, right from MS-Office 97 to MS-
Office 2013 and also with any of the latest versions available. Among
VBA, Excel VBA is the most popular. The advantage of using VBA is that
you can build very powerful tools in MS Excel using linear programming.
Benefits of using VBA:

• Automate repetitive tasks.

• Build custom functions and tools.
• Enhance productivity.
Recording a Macro

1.What is a Macro Recorder?

•A tool that records your actions in Excel and translates them into VBA code.
2.Step-by-Step Macro Recording:
•Go to the Developer Tab → Click Record Macro.
•Perform simple tasks, e.g., formatting cells (bold, font color, alignment).
•Stop recording (Developer Tab → Stop Recording).
3.Run the Macro:
•Go to the Developer Tab → Macros → Select Macro Name → Run.
4.Viewing the Recorded Code:
•Open the VBA Editor (Alt + F11
).

•Locate your macro under Modules.

Exercise:

• Record a macro that:

• Formats a range of cells (change font size, add borders).
• Changes the background color of the cells
Editing a Recorded Macro

1.Editing Basics:
•Open the VBA Editor.
•Modify the recorded code (e.g., change the range of cells or font size).
•Example: Change "A1:A5" to "B1:B5".
2.Exercise:
•Record a macro to add bold formatting to cells.
•Open the VBA Editor and modify the code to apply bold and italic formatting instead.
Writing Simple VBA Code
•Basics of Writing VBA Code
1.Structure of VBA Code:
•Subroutines: Begin with Sub and end with End Sub.
Sub
HelloWorld() MsgBox "Hello, World!"
End Sub

•Comments: Use an apostrophe (') to add comments.

•Basic commands: Range, Cells, MsgBox.
2.Writing Your First Code:
•Open VBA Editor → Insert a new Module.
•Write a simple "Hello World" macro:

Sub
HelloWorld() MsgBox "Hello, welcome to VBA!"
End Sub
•Run the macro in the editor using the Run button (or F5).
Working with Ranges
Selecting and Modifying Ranges:
and Loops
vba
Copy code
Sub FormatRange()
Range("A1:A5").Font.Bold = True
Range("A1:A5").Interior.Color = RGB(200, 200, 255)
End Sub
For Loops Example:

Automating a task for multiple cells:

vba
Copy code
Sub LoopExample()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = "Row " & i
Next i
End Sub
Exercise

Write a VBA code to:

Fill cells B1:B10 with numbers 1 to 10.

Format the numbers to bold.
Error Handling and Debugging Basics
Syntax errors.
Runtime errors (e.g., invalid range).
Using MsgBox for Debugging:

vba
Copy code
Sub DebugExample()
MsgBox "Starting the macro"
Range("A1:A5").Value = "Test"
MsgBox "Macro completed"
End Sub
Using Debug.Print:

Outputs information to the Immediate Window:

vba
Copy code
Sub DebugExample()
Debug.Print "Loop started"
Dim i As Integer
For i = 1 To 5
Debug.Print "Row: " & i
Next i
End Sub
Exercise

Write a code that formats cells but includes MsgBox and Debug.Print to track
progress.
HOW TO ACCESS VBA EDITOR?
2 WAYS
1. In Excel window, press "ALT+F11". A VBA window opens up as
shown in the following screenshot.
HOW TO ACCESS VBA EDITOR?
2 WAYS
• Step 1 − First, enable 'Developer' menu in Excel 20XX. To do the
same, click File → Options.
• Step 2 − Click ‘Customize the Ribbon’ tab and check 'Developer'.
Click 'OK’.
• Step 3 − The 'Developer' ribbon appears in the menu bar.
• Step 4 − Click the 'Visual Basic' button to open the VBA Editor.
EXCEL TERMS
 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
 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.
 2 TYPES OF
PROCEDURE
 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-Procedure
• 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.
COMMENTS
 Comment
• 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.
• Any statement that starts with the keyword "REM". Following is an
example.
MESSAGE BOX
 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])
 Message Box
• 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.
 Message Box
• 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.character (Chr(10)) between each
line.
• 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.
Buttons
Buttons
INPUT BOX
 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])
 Input Box
• 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.
 Input Box
• 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
VARIABLE
 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.
• In VBA, you need to declare the variables before using them.
• Syntax : Dim <<variable_name>> As <<variable_type>>
DATA TYPES
 Numeric Data Types
• There are many VBA data
types, which can be divided
into two main categories,
namely numeric and non-
numeric data types.
 Non-Numeric Data Types
• Following table displays the
non-numeric data types and
the allowed range of values.
OPERATORS
 Operator
• An Operator can be defined using a simple expression - 4 + 5 is
equal to 9. 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
 Operator
Following arithmetic
operators are supported
by VBA.
• Assume variable A holds
5 and variable B holds
10, then −
DECISIONS
 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.
LOOPS
 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.
• Programming languages provide
various control structures that
allow for more complicated
execution paths.
• A loop statement allows us to
execute a statement or group of
statements multiple times.
Following is the general form of a
loop statement in VBA.
Try
1. Using for loop and if…else, Show the name of
students using msgbox the who got 15 and
below score.
Activity
• Create a sub procedure using For Loop and
If…Else statement that will write if the student
failed to quiz or not. If student is below 24 the
grade is FAILED and if the student grade is 24
above then the student grade is PASSED.