Lesson 5: Writing Excel VBA Code

5.1 Introduction to Writing VBA Code

Writing Excel VBA 365 code (Excel's macro language) requires using the Excel VBA Editor. This powerful integrated development environment (IDE) allows you to create, edit, and debug VBA macros that can automate virtually any task in Excel.

5.2 Accessing the VBA Editor

The simplest way to launch the Excel VBA Editor is:

1. Click on the Developer tab in Excel
2. Click Insert in the Controls group
3. Select an Active-X control (typically the Command Button)

5.3 Understanding the VBA Editor Interface

After inserting a command button, click on it to enter the Excel VBA Editor (VBE), as shown in Figure 5.2. The VBE consists of several key components:

• Project Explorer - Shows all open workbooks and their components
• Properties Window - Displays properties for selected objects
• Code Window - Where you write and edit your VBA code
• Immediate Window - For debugging and testing code snippets

5.4 Basic VBA Code Structure

Excel VBA code is organized into procedures, typically event procedures that respond to user actions. The most common structure is:

Private Sub CommandButton1_Click()
  ' Your VBA code goes here
End Sub
    

This structure creates an event procedure that runs when the user clicks CommandButton1. Excel VBA is event-driven, meaning code executes in response to specific events like button clicks, workbook openings, or cell changes.

5.5 Practical VBA Code Examples

Example 5.1: Simple Message Box

Private Sub CommandButton1_Click()
  MsgBox "Welcome to Excel VBA 365"
End Sub
    

This simple example displays a message box when the button is clicked. The MsgBox function is one of the most commonly used VBA functions for displaying information to users.

Example 5.2: Using Variables

Private Sub CommandButton1_Click()
  Dim YourMsg As String
  YourMsg = "Welcome to Excel VBA 365"
  MsgBox YourMsg
End Sub
    

This example demonstrates variable declaration and assignment. Using variables makes your code more flexible and easier to maintain.

Example 5.3: Mathematical Operation

Private Sub CommandButton1_Click()
  Dim x As Integer, y As Integer
  x = 105
  y = 20
  MsgBox x Mod y
End Sub
    

This example calculates the modulus (remainder) of two numbers. The Mod operator is useful for cyclic calculations and determining divisibility.

Example 5.4: String Concatenation

Private Sub CommandButton1_Click()
  Dim A As String, B As String, C As String, D As String, E As String
  A = "Tom"
  B = "likes"
  C = "to"
  D = "eat"
  E = "burger"
  MsgBox A & " " & B & " " & C & " " & D & " " & E
End Sub
    

This example demonstrates string concatenation using the & operator. Note that we've added spaces between words for proper formatting. The result is the sentence "Tom likes to eat burger".

Example 5.5: Interactive Input

Private Sub CommandButton1_Click()
  Dim userName As String
  userName = InputBox("Please enter your name", "User Input")
  If userName <> "" Then
    MsgBox "Hello, " & userName & "! Welcome to Excel VBA 365."
  Else
    MsgBox "You didn't enter a name."
  End If
End Sub
    

This new example shows how to get user input using the InputBox function and respond differently based on whether the user provided input.

Example 5.6: Simple Calculator

Private Sub CommandButton1_Click()
  Dim num1 As Double, num2 As Double, result As Double
  num1 = InputBox("Enter first number", "Calculator")
  num2 = InputBox("Enter second number", "Calculator")
  
  ' Basic error handling
  If IsNumeric(num1) And IsNumeric(num2) Then
    result = num1 + num2
    MsgBox "The sum is: " & result, vbInformation, "Calculation Result"
  Else
    MsgBox "Please enter valid numbers", vbExclamation, "Input Error"
  End If
End Sub
    

This example creates a simple calculator that adds two numbers, demonstrating basic error handling to ensure numeric input.

VBA Coding Best Practices

Follow these guidelines to write better VBA code:

• Use Option Explicit - Always declare variables by adding Option Explicit at the top of each module
• Meaningful Names - Use descriptive names for variables and procedures
• Consistent Indentation - Improve readability with proper indentation
• Add Comments - Explain complex logic with comments
• Error Handling - Include error handling routines
• Modularize Code - Break complex tasks into smaller procedures

Summary

🔗 Related Resources

• Excel VBA Classic Tutorial
• Excel VBA Examples
• Excel VBA 2010 Tutorial
• Visual Basic Tutorial