CODEWITHSUNDEEP
arraysvbapreserve
Mike Mirabelli
Mike Mirabelli

Reputation: 410

VBA re-dimensioning array based on a loop

I am trying to fill in an array but increasing the element size for dim 1 on each iteration, but am getting a "Subscript out of range message" when I try 

ReDim Preserve sArray(1 To jTotal, 1 To UBound(sArray, 2))

Any ideas?

Dim j As Integer, jTotal As Integer
Dim eCount As Integer

ReDim sArray(1, 6)

For j = 1 To Application.Session.AddressLists.Item(AddList).AddressEntries.Count

    On Error GoTo Err

    If VBA.InStr(1, Application.Session.AddressLists.Item(AddList).AddressEntries.Item(j).GetExchangeUser.Department, _
    fFilter, vbTextCompare) > 0 Then

    jTotal = jTotal + 1

'    '--> Redimensioning the array
    ReDim Preserve sArray(1 To UBound(sArray, 1), 1 To UBound(sArray, 2))
    'ReDim Preserve sArray(1 To jTotal, 1 To UBound(sArray, 2))

    sArray(jTotal, 1) = j
    sArray(jTotal, 2) = Application.Session.AddressLists.Item(AddList).AddressEntries.Item(j).Name
    sArray(jTotal, 3) = Application.Session.AddressLists.Item(AddList).AddressEntries.Item(j).GetExchangeUser.Alias
    sArray(jTotal, 4) = Application.Session.AddressLists.Item(AddList).AddressEntries.Item(j).GetExchangeUser.PrimarySmtpAddress
    sArray(jTotal, 5) = Application.Session.AddressLists.Item(AddList).AddressEntries.Item(j).GetExchangeUser.BusinessTelephoneNumber
    sArray(jTotal, 6) = Application.Session.AddressLists.Item(AddList).AddressEntries.Item(j).GetExchangeUser.Department

    If Err.Number > 0 Then eCount = eCount + 1

    End If

Next j

Upvotes: 2

Views: 162

Answers (2)

user2261597
user2261597

Reputation:

Try rewriting your code using Collection:

Dim sArray As New Collection

Add new elements with 

sArray.Add(item)

You will never need to use redim with a Collection. It is handles automatically. Google on VBA Collection for additional info.

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

As has been mentioned in the comments, you can only resize the last dimension of an array (which is the second dimension in your example)

Also, watch out for this: ReDim sArray(1, 6). It creates an array with dimensions (0 to 1, 0 to 6) not (1 to 1, 1 to 6). So Redim Preserve sArray(1 to 1, 1 to 6) still won't work because you will be resizing the first dimension again.

Re-dimension your array something like this:

Sub Test()
    Dim sArray As Variant
    ReDim sArray(1 To 1, 1 To 6)
    ReDim Preserve sArray(1 To 1, 1 To 7)
End Sub

Upvotes: 3

Related Questions