10 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

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.