[VBA] How to Start Array Indices at 1 and Using LBound/UBound

Introduction

In VBA, array indices (the numbers that identify each element) start at 0 by default. However, there are many cases where managing data using a 1-based index is more intuitive, such as when dealing with “Months 1 to 12.”

VBA allows you to specify both the starting and ending index numbers when declaring an array. Furthermore, by using the LBound (Lower Bound) and UBound (Upper Bound) functions, you can automatically retrieve the start and end indices of an array. This allows you to write safer and more flexible loops.

In this article, I will explain how to start array indices at 1 and how to create reliable loops using LBound and UBound.

Sample Code: Array Starting at Index 1

This macro declares an array with five elements using indices 1 to 5. It then loops through the array and writes the content starting from cell C2 downward.

' How to start array indices at 1
Sub CustomIndexArrayExample()
    
    '--- 1. Declare an array with indices from 1 to 5 ---
    Dim cityList(1 To 5) As String
    Dim i As Long ' Variable for the loop counter
    
    '--- 2. Assign values to each element of the array ---
    cityList(1) = "Sapporo"
    cityList(2) = "Sendai"
    cityList(3) = "Yokohama"
    cityList(4) = "Kobe"
    cityList(5) = "Naha"
    
    '--- 3. Loop using LBound and UBound ---
    ' LBound(cityList) returns 1, and UBound(cityList) returns 5
    For i = LBound(cityList) To UBound(cityList)
        ' Adjust the offset using (i - 1) to match the cell row index
        Worksheets("Sheet1").Range("C2").Offset(i - 1, 0).Value = cityList(i)
    Next i
    
    MsgBox "Array content has been written to the cells."

End Sub

Note: If code tries to access an index like myList(0) for an array declared as Dim myList(1 To 3), a “Subscript out of range” error occurs. This sample code ensures that the declaration and loop ranges match correctly.

Code Explanation

1. Dim cityList(1 To 5) As String

When declaring an array, you can specify the index range using the format (Start To End).

• Writing (1 To 5) creates an array with five elements: indices 1, 2, 3, 4, and 5.
• It is also possible to start from a number other than 1, such as (101 To 105).

2. LBound and UBound Functions

These functions are key to performing loop operations safely without needing to manually track the array’s index range.

• LBound(ArrayName): Returns the smallest index number (Lower Bound) of the array. In this example, it returns 1.
• UBound(ArrayName): Returns the largest index number (Upper Bound) of the array. In this example, it returns 5.

Instead of hard-coding values like For i = 1 To 5, writing For i = LBound(cityList) To UBound(cityList) means the loop code will not need modification even if the array size is changed later (e.g., to Dim cityList(1 To 10)). This is a vital technique for writing maintainable code.

Summary

In this article, I explained how to set the starting index of a VBA array to 1 and how to use the LBound and UBound functions.

• By writing Dim ArrayName(Start To End) during declaration, you can set the index range.
• Using LBound and UBound to specify loop ranges creates flexible and safe code that adapts to changes in array size.

By setting the starting index to 1 according to the nature of the data and utilizing LBound/UBound, you can achieve more intuitive programming with fewer errors.