Arrays

Published on February 7, 2013

Efficient Array Looping

VBA provides the LBound and UBound functions for retrieving the lower and upper bounds of an array.  When looping through an array many developers will conserve lines of code by putting the LBound and/or UBound functions in the loop test statement. This can decrease the performance of your application as these functions are called on every iteration through the loop.

To improve the performance of your application you should assign the LBound and UBound values to variables prior to entering the loop. Then use the variables in the loop test statement.  Using this method will only make a single call to these functions instead of one call on every iteration.

The subroutine that follows illustrates the two methods.

'
' Efficient array looping
'
Sub ArrayExample1()
    Dim myArray() As Long
    Dim numItems As Long
    Dim indexCount As Long
    Dim indexStart As Long
    Dim indexEnd As Long
    numItems = 9999999
    ReDim myArray(numItems)
    '
    ' Commonly used method for looping through an array
    ' The UBound function is called on every iteration
    '
    For indexCount = LBound(myArray) To UBound(myArray)
        '
        ' Do stuff here
        '
    Next
    '
    ' For better performance do this
    ' The UBound function is only called once
    '
    indexStart = LBound(myArray)
    indexEnd = UBound(myArray)
    For indexCount = indexStart To indexEnd
        '
        ' Do stuff here
        '
    Next
End Sub

 

 

Determining if an array is dimensioned

The above example brings up a common question: “How can you determine if an array has been dimensioned?”  Using the LBound or UBound functions on arrays that have not been dimensioned will raise an error. Use this to your advantage to create your own function to make this determination. The custom function IsArrayDimd listed below illustrates how to accomplish this.

'
' Determine if array is dimensioned
'
Sub ArrayExample2()
    Dim myArray() As Long
    '
    ' Array is not dimensioned, will display "False"
    '
    MsgBox CStr(IsArrayDimd(myArray)), vbInformation, "IsArrayDimd"
    ReDim myArray(10)
    '
    ' Now that array is dimensioned, will display "True"
    '
    MsgBox CStr(IsArrayDimd(myArray)), vbInformation, "IsArrayDimd"
End Sub
'
' Custom function to do the test
'
Public Function IsArrayDimd(inArray() As Long) As Boolean
    On Error GoTo NOT_DIMD
    '
    ' The following line will raise an error if the array
    ' has not been dimensioned. It will always return “True”
    ' for any array that has been dimensioned.
    '
    IsArrayDimd = UBound(inArray) >= 0
    Exit Function
NOT_DIMD:
    IsArrayDimd = False
End Function

Putting it all together

Now that you can efficiently loop through an array, and determine if an array has been dimensioned, you can put it all together as is illustrated in the following subroutine.

'
' Combining Examples 1 and 2
'
Sub ArrayExample3()
    Dim myArray() As Long
    Dim numItems As Long
    Dim indexCount As Long
    Dim indexStart As Long
    Dim indexEnd As Long
    numItems = 9999999
    '
    ' If array is not yet dimensioned, dimension it
    '
    If Not IsArrayDimd(myArray) Then
        ReDim myArray(numItems)
        '
        ' You could also raise your own custom error here
        ' and pass it off to your error handler
        '
    End If

    indexStart = LBound(myArray)
    indexEnd = UBound(myArray)
    For indexCount = indexStart To indexEnd
        '
        ' Do stuff here
        '
    Next
End Sub

Tags:
Like this article? Share it!

Leave a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Privacy Policy