Simple VBA procedures in Excel - Step-by-Step Guide

Introduction

Simple VBA procedures let you automate tasks in Excel by writing small programs. They help you do repetitive work faster without clicking many times.

When you want to quickly format a report with one click.

When you need to insert the same text or numbers repeatedly.

When you want to create a button that runs a task automatically.

When you want to clean or organize data without manual steps.

When you want to open a message box to show information.

Steps

Step 1: Click

- Developer tab on the Excel ribbon

The Developer tab options appear

💡 If Developer tab is not visible, enable it in Excel Options under Customize Ribbon

Step 2: Click

- Visual Basic button in the Developer tab

The Visual Basic for Applications editor opens

Step 3: In the VBA editor, click

- Insert menu > Module

A new blank module window opens for writing code

Step 4: Type

- the module window

Your VBA procedure code appears

💡 Start with: Sub MyProcedureName() and end with End Sub

Step 5: Click

- Run menu > Run Sub/UserForm or press F5

The VBA procedure runs and performs its task

Step 6: Close

- VBA editor window

Return to Excel with your procedure ready to use

Before vs After

Before

Excel workbook with no automation; repetitive tasks done manually

After

Excel workbook with a VBA procedure that runs a task automatically when triggered

Settings Reference

Trust access to the VBA project object model

📍 File > Options > Trust Center > Trust Center Settings > Macro Settings

Allows VBA code to interact with other VBA projects safely

Default: Disabled

Macro Security Level

📍 File > Options > Trust Center > Trust Center Settings > Macro Settings

Controls when macros can run to protect your computer

Default: Disable all macros with notification

Common Mistakes

Not starting the procedure with Sub and ending with End Sub

Excel VBA requires this structure to recognize the procedure

Always write procedures like: Sub ProcedureName() ... End Sub

Running macros when macro security settings block them

Macros will not run if security settings disable them

Check and adjust macro security settings to allow your macros to run

Typing code in the wrong place, like a worksheet cell

VBA code must be written in the VBA editor modules, not in cells

Open the VBA editor and insert a module to write your code

Summary

Simple VBA procedures automate repetitive Excel tasks with small programs.

You write and run these procedures in the VBA editor accessed from the Developer tab.

Always use the correct Sub...End Sub structure and check macro security settings.

Practice

1. What does a simple VBA procedure in Excel always start with?

easy

A. Sub

B. Function

C. Begin

D. Start

Solution

1. Step 1: Understand VBA procedure structure

   A simple VBA procedure always begins with the keyword Sub to define a subroutine.

2. Step 2: Confirm the correct starting keyword

   The procedure does not start with Function, Begin, or Start, only Sub.

3. Final Answer:

   Sub -> Option A

4. Quick Check:

   Simple VBA procedures start with Sub [OK]

Hint: Remember VBA subs always start with 'Sub' keyword [OK]

Common Mistakes:

• Confusing Sub with Function
• Using Begin or Start instead of Sub
• Forgetting to write Sub at the start

2. Which of the following is the correct way to end a simple VBA procedure?

easy

A. End Function

B. Stop

C. End Sub

D. Exit Sub

Solution

1. Step 1: Identify the correct ending for a Sub procedure

   A simple VBA procedure defined with Sub must end with End Sub.

2. Step 2: Differentiate from other endings

   End Function ends a function, Stop pauses code, and Exit Sub exits early but does not end the procedure.

3. Final Answer:

   End Sub -> Option C

4. Quick Check:

   Simple VBA procedures end with End Sub [OK]

Hint: End VBA subs with 'End Sub' exactly [OK]

Common Mistakes:

• Using End Function for subs
• Confusing Exit Sub with End Sub
• Writing Stop instead of End Sub

3. What will the following VBA code do when run?

Sub ShowMessage()
  MsgBox "Hello, Excel!"
End Sub

medium

A. Save the workbook automatically

B. Clear all cells in the worksheet

C. Change the background color of cells

D. Display a message box saying 'Hello, Excel!'

Solution

1. Step 1: Analyze the MsgBox command

   The code uses MsgBox to show a popup message with the text "Hello, Excel!".

2. Step 2: Confirm no other actions are present

   There are no commands to clear cells, change colors, or save the workbook.

3. Final Answer:

   Display a message box saying 'Hello, Excel!' -> Option D

4. Quick Check:

   MsgBox shows a popup message [OK]

Hint: MsgBox always shows a popup message [OK]

Common Mistakes:

• Thinking MsgBox clears cells
• Assuming MsgBox changes colors
• Believing MsgBox saves files

4. Identify the error in this VBA procedure:

Sub ClearCells()
  Range("A1:A10").ClearContents
End Function

medium

A. Procedure ends with End Function instead of End Sub

B. Missing parentheses in ClearContents

C. Range syntax is incorrect

D. Sub keyword is misspelled

Solution

1. Step 1: Check procedure start and end keywords

   The procedure starts with Sub but incorrectly ends with End Function.

2. Step 2: Confirm correct ending for Sub procedures

   Sub procedures must end with End Sub, not End Function.

3. Final Answer:

   Procedure ends with End Function instead of End Sub -> Option A

4. Quick Check:

   Sub procedures end with End Sub [OK]

Hint: Match Sub with End Sub, not End Function [OK]

Common Mistakes:

• Ending Sub with End Function
• Adding unnecessary parentheses to ClearContents
• Incorrect Range syntax
• Misspelling Sub keyword

5. You want to write a VBA procedure that changes the background color of cells A1 to A5 to yellow. Which code snippet correctly does this?

hard

A. Sub ColorCells()
Cells(1,1).Color = vbYellow
End Sub

B. Sub ColorCells()
Range("A1:A5").Interior.Color = vbYellow
End Sub

C. Sub ColorCells()
Range("A1:A5").Color = Yellow
End Sub

D. Sub ColorCells()
Range("A1:A5").Interior.ColorIndex = "Yellow"
End Sub

Solution

1. Step 1: Understand how to set cell background color in VBA

   Use Range(...).Interior.Color with a color constant like vbYellow.

2. Step 2: Check each option for correct syntax

   Sub ColorCells() Range("A1:A5").Interior.Color = vbYellow End Sub uses correct syntax. Sub ColorCells() Range("A1:A5").Color = Yellow End Sub uses .Color without Interior and 'Yellow' without vb prefix. Sub ColorCells() Cells(1,1).Color = vbYellow End Sub changes only one cell and uses .Color incorrectly. Sub ColorCells() Range("A1:A5").Interior.ColorIndex = "Yellow" End Sub uses ColorIndex with a string, which is invalid.

3. Final Answer:

   Sub ColorCells() Range("A1:A5").Interior.Color = vbYellow End Sub -> Option B

4. Quick Check:

   Use Interior.Color = vbYellow to color cells [OK]

Hint: Use Interior.Color = vbYellow to color cells [OK]

Common Mistakes:

• Using .Color without Interior
• Passing color name as string instead of constant
• Changing only one cell instead of range
• Using ColorIndex with string value