Working with Arrays

This tip discusses working with arrays in Visual Basic.

Working with Arrays

Reader Tony Beaulieu sends this tip for keeping everything kosher inside arrays. His tip provides a way to use the upper and lower bounds of an array, and ensure that you save memory that might otherwise be wasted with unnecessary empty values.

When adding and subtracting elements to/from arrays, it's important to keep the array clean, allowing no extraneous elements with empty values. The benefit --outside of memory efficiency -- is that you will be able to use the upper and lower bounds properties in your loops (as opposed to fixed elements) confidently without having to handle empty values.

To accomplish this, consider the following two ways of copying a recordset (field = myRS!MyField) to an array (myArray):

Typical way:

2) You won't have to later add or subtract from the array.

My way:

myArray = removeFrom1DimArray(myArray, 5)

Here are the functions you will need:
Function addTo1DimArray(ByVal ataArray As Variant, ByVal ataValue As
Variant) As Variant
On Error GoTo addTo1DimArrayE
Dim tmpArray()
Dim ata_i As Long
If IsEmpty(ataArray) Then
�/�� �/��ReDim tmpArray(0 To 0) 'value should be whatever the option base is
�/�� �/��ReDim tmpArray(LBound(ataArray) To UBound(ataArray) + 1) End If
For ata_i = LBound(tmpArray) To UBound(tmpArray) - 1
�/�� �/��tmpArray(ata_i) = ataArray(ata_i)
tmpArray(UBound(tmpArray)) = ataValue
addTo1DimArray = tmpArray
Exit Function
MsgBox Error, vbCritical, Title
addTo1DimArray = ataArray
End Function

Function removeFrom1DimArray(ByVal rf_Array, ByVal rf_ExcludeElem As
Long) As Variant
If IsEmpty(rf_Array) Then
MsgBox "The array is empty. No removal has been made."
removeFrom1DimArray = rf_Array
Exit Function
End If
If rf_ExcludeElem < LBound(rf_Array) Or rf_ExcludeElem >
UBound(rf_Array) Then
MsgBox "The element to be excluded is invalid. No removal has been made."
removeFrom1DimArray = rf_Array
Exit Function
End If
Dim tmpArray
If LBound(rf_Array) = UBound(rf_Array) Then
removeFrom1DimArray = tmpArray
Exit Function
End If
Dim rf_elem As Long, rf_i As Long
rf_elem = LBound(rf_Array)
ReDim tmpArray(LBound(rf_Array) To UBound(rf_Array) - 1)
For rf_i = LBound(rf_Array) To UBound(rf_Array)
If Not rf_i = rf_ExcludeElem Then
tmpArray(rf_elem) = rf_Array(rf_i)
rf_elem = rf_elem + 1
End If
removeFrom1DimArray = tmpArray
End Function

Tony, thanks for the great tip for our readers, and to show our appreciation, we're sending you a free denim shirt. Enjoy!
Tony Beaulieu currently works as a Senior Developer for Idea Integration (, a leading Internet and new media solutions provider specializing in the comprehensive e-Integration of business and technology. At present he is on an engagement at Arcadis Giffels ( developing web enabled applications in VB as well as Visual Interdev.

Dig Deeper on Win Development Resources

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.