AI Reading

Quick summary of this article

Preparing the AI summary…

Advanced Excel Chapter 16: Macros and VBA Automation

In Advanced Excel Chapter 16, you will learn how to automate repetitive Excel tasks using recorded macros and Visual Basic for Applications, commonly called VBA. You will record macros, edit generated code, create procedures, use variables, conditions and loops, work with Excel objects, add buttons, handle errors, and build a practical report-automation project.

Macros can save time and reduce manual errors, but VBA code can also perform powerful actions. Run macros only when you understand the code or trust the workbook’s source.

Learning Objectives

Understand the difference between a macro and VBA.
Display and use the Developer tab.
Record, run, edit, and delete macros.
Save workbooks in a macro-enabled format.
Understand VBA modules, procedures, objects, properties, and methods.
Declare variables and use appropriate data types.
Use If statements, Select Case, and loops.
Automate formatting, data cleaning, and report generation.
Assign a macro to a button.
Debug VBA code and handle errors.
Apply safe macro-security practices.

1. What Is an Excel Macro?

A macro is a stored series of commands that performs a task automatically. For example, a macro can format a sales report, remove blank rows, refresh PivotTables, export a worksheet as PDF, or prepare a monthly management report.

Common Tasks Automated with Macros

Formatting recurring reports
Cleaning imported data
Creating worksheets
Applying formulas
Refreshing PivotTables and data connections
Filtering and sorting records
Generating invoices
Exporting reports as PDF
Sending print commands
Creating standardized workbook templates

2. What Is VBA?

VBA stands for Visual Basic for Applications. It is the programming language used to create and edit macros in desktop versions of Microsoft Office.

Macro Recording	VBA Programming
Records actions performed by the user	Allows instructions to be written manually
Suitable for simple repetitive tasks	Suitable for logical and complex automation
Requires little programming knowledge	Requires an understanding of VBA syntax
May generate unnecessary code	Can be optimized and made reusable
Has limited decision-making ability	Supports conditions, loops, events, and error handling

3. Display the Developer Tab

The Developer tab contains commands for recording macros, opening the Visual Basic Editor, inserting controls, and managing macro security.

Open Excel.
Go to File → Options.
Select Customize Ribbon.
Under Main Tabs, select Developer.
Click OK.

4. Record Your First Macro

In this example, you will record a macro that formats a report heading.

Record the Macro

Go to the Developer tab.
Click Record Macro.
Enter FormatReportHeading as the macro name.
Optionally assign a shortcut key.
Store the macro in This Workbook.
Enter a short description.
Click OK.
Select cells A1:H1.
Apply a dark-blue fill, white bold font, and center alignment.
Increase the row height.
Return to the Developer tab and click Stop Recording.

Macro Naming Rules

The name must begin with a letter.
Do not use spaces in the macro name.
Use meaningful names such as RefreshDashboard.
Underscores may be used, such as Format_Monthly_Report.
Avoid names that conflict with VBA keywords.

5. Run a Macro

Method 1: Macro Dialog Box

Press Alt + F8.
Select the required macro.
Click Run.

Method 2: Developer Tab

Go to Developer → Macros.
Select the macro.
Click Run.

Method 3: Keyboard Shortcut

Use the keyboard shortcut assigned while recording the macro. Avoid overriding important Excel shortcuts such as Ctrl + C or Ctrl + S.

6. Save a Macro-Enabled Workbook

A normal .xlsx workbook cannot retain VBA macro code. Save the workbook in an appropriate macro-enabled format.

File Type	Extension	Use
Excel Macro-Enabled Workbook	`.xlsm`	Standard workbook containing VBA macros
Excel Binary Workbook	`.xlsb`	Binary workbook that can contain macros
Excel Macro-Enabled Template	`.xltm`	Reusable workbook template containing macros
Excel Workbook	`.xlsx`	Does not preserve VBA macros

Save the File

Go to File → Save As.
Select Excel Macro-Enabled Workbook (*.xlsm).
Enter the file name.
Click Save.

7. Relative and Absolute Macro Recording

Absolute Reference Recording

By default, the Macro Recorder records exact cell addresses. If you format cell A1, the recorded macro will normally return to cell A1 when it runs.

Relative Reference Recording

When Use Relative References is enabled, Excel records actions relative to the active cell. This is useful when the same action must be applied at different worksheet locations.

Example

Absolute macro: always formats A1:D1.
Relative macro: formats four cells beginning at the selected cell.

8. Open the Visual Basic Editor

The Visual Basic Editor, or VBE, is where VBA procedures are created, reviewed, edited, and debugged.

Open the Editor

Press Alt + F11, or
Go to Developer → Visual Basic.

Main Visual Basic Editor Windows

Window	Purpose
Project Explorer	Displays open workbooks, worksheets, modules, and forms
Properties Window	Displays properties of the selected object
Code Window	Used to write and edit VBA procedures
Immediate Window	Used to test statements and inspect values
Locals Window	Displays variables during code execution

9. Create a VBA Module

Open the Visual Basic Editor.
Select the workbook in Project Explorer.
Go to Insert → Module.
Write the VBA procedure inside the new module.

Your First VBA Procedure

Sub WelcomeMessage()

    MsgBox "Welcome to Advanced Excel VBA!"

End Sub

Procedure Structure

Sub begins a procedure.
WelcomeMessage is the procedure name.
MsgBox displays a message.
End Sub ends the procedure.

10. Add Comments to VBA Code

Comments explain the purpose of code and are ignored when the macro runs. Begin a comment with an apostrophe.

Sub CalculateTotal()

    'This macro calculates the total sales
    Range("B10").Value = WorksheetFunction.Sum(Range("B2:B9"))

End Sub

11. Understanding Excel’s Object Model

VBA controls Excel through a hierarchy of objects. Important objects include Application, Workbook, Worksheet, Range, Chart, PivotTable, and Table.

Application
    Workbooks
        Workbook
            Worksheets
                Worksheet
                    Range

Object, Property, and Method Example

Worksheets("Sales").Range("A1").Value = "Monthly Sales Report"

Worksheets("Sales") identifies a worksheet object.
Range("A1") identifies a range object.
Value is a property.

Method Example

Worksheets("Sales").Range("A1:H20").ClearContents

ClearContents is a method that removes cell values and formulas while leaving formatting in place.

12. Work with Cells and Ranges

Enter a Value

Sub EnterHeading()

    Worksheets("Sales").Range("A1").Value = "Sales Report"

End Sub

Enter a Formula

Sub EnterTotalFormula()

    Worksheets("Sales").Range("B10").Formula = "=SUM(B2:B9)"

End Sub

Use the Cells Property

Sub EnterValueWithCells()

    Worksheets("Sales").Cells(2, 3).Value = 25000

End Sub

Cells(2, 3) represents the cell in row 2 and column 3, which is C2.

Copy a Range

Sub CopySalesData()

    Worksheets("Sales").Range("A1:H100").Copy _
        Destination:=Worksheets("Report").Range("A1")

End Sub

13. Use the With Statement

The With statement applies several instructions to the same object without repeating its full reference.

Sub FormatTitle()

    With Worksheets("Sales").Range("A1:H1")
        .Merge
        .Value = "Monthly Sales Report"
        .Font.Bold = True
        .Font.Size = 18
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(0, 51, 102)
        .HorizontalAlignment = xlCenter
        .RowHeight = 32
    End With

End Sub

14. VBA Variables and Data Types

A variable stores information that may change while a procedure runs.

Common VBA Data Types

Data Type	Stores	Example
String	Text	Employee name
Long	Whole numbers	Row number or quantity
Double	Numbers with decimals	Sales or percentage
Currency	Currency values	Invoice amount
Date	Date and time	Order date
Boolean	True or False	Validation result
Range	An Excel range object	Selected cells
Worksheet	A worksheet object	Sales worksheet

Declare and Use Variables

Sub VariableExample()

    Dim employeeName As String
    Dim totalSales As Double
    Dim orderCount As Long

    employeeName = "Aarav"
    totalSales = 485000
    orderCount = 24

    MsgBox employeeName & " completed " & orderCount & _
           " orders worth " & Format(totalSales, "₹#,##0")

End Sub

15. Use Option Explicit

Option Explicit forces variables to be declared before use. It helps identify typing mistakes in variable names.

Option Explicit

Sub CalculateProfit()

    Dim salesAmount As Double
    Dim costAmount As Double
    Dim profitAmount As Double

    salesAmount = Range("B2").Value
    costAmount = Range("B3").Value
    profitAmount = salesAmount - costAmount

    Range("B4").Value = profitAmount

End Sub

Place Option Explicit at the top of each module. You can also enable Require Variable Declaration from the VBE options.

16. InputBox and MsgBox

Collect User Input

Sub GetEmployeeName()

    Dim employeeName As String

    employeeName = InputBox("Enter the employee name:")

    If employeeName <> "" Then
        Range("A2").Value = employeeName
        MsgBox "Employee name added successfully."
    Else
        MsgBox "No name was entered.", vbExclamation
    End If

End Sub

Ask for Confirmation

Sub ConfirmClearData()

    Dim userChoice As VbMsgBoxResult

    userChoice = MsgBox( _
        "Do you want to clear the report?", _
        vbYesNo + vbQuestion, _
        "Confirm Action")

    If userChoice = vbYes Then
        Worksheets("Report").Range("A2:H1000").ClearContents
        MsgBox "Report data cleared."
    End If

End Sub

17. If…Then…Else Statement

An If statement runs different instructions depending on whether a condition is true or false.

Sub CheckTarget()

    Dim actualSales As Double
    Dim targetSales As Double

    actualSales = Range("B2").Value
    targetSales = Range("C2").Value

    If actualSales >= targetSales Then
        Range("D2").Value = "Target Achieved"
        Range("D2").Interior.Color = RGB(198, 239, 206)
    Else
        Range("D2").Value = "Below Target"
        Range("D2").Interior.Color = RGB(255, 199, 206)
    End If

End Sub

18. Multiple Conditions with ElseIf

Sub AssignPerformanceGrade()

    Dim achievement As Double
    achievement = Range("B2").Value

    If achievement >= 1 Then
        Range("C2").Value = "Excellent"
    ElseIf achievement >= 0.9 Then
        Range("C2").Value = "Good"
    ElseIf achievement >= 0.75 Then
        Range("C2").Value = "Average"
    Else
        Range("C2").Value = "Needs Improvement"
    End If

End Sub

19. Select Case Statement

Select Case is useful when one value must be compared with several possible options.

Sub ApplyRegionManager()

    Dim regionName As String
    regionName = Range("A2").Value

    Select Case regionName
        Case "North"
            Range("B2").Value = "Aarav"
        Case "South"
            Range("B2").Value = "Meera"
        Case "East"
            Range("B2").Value = "Diya"
        Case "West"
            Range("B2").Value = "Rohan"
        Case Else
            Range("B2").Value = "Unassigned"
    End Select

End Sub

20. For…Next Loop

A loop repeats a set of instructions. The following macro adds a status formula to rows 2 through 100.

Sub AddStatus()

    Dim rowNumber As Long

    For rowNumber = 2 To 100
        If Cells(rowNumber, 2).Value >= Cells(rowNumber, 3).Value Then
            Cells(rowNumber, 4).Value = "Achieved"
        Else
            Cells(rowNumber, 4).Value = "Below Target"
        End If
    Next rowNumber

End Sub

21. Find the Last Used Row

Hard-coding the final row can cause records to be missed. The following statement finds the last populated row in column A:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dynamic Loop Example

Sub CalculateProfitForAllRows()

    Dim lastRow As Long
    Dim rowNumber As Long

    With Worksheets("Sales")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For rowNumber = 2 To lastRow
            .Cells(rowNumber, "J").Value = _
                .Cells(rowNumber, "H").Value - _
                .Cells(rowNumber, "I").Value
        Next rowNumber
    End With

    MsgBox "Profit calculated for all rows."

End Sub

22. For Each Loop

A For Each loop performs an action on every object in a collection or every cell in a range.

Sub HighlightNegativeValues()

    Dim currentCell As Range

    For Each currentCell In Range("J2:J100")
        If IsNumeric(currentCell.Value) Then
            If currentCell.Value < 0 Then
                currentCell.Font.Color = RGB(255, 0, 0)
                currentCell.Font.Bold = True
            End If
        End If
    Next currentCell

End Sub

23. Do While Loop

Sub ProcessUntilBlank()

    Dim rowNumber As Long
    rowNumber = 2

    Do While Cells(rowNumber, "A").Value <> ""
        Cells(rowNumber, "K").Value = Date
        rowNumber = rowNumber + 1
    Loop

End Sub

A loop must have a condition that can eventually become false. Otherwise, it may continue indefinitely.

24. Work with Worksheets

Create a Worksheet

Sub CreateReportSheet()

    Dim reportSheet As Worksheet

    Set reportSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    reportSheet.Name = "Management_Report"

End Sub

Check Whether a Worksheet Exists

Function WorksheetExists(sheetName As String) As Boolean

    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    WorksheetExists = Not ws Is Nothing
    On Error GoTo 0

End Function

Use the Function

Sub CreateSheetIfMissing()

    If WorksheetExists("Report") = False Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Report"
    Else
        MsgBox "The Report worksheet already exists."
    End If

End Sub

25. ThisWorkbook vs ActiveWorkbook

Object	Meaning
`ThisWorkbook`	The workbook containing the VBA code
`ActiveWorkbook`	The workbook currently active on the screen

Use ThisWorkbook when the macro should work with the workbook in which the code is stored. This avoids accidentally modifying another active workbook.

26. Format a Complete Report

Sub FormatSalesReport()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long

    Set ws = ThisWorkbook.Worksheets("Sales")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    With ws.Range(ws.Cells(1, 1), ws.Cells(1, lastColumn))
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(0, 70, 127)
        .HorizontalAlignment = xlCenter
    End With

    With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))
        .Borders.LineStyle = xlContinuous
        .Borders.Color = RGB(210, 210, 210)
    End With

    ws.Columns.AutoFit
    ws.Rows(1).RowHeight = 28
    ws.Range("H2:J" & lastRow).NumberFormat = "₹#,##0"

    MsgBox "Sales report formatted successfully.", vbInformation

End Sub

27. Sort Data with VBA

Sub SortSalesLargestToSmallest()

    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("H2:H" & lastRow), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal

        .SetRange ws.Range("A1:J" & lastRow)
        .Header = xlYes
        .Apply
    End With

End Sub

28. Refresh PivotTables and Connections

Refresh Everything

Sub RefreshDashboard()

    ThisWorkbook.RefreshAll
    MsgBox "Dashboard refresh has been started.", vbInformation

End Sub

Refresh Every PivotTable

Sub RefreshAllPivotTables()

    Dim ws As Worksheet
    Dim pt As PivotTable

    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    MsgBox "All PivotTables refreshed successfully."

End Sub

29. Export a Worksheet as PDF

Sub ExportDashboardAsPDF()

    Dim filePath As String

    If ThisWorkbook.Path = "" Then
        MsgBox "Save the workbook before exporting the PDF.", vbExclamation
        Exit Sub
    End If

    filePath = ThisWorkbook.Path & _
               Application.PathSeparator & _
               "Sales_Dashboard_" & Format(Date, "yyyy-mm-dd") & ".pdf"

    Worksheets("Dashboard").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

    MsgBox "PDF created successfully.", vbInformation

End Sub

30. Assign a Macro to a Button

Using a Shape

Go to Insert → Shapes.
Draw a rounded rectangle.
Enter text such as Refresh Dashboard.
Right-click the shape.
Select Assign Macro.
Select RefreshDashboard.
Click OK.

Using a Form Control Button

Go to Developer → Insert.
Select Button (Form Control).
Draw the button on the worksheet.
Select the required macro.
Click OK.

31. VBA Error Handling

Error handling prevents a macro from stopping abruptly and allows a helpful message to be displayed.

Sub SafeCalculation()

    On Error GoTo ErrorHandler

    Range("B4").Value = Range("B2").Value / Range("B3").Value

    MsgBox "Calculation completed.", vbInformation
    Exit Sub

ErrorHandler:
    MsgBox "The calculation could not be completed: " & _
           Err.Description, vbCritical

End Sub

Error-Handling Structure

On Error GoTo ErrorHandler redirects execution when an error occurs.
Exit Sub prevents the handler from running after successful completion.
Err.Description provides information about the error.

32. Improve Macro Performance

Large macros may run slowly if Excel redraws the screen or recalculates formulas after every action.

Sub OptimizedProcess()

    On Error GoTo CleanUp

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'Place the main automation code here

CleanUp:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox Err.Description, vbCritical
    End If

End Sub

Always restore application settings, even when an error occurs. Otherwise, Excel may remain in manual-calculation mode or stop responding to events.

33. Debug VBA Code

Compile the VBA Project

In the Visual Basic Editor, select Debug → Compile VBAProject to identify syntax and declaration problems.

Use Breakpoints

Click beside a line of code or press F9.
Run the macro.
Execution pauses at the breakpoint.

Run Code One Line at a Time

Press F8 to execute the procedure one statement at a time.

Use the Immediate Window

? Range("B2").Value

Print a Variable

Debug.Print totalSales

34. Macro Security

Macros are active content and can contain harmful instructions. Do not enable macros merely because a workbook looks familiar or appears to come from a known person.

Safe Macro Practices

Enable macros only in files from trusted and verified sources.
Review VBA code when possible.
Keep the default macro-warning protection enabled.
Do not enable all macros globally.
Use trusted locations only for controlled folders.
Use digitally signed macros in managed business environments.
Scan downloaded files with current security software.
Keep a backup before testing unfamiliar code.
Do not store passwords or secret keys directly in VBA code.

35. Common VBA Mistakes

Saving a macro workbook as .xlsx.
Using Select and Activate unnecessarily.
Failing to qualify Range or Cells with a worksheet.
Using Integer for large row numbers instead of Long.
Hard-coding the last data row.
Deleting or clearing data without confirmation.
Failing to restore application settings after an error.
Using On Error Resume Next for an entire procedure.
Running macros without first testing them on sample data.
Enabling macros from untrusted workbooks.

36. Practical Project: Automated Monthly Sales Report

Create a macro-enabled workbook that prepares a monthly management report from raw sales data.

Project Requirements

Create worksheets named Raw_Data, Report, and Dashboard.
Find the last used row automatically.
Calculate Profit for all records.
Apply professional header and number formatting.
Sort records by Sales from highest to lowest.
Refresh all PivotTables.
Add a report generation date.
Export the dashboard as PDF.
Add buttons for Refresh, Format, and Export.
Include error handling.

Master Automation Procedure

Sub GenerateMonthlyReport()

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Call CalculateProfitForAllRows
    Call SortSalesLargestToSmallest
    Call FormatSalesReport
    Call RefreshAllPivotTables

    Worksheets("Dashboard").Range("B2").Value = _
        "Report generated: " & Format(Now, "dd-mmm-yyyy hh:mm")

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    MsgBox "Monthly report generated successfully.", vbInformation
    Exit Sub

ErrorHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    MsgBox "Report generation failed: " & _
           Err.Description, vbCritical

End Sub

37. Practice Exercises

Record a macro that formats a worksheet heading.
Edit the recorded macro to change its font color.
Write a procedure that enters today’s date in cell B2.
Create a variable to store Total Sales.
Use an If statement to compare Sales with Target.
Use a loop to calculate profit for every populated row.
Create a macro that adds a new Report worksheet only when it is missing.
Create a macro to refresh all PivotTables.
Assign the refresh macro to a shape.
Create a macro that exports the Dashboard worksheet as PDF.

38. Chapter 16 Quiz

Question 1

Which file format normally stores an Excel workbook containing VBA macros?

A. .xlsx
B. .csv
C. .xlsm
D. .txt

Answer: C. .xlsm

Question 2

Which keyboard shortcut opens the Visual Basic Editor?

A. Alt + F11
B. Ctrl + F11
C. Shift + F5
D. Ctrl + M

Answer: A. Alt + F11

Question 3

Which VBA statement is used to declare a variable?

A. SetCell
B. Dim
C. Select
D. DeclareCell

Answer: B. Dim

Question 4

Which loop is suitable for processing every worksheet in a workbook?

A. SUMIFS
B. For Each
C. XLOOKUP
D. Goal Seek

Answer: B. For Each

Question 5

When should macros from an external workbook be enabled?

A. Whenever the workbook contains charts
B. Only when the source and contents are trusted
C. Whenever the file name looks professional
D. All macros should always be enabled

Answer: B. Only when the source and contents are trusted

39. Frequently Asked Questions

Do I need programming knowledge to record a macro?

No. The Macro Recorder captures actions without requiring you to write code. Basic VBA knowledge becomes useful when you need to modify, optimize, or extend the recorded procedure.

Why did my macro disappear after saving?

The workbook may have been saved as an .xlsx file. Save macro workbooks as .xlsm, .xlsb, or another macro-compatible format.

What is the difference between ThisWorkbook and ActiveWorkbook?

ThisWorkbook is the workbook containing the VBA code. ActiveWorkbook is whichever workbook is currently active.

Why does my macro work only on one worksheet?

The code may contain fixed worksheet names or unqualified Range references. Check which workbook and worksheet each instruction is targeting.

Can I undo a macro?

Many actions performed by VBA cannot be reversed with Excel’s Undo command. Save a backup and test potentially destructive procedures on sample data first.

Should I use Form Controls or ActiveX Controls?

Form Controls are usually simpler for assigning standard macros. ActiveX controls provide additional properties and events but may have more compatibility and security considerations.

40. Chapter 16 Summary

In Advanced Excel Chapter 16, you learned how to record and run macros, use the Visual Basic Editor, create procedures, declare variables, apply conditions and loops, control workbook objects, refresh reports, and export worksheets as PDF.

You also learned essential debugging, error-handling, performance, and macro-security practices. These skills allow you to transform repetitive Excel processes into consistent button-controlled workflows.

Next Chapter

Advanced Excel Chapter 17: Formula Auditing and Workbook Security will explain how to trace formulas, identify errors, evaluate calculations, manage links, protect worksheets and workbooks, inspect files, and safely distribute professional Excel solutions.