15 VBA vs Python Examples

1. Hello World / Message Box

The classic first program. In VBA you reach for MsgBox to display output; in Python, print() writes to the console.

MsgBox "Hello, World!"

print("Hello, World!")

• MsgBox opens a modal dialog box that pauses execution until the user clicks OK.
• print() writes to standard output (the terminal) and does not block execution.
• VBA strings use double quotes only; Python accepts single or double quotes.

2. Variables and Type Declarations

VBA requires you to declare variables with Dim and an explicit type. Python infers types automatically at assignment time.

Dim name As String
Dim age As Long
Dim salary As Double
name = "Alice"
age = 30
salary = 75000.50

name = "Alice"
age = 30
salary = 75000.50

• Python does not need Dim — types are inferred from the assigned value.
• No As String, As Long, or As Double keywords.
• Python variables can be reassigned to a different type at any time (dynamic typing).

3. If / ElseIf / Else Conditionals

Branching logic looks similar in both languages, but the syntax markers differ. VBA uses Then and End If; Python uses colons and indentation.

If score >= 90 Then
    grade = "A"
ElseIf score >= 80 Then
    grade = "B"
ElseIf score >= 70 Then
    grade = "C"
Else
    grade = "F"
End If

if score >= 90:
    grade = "A"
elif score >= 80:
    grade = "B"
elif score >= 70:
    grade = "C"
else:
    grade = "F"

• Python uses a colon : instead of Then.
• ElseIf becomes elif (one word, lowercase).
• No End If — indentation defines the block boundaries.

4. For Loop with Range

Counting loops are one of the most common constructs in VBA macros. Python's range() is the direct equivalent, but the upper bound is exclusive.

Dim total As Long
total = 0
For i = 1 To 10
    total = total + i
Next i

total = 0
for i in range(1, 11):
    total += i

• range(1, 11) produces 1 through 10 — the upper bound is exclusive, so you add 1.
• Python supports += shorthand; VBA does not.
• No Next i — the indented block defines the loop body.

5. For Each / Iterating Collections

VBA's For Each iterates over collections like ranges or arrays. Python's for ... in loop is a for-each by default — there is no separate keyword.

Dim item As Variant
For Each item In myCollection
    Debug.Print item
Next item

for item in my_collection:
    print(item)

• Python's for loop is always a for-each — no separate syntax needed.
• No Variant type declaration required; Python handles any type dynamically.
• Debug.Print becomes print().

6. String Manipulation (Trim, Case, Substring)

VBA uses standalone functions like Trim(), UCase(), and Left(). Python uses string methods and slice syntax instead.

Dim s As String
s = "  Hello World  "
s = Trim(s)
s = UCase(s)
result = Left(s, 5)
pos = InStr(s, "WORLD")

s = "  Hello World  "
s = s.strip()
s = s.upper()
result = s[:5]
pos = s.find("WORLD") + 1  # VBA is 1-based

• Python uses methods on the string object: .strip(), .upper(), .lower().
• Slice syntax s[:5] replaces Left(s, 5); s[-5:] replaces Right(s, 5).
• .find() returns a 0-based index; VBA's InStr returns 1-based. Add 1 for parity.

7. Reading / Writing Excel Cells

Cell access is the heart of most VBA macros. In Python with openpyxl, the API is similar but uses named parameters and lowercase properties.

Dim val As Variant
val = ws.Cells(1, 1).Value
ws.Cells(2, 1).Value = "Result"
ws.Range("A3").Value = val * 2

val = ws.cell(row=1, column=1).value
ws.cell(row=2, column=1).value = "Result"
ws["A3"].value = val * 2

• openpyxl uses ws.cell(row=, column=) with named arguments instead of positional Cells(r, c).
• Range-style access uses ws["A3"] — bracket notation, not a method call.
• Properties are lowercase: .value not .Value.

8. Error Handling

VBA uses On Error GoTo with labeled sections. Python uses structured try/except blocks that are more readable and maintainable.

On Error GoTo ErrHandler
result = riskyOperation()
Exit Sub
ErrHandler:
    MsgBox "Error: " & Err.Description

try:
    result = risky_operation()
except Exception as e:
    print(f"Error: {e}")

• Python uses structured try/except instead of GoTo labels.
• No need for Exit Sub before the handler — the block structure handles flow automatically.
• You can catch specific exception types (e.g., except ValueError) for finer control.

9. Select Case (Switch)

VBA's Select Case is a powerful multi-branch construct. Python traditionally uses if/elif/else chains, which remain the most idiomatic approach.

Select Case dayOfWeek
    Case 1
        name = "Monday"
    Case 2, 3
        name = "Mid-week"
    Case Is >= 6
        name = "Weekend"
    Case Else
        name = "Other"
End Select

if day_of_week == 1:
    name = "Monday"
elif day_of_week in (2, 3):
    name = "Mid-week"
elif day_of_week >= 6:
    name = "Weekend"
else:
    name = "Other"

• Python 3.10+ has match/case, but if/elif/else is still the most common pattern.
• Multiple values in one case (Case 2, 3) become in (2, 3) in Python.
• Range comparisons like Case Is >= 6 become a simple elif condition.

10. Dictionaries (Scripting.Dictionary)

VBA requires a COM object (Scripting.Dictionary) for key-value storage. Python dictionaries are a built-in data type — no imports or object creation needed.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "name", "Alice"
dict.Add "age", 30
If dict.Exists("name") Then
    MsgBox dict.Item("name")
End If
dict.Remove "age"

d = {}
d["name"] = "Alice"
d["age"] = 30
if "name" in d:
    print(d["name"])
del d["age"]

• Python dicts are built-in — no CreateObject or late binding needed.
• The in keyword replaces .Exists() for membership testing.
• del d["key"] replaces .Remove; bracket syntax d["key"] replaces .Item().

Quick Reference Table

11. Open, Edit, and Save a Workbook

Sub ProcessReport()
    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Reports\Q1.xlsx")
    wb.Sheets("Summary").Cells(1, 1).Value = "Updated"
    wb.Save
    wb.Close
End Sub

import openpyxl

def process_report():
    wb = openpyxl.load_workbook("C:/Reports/Q1.xlsx")
    ws = wb["Summary"]
    ws.cell(row=1, column=1).value = "Updated"
    wb.save("C:/Reports/Q1.xlsx")
    # No explicit close needed — Python handles it

Note the path separator: VBA uses backslashes, Python uses forward slashes (or raw strings). openpyxl doesn't keep the file locked like VBA does.

12. Create a Pivot-Style Summary

Sub SumByCategory()
    Dim d As Object, k As Variant
    Set d = CreateObject("Scripting.Dictionary")
    Dim i As Long
    For i = 2 To 100
        k = Cells(i, 1).Value
        If d.Exists(k) Then
            d(k) = d(k) + Cells(i, 2).Value
        Else
            d.Add k, Cells(i, 2).Value
        End If
    Next i
End Sub

from collections import defaultdict
import openpyxl

def sum_by_category():
    wb = openpyxl.load_workbook("data.xlsx")
    ws = wb.active
    totals = defaultdict(float)
    for row in range(2, 101):
        key = ws.cell(row=row, column=1).value
        val = ws.cell(row=row, column=2).value or 0
        totals[key] += val
    return dict(totals)

Python's defaultdict eliminates the Exists/Add boilerplate. For larger datasets, pandas.groupby().sum() is even cleaner.

13. Generate a Bar Chart

Sub CreateChart()
    Dim ch As Chart
    Set ch = Charts.Add
    ch.ChartType = xlColumnClustered
    ch.SetSourceData Source:=Range("A1:B10")
    ch.HasTitle = True
    ch.ChartTitle.Text = "Sales by Region"
End Sub

from openpyxl.chart import BarChart, Reference

def create_chart(ws):
    chart = BarChart()
    chart.title = "Sales by Region"
    data = Reference(ws, min_col=2, min_row=1, max_row=10)
    cats = Reference(ws, min_col=1, min_row=2, max_row=10)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws.add_chart(chart, "D2")

openpyxl supports bar, line, pie, scatter, and area charts. The chart object is embedded in the worksheet — no separate chart sheet needed.

14. Send an Email (Outlook)

Sub SendEmail()
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    Dim mail As Object
    Set mail = olApp.CreateItem(0)
    mail.To = "team@company.com"
    mail.Subject = "Report Ready"
    mail.Body = "The Q1 report is attached."
    mail.Send
End Sub

import smtplib
from email.message import EmailMessage

def send_email():
    msg = EmailMessage()
    msg["To"] = "team@company.com"
    msg["Subject"] = "Report Ready"
    msg["From"] = "you@company.com"
    msg.set_content("The Q1 report is attached.")

    with smtplib.SMTP("smtp.company.com", 587) as s:
        s.starttls()
        s.login("you@company.com", "password")
        s.send_message(msg)

VBA's Outlook COM automation only works on Windows with Outlook installed. Python's smtplib works cross-platform. For Outlook-specific features, use win32com on Windows.

15. File System Operations

Sub ListFiles()
    Dim fso As Object, folder As Object
    Dim f As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\Reports")
    For Each f In folder.Files
        If Right(f.Name, 5) = ".xlsx" Then
            Debug.Print f.Name & " - " & f.Size
        End If
    Next f
End Sub

from pathlib import Path

def list_files():
    folder = Path("C:/Reports")
    for f in folder.glob("*.xlsx"):
        print(f"{f.name} - {f.stat().st_size}")

Python's pathlib replaces VBA's FileSystemObject with a cleaner, cross-platform API. The .glob() method handles pattern matching that VBA does with Dir().

Related Guides

• Complete Syntax Reference (53 functions) — Every VBA keyword and function mapped to Python in one page.
• Deep Dive: VBA Loops — For/Next, Do While/Until, Step, Exit For, and nested loops.
• String Function Reference — Trim, Left, Right, Mid, InStr, Replace, and more.
• GoTo & Error Handling — On Error GoTo, Resume, and structured exception handling.