VBA Collection to Python list (With Examples)

VBA Collection Example

VBA

Sub ProcessEmployeeData()
    ' Demonstrates Collection usage with both indexed and keyed access
    Dim employees As New Collection
    Dim empData As String
    Dim i As Integer
    
    ' Add employees with keys for lookup
    employees.Add "John Smith", "EMP001"
    employees.Add "Jane Doe", "EMP002"
    employees.Add "Bob Johnson", "EMP003"
    
    ' Insert new employee at specific position (before index 2)
    employees.Add "Alice Brown", "EMP004", , 2
    
    ' Process all employees using For Each
    Debug.Print "All employees:"
    For Each empData In employees
        Debug.Print empData
    Next empData
    
    ' Access by key for quick lookup
    If employees.Count > 0 Then
        Debug.Print "Found employee: " & employees.Item("EMP002")
    End If
    
    ' Remove employee by index (1-based)
    employees.Remove 1
    Debug.Print "Remaining count: " & employees.Count
End Sub

Python list/dict Equivalent

Python

def process_employee_data() -> None:
    """Demonstrates list and dict usage equivalent to VBA Collection."""
    # Use list for ordered access and dict for keyed lookup
    employees_list: list[str] = []
    employees_dict: dict[str, str] = {}
    
    # Add employees (equivalent to Collection.Add with keys)
    employees_list.extend(["John Smith", "Jane Doe", "Bob Johnson"])
    employees_dict.update({
        "EMP001": "John Smith",
        "EMP002": "Jane Doe", 
        "EMP003": "Bob Johnson"
    })
    
    # Insert new employee at specific position (before index 2, 0-based)
    employees_list.insert(1, "Alice Brown")
    employees_dict["EMP004"] = "Alice Brown"
    
    # Process all employees using for loop
    print("All employees:")
    for emp_data in employees_list:
        print(emp_data)
    
    # Access by key for quick lookup
    if len(employees_list) > 0:
        print(f"Found employee: {employees_dict['EMP002']}")
    
    # Remove employee by index (convert to 0-based)
    del employees_list[0]
    print(f"Remaining count: {len(employees_list)}")

Common VBA → Python Mappings

VBA Collection	Python list/dict
Dim c As New Collection	c = []
c.Add item	c.append(item)
c.Add item, key	d[key] = item
c.Remove(index)	del c[index-1]
c.Item(index)	c[index-1]
c.Count	len(c)

Side-by-Side Comparison

VBA

Dim orders As New Collection
orders.Add "Order A", "ORD001"
orders.Add "Order B", "ORD002"

' Check if key exists before adding
On Error Resume Next
orders.Add "Order C", "ORD001"
If Err.Number <> 0 Then
    Debug.Print "Key already exists"
End If

Python

orders_dict: dict[str, str] = {}
orders_dict["ORD001"] = "Order A"
orders_dict["ORD002"] = "Order B"

# Check if key exists before adding
if "ORD001" in orders_dict:
    print("Key already exists")
else:
    orders_dict["ORD001"] = "Order C"

Important Differences

Index Base: VBA Collections use 1-based indexing while Python lists use 0-based indexing, requiring index adjustments during migration.
Hybrid Nature: VBA Collections support both numeric and string key access, requiring separate Python list and dict structures for equivalent functionality.
Key Conflicts: VBA Collections raise errors on duplicate keys while Python dicts silently overwrite existing values.
Advanced Features: Python lists support slicing, list comprehensions, and direct index assignment which VBA Collections do not offer.

Have VBA code using Collections?

See how Collection patterns map to Python lists and dicts instantly.

Convert My VBA Code