Working with Arrays

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 searchVB.com denim shirt. Enjoy!
Tony Beaulieu currently works as a Senior Developer for Idea Integration (http://www.idea.com), 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 (http://www.arcadis-us.com) developing web enabled applications in VB as well as Visual Interdev.

This was first published in September 2000

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.