Excel Macros and VBA Introduction

Macros and VBA (Visual Basic for Applications) allow repetitive tasks in Excel to be automated. Instead of performing the same sequence of actions manually every day — such as formatting a report, copying data, or generating summaries — a macro can execute those actions instantly with a single click or keyboard shortcut.

What is a Macro?

A Macro is a recorded or written sequence of instructions that Excel can follow to perform a set of actions automatically. Macros are stored inside Excel workbooks and can be run at any time.

What is VBA?

VBA (Visual Basic for Applications) is the programming language Excel uses to write and run macros. When a macro is recorded, Excel writes VBA code automatically behind the scenes. Macros can also be written directly in VBA for more flexibility and control.

Analogy

Think of a macro as a cooking recipe. Instead of explaining every step each time a dish is made, the recipe (macro) is followed and the result is the same every time. VBA is the language in which the recipe is written.

Enabling the Developer Tab

The Macro and VBA tools are accessed from the Developer tab, which is hidden by default.

How to Enable the Developer Tab

1. Go to File → Options → Customize Ribbon.
2. In the right panel, check the box next to Developer.
3. Click OK. The Developer tab now appears in the Ribbon.

Recording a Macro

Recording a macro is the easiest way to get started. Excel records every action taken and converts it to VBA code automatically.

Step-by-Step: Record a Simple Macro

1. Go to Developer → Record Macro (or View → Macros → Record Macro).
2. Enter a Macro Name (no spaces allowed; use underscores, e.g., Format_Report).
3. Optionally assign a Shortcut Key (e.g., Ctrl + Shift + F).
4. Choose where to store the macro:
   • This Workbook: The macro is saved in the current file only.
   • Personal Macro Workbook: The macro is available in all Excel files.
5. Click OK. Recording begins.
6. Perform the actions to automate (e.g., format cells, apply bold, change colors, etc.).
7. Go to Developer → Stop Recording when done.

Example: Recording a Header Formatting Macro

  Actions recorded:
  1. Select row 1
  2. Apply bold
  3. Set font size to 14
  4. Apply blue background fill
  5. Set font color to white
  6. Center-align the text

  All of these six steps are now saved as a single macro.
  Running the macro performs all six steps instantly.

Running a Macro

• Press the assigned shortcut key (e.g., Ctrl + Shift + F).
• Go to Developer → Macros, select the macro name, and click Run.
• Assign the macro to a button (covered below).

The VBA Editor

The VBA Editor is where macro code can be viewed, edited, and written manually.

Opening the VBA Editor

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

Layout of the VBA Editor

• Project Explorer (left): Shows all open workbooks and their components (sheets, modules).
• Properties Window (bottom left): Displays properties of the selected object.
• Code Window (right): Where VBA code is written and viewed.

Understanding VBA Code Structure

A Recorded Macro — How It Looks in VBA

  Sub Format_Report()
  '
  ' Format_Report Macro
  '

      Rows("1:1").Select
      Selection.Font.Bold = True
      Selection.Font.Size = 14
      With Selection.Interior
          .Color = RGB(0, 70, 127)
      End With
      Selection.Font.Color = RGB(255, 255, 255)
      With Selection
          .HorizontalAlignment = xlCenter
      End With

  End Sub

Key VBA Vocabulary

• Sub: Short for subroutine — marks the beginning of a macro. Every macro starts with Sub and ends with End Sub.
• Rows("1:1").Select: Selects the entire row 1.
• Selection.Font.Bold = True: Makes the selected text bold.
• RGB(r, g, b): Defines a color using red, green, blue values.
• ' (apostrophe): Marks a comment — Excel ignores this line. Used for notes.

Writing a Simple VBA Macro from Scratch

Example: Display a Message Box

  Sub ShowGreeting()
      MsgBox "Hello! Welcome to Excel Macros."
  End Sub

To write this:

1. Open the VBA Editor (Alt + F11).
2. In the Project Explorer, double-click a Module (or insert one: Insert → Module).
3. Type the code in the code window.
4. Press F5 or click the Run button to execute it.

Example: Clear Contents of a Range

  Sub ClearData()
      Range("A2:D100").ClearContents
  End Sub

Example: Copy Data to Another Sheet

  Sub CopyToSummary()
      Sheets("Data").Range("A1:D50").Copy
      Sheets("Summary").Range("A1").PasteSpecial xlPasteValues
      Application.CutCopyMode = False
  End Sub

Using Variables in VBA

Variables store values temporarily while the macro runs.

  Sub CalculateBonus()
      Dim Sales As Double
      Dim Bonus As Double

      Sales = Range("B2").Value

      If Sales > 10000 Then
          Bonus = Sales * 0.1
      Else
          Bonus = Sales * 0.05
      End If

      Range("C2").Value = Bonus
  End Sub

• Dim: Declares a variable (short for Dimension).
• As Double: Specifies the variable stores decimal numbers.
• Range("B2").Value: Reads the value from cell B2.
• If … Then … Else … End If: Conditional logic (same concept as Excel's IF function).

Loops in VBA

Loops repeat an action multiple times — useful for processing rows of data.

Example: Loop Through Rows and Apply Formatting

  Sub HighlightHighSales()
      Dim i As Integer

      For i = 2 To 100
          If Cells(i, 2).Value > 5000 Then
              Cells(i, 2).Interior.Color = RGB(144, 238, 144) ' Light green
          End If
      Next i

  End Sub

• For i = 2 To 100: Repeats the action for rows 2 through 100.
• Cells(i, 2): Refers to the cell at row i, column 2.
• Next i: Moves to the next row.

Assigning a Macro to a Button

1. Go to Developer → Insert → Button (Form Control).
2. Draw the button on the sheet by clicking and dragging.
3. The Assign Macro dialog appears. Select the macro to assign.
4. Click OK. Right-click the button to rename it (e.g., "Format Report").
5. Clicking the button now runs the macro.

Saving a Workbook with Macros

Workbooks containing macros must be saved in the Excel Macro-Enabled Workbook format.

1. Go to File → Save As.
2. In the "Save as type" dropdown, select Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

If saved as a regular .xlsx file, all macros will be permanently removed.

Macro Security

Macros can potentially be used to run harmful code. Excel warns when opening files with macros. Trusted files should only be enabled, not files from unknown sources.

Macro settings are controlled via File → Options → Trust Center → Trust Center Settings → Macro Settings.

Summary

• A Macro is a saved sequence of actions that can be replayed automatically.
• VBA is the programming language used to write and edit macros in Excel.
• Macros are recorded via Developer → Record Macro or written directly in the VBA Editor (Alt + F11).
• Every macro starts with Sub and ends with End Sub.
• Variables store values temporarily; loops repeat actions across rows or ranges.
• Macros can be assigned to buttons for easy one-click execution.
• Workbooks with macros must be saved as .xlsm files to preserve the code.