[VBA Beginner] Mastering Arrays: From Declaration to Loops

Introduction

When handling multiple pieces of data in VBA, preparing a separate variable for each one can be tedious. For example, if you are handling sales data for 12 months, declaring 12 variables is inefficient.

This is where Arrays come in handy. An array is like a large box with dividers that can store multiple values. You can access each value using a single variable name and a number (index), allowing you to manage large amounts of related data efficiently.

This article explains the basics of VBA arrays: how to declare them, assign values, and use them in loops, in a way that is easy to understand even for beginners.

Basic Usage of Arrays: Sample Code

This macro creates an array containing three department names and writes the contents sequentially downward starting from cell B2.

Complete Code

' Basic usage of arrays
Sub BasicArrayExample()
    
    '--- 1. Declaration of the Array ---
    ' Declare a String array with 3 elements (from index 0 to 2)
    Dim departmentList(0 To 2) As String
    Dim i As Long ' Variable for the loop counter
    
    '--- 2. Assigning Values to the Array ---
    ' Store values by specifying the index number
    departmentList(0) = "HR Dept"
    departmentList(1) = "Sales Dept"
    departmentList(2) = "Dev Dept"
    
    '--- 3. Writing Array Values to Cells Using a Loop ---
    For i = 0 To 2
        ' Write to cells starting from B2, shifting down by i rows
        Worksheets("Sheet1").Range("B2").Offset(i, 0).Value = departmentList(i)
    Next i
    
    MsgBox "The array contents have been written to the cells."

End Sub

Explanation of the Code

1. Declaration of the Array

Dim departmentList(0 To 2) As String

This is where the array is declared.

• Dim departmentList(...): Declares a variable named departmentList.
• (0 To 2): Specifies the size and index range. This creates slots for 3 elements: index 0, 1, and 2.
• As String: Specifies that the data type stored in this array is text.

2. Assigning Values to the Array

departmentList(0) = "HR Dept"

To store a value, use the format VariableName(IndexNumber). departmentList(0) refers to the first slot of the array.

3. Using in Loop Processing

For i = 0 To 2

Arrays work perfectly with For loops. By matching the loop counter i with the array index (0, 1, 2), you can process all elements in order.

...Offset(i, 0).Value = departmentList(i)

• When i is 0: departmentList(0) (“HR Dept”) is written to Offset(0, 0) (Cell B2).
• When i is 1: departmentList(1) (“Sales Dept”) is written to Offset(1, 0) (Cell B3).

Summary

In this article, I explained the fundamental usage of arrays in VBA.

• Declare: Dim ArrayName(Start To End) As DataType
• Access: ArrayName(IndexNumber)
• Process: Combine with For loops to handle large amounts of data efficiently.

Mastering arrays is the first step to freely manipulating data in VBA. Once you learn this, you will be able to create much more powerful programs.