VBA editor basics in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant learning to automate tasks in Excel.

📋 Request: Your manager wants you to create a simple macro that formats a sales report with bold headers and autofit columns.

📊 Data: You have a sales report table with columns: Date, Product, Quantity, and Sales Amount.

🎯 Deliverable: Create a VBA macro that formats the header row in bold and adjusts the column widths to fit the content.

Sample Data

Date	Product	Quantity	Sales Amount
2024-01-01	Apples	10	50
2024-01-02	Bananas	5	20
2024-01-03	Cherries	15	75
2024-01-04	Dates	7	35
2024-01-05	Elderberries	3	30

1

Step 1: Open the VBA editor by pressing Alt + F11 in Excel.

No formula needed.

Expected Result

The VBA editor window opens.

2

Step 2: Insert a new module to write your macro.

In the VBA editor, click Insert > Module.

Expected Result

A new blank module appears in the editor.

3

Step 3: Write a macro named FormatReport that makes the first row bold and autofits columns A to D.

Sub FormatReport() Rows("1:1").Font.Bold = True Columns("A:D").AutoFit End Sub

Expected Result

Macro code is entered without errors.

4

Step 4: Return to Excel and run the macro.

Press Alt + F8, select FormatReport, then click Run.

Expected Result

The header row becomes bold and columns A to D adjust their width to fit the content.

Final Result

Date       | Product     | Quantity | Sales Amount
-----------------------------------------------
2024-01-01 | Apples      | 10       | 50
2024-01-02 | Bananas     | 5        | 20
2024-01-03 | Cherries    | 15       | 75
2024-01-04 | Dates       | 7        | 35
2024-01-05 | Elderberries| 3        | 30

✓The macro successfully formats the header row in bold.

✓Columns automatically adjust to fit the data width.

✓Using VBA editor basics, simple repetitive formatting can be automated.

Bonus Challenge

Modify the macro to also color the header row background light yellow.

Show Hint

Use Rows("1:1").Interior.Color = RGB(255, 255, 153) inside the macro.

Practice

1. What is the shortcut key to open the VBA editor in Excel?

easy

A. Alt + F11

B. Ctrl + V

C. Shift + F5

D. Ctrl + Alt + Delete

Solution

1. Step 1: Recall the VBA editor shortcut

   The VBA editor in Excel is opened using a specific keyboard shortcut.

2. Step 2: Identify the correct shortcut

   The correct shortcut to open the VBA editor is Alt + F11, which is a standard across Excel versions.

3. Final Answer:

   Alt + F11 -> Option A

4. Quick Check:

   VBA editor shortcut = Alt + F11 [OK]

Hint: Remember Alt + F11 opens VBA editor fast [OK]

Common Mistakes:

• Confusing with copy-paste shortcuts
• Trying Ctrl + V
• Using Ctrl + Alt + Delete

2. Which of the following is the correct way to insert a new module in the VBA editor?

easy

A. Home > Insert > Worksheet

B. File > New Workbook

C. Right-click on VBAProject > Insert > Module

D. Right-click on Sheet1 > Delete

Solution

1. Step 1: Understand how to add code containers

   In VBA editor, code is written inside modules which must be inserted first.

2. Step 2: Identify the correct menu path

   Right-clicking on the VBAProject and choosing Insert > Module is the correct way to add a new module.

3. Final Answer:

   Right-click on VBAProject > Insert > Module -> Option C

4. Quick Check:

   Insert module = Right-click VBAProject > Insert > Module [OK]

Hint: Right-click VBAProject to insert modules quickly [OK]

Common Mistakes:

• Confusing Excel sheet insert with VBA module insert
• Trying to insert module from Excel ribbon
• Deleting sheets instead of inserting modules

3. What will happen if you run this VBA code in the editor?

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

medium

A. A message box appears with text 'Hello, Excel!'

B. The code will cause a syntax error

C. Nothing happens because MsgBox is not valid

D. Excel will close immediately

Solution

1. Step 1: Understand the MsgBox function

   MsgBox is a VBA function that shows a popup message box with the given text.

2. Step 2: Analyze the code behavior

   The code defines a subroutine that when run, displays a message box with "Hello, Excel!" text.

3. Final Answer:

   A message box appears with text 'Hello, Excel!' -> Option A

4. Quick Check:

   MsgBox "Hello, Excel!" shows popup message [OK]

Hint: MsgBox shows popup messages in VBA [OK]

Common Mistakes:

• Thinking MsgBox is invalid
• Expecting code to close Excel
• Confusing syntax error with runtime behavior

4. Identify the error in this VBA code snippet:

Sub Test()
  MsgBox "Welcome to VBA"
  End Subd

medium

A. Missing quotation marks around text

B. Incorrect end sub statement spelling

C. MsgBox is not a valid function

D. Sub name cannot be Test

Solution

1. Step 1: Check the End Sub statement

   The code ends with 'End Subd' which is a typo and invalid in VBA.

2. Step 2: Confirm correct syntax

   The correct ending for a subroutine is 'End Sub' exactly, so the typo causes a syntax error.

3. Final Answer:

   Incorrect end sub statement spelling -> Option B

4. Quick Check:

   End Sub must be spelled exactly [OK]

Hint: Check End Sub spelling carefully to avoid errors [OK]

Common Mistakes:

• Ignoring typo in End Sub
• Thinking MsgBox is invalid
• Assuming sub name causes error

5. You want to automate formatting cells in Excel using VBA. Which steps correctly describe how to start this in the VBA editor?

hard

A. Open VBA editor, delete all sheets, then write formatting code in ThisWorkbook

B. Open Excel options, enable macros, then type code directly in a worksheet cell

C. Use Excel ribbon to record macro, then paste code into a new worksheet

D. Open VBA editor with Alt + F11, insert a module, write code to select cells and apply formatting

Solution

1. Step 1: Open VBA editor and insert module

   Use Alt + F11 to open the VBA editor, then insert a new module to write your code.

2. Step 2: Write code to select and format cells

   In the module, write VBA code that selects the desired cells and applies formatting commands.

3. Final Answer:

   Open VBA editor with Alt + F11, insert a module, write code to select cells and apply formatting -> Option D

4. Quick Check:

   VBA automation starts with editor, module, then code [OK]

Hint: Start VBA automation by inserting module and writing code [OK]

Common Mistakes:

• Trying to write code in worksheet cells
• Deleting sheets unnecessarily
• Confusing macro recording with direct coding