VBA re-dimensioning array based on a loop

Question

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

FWIW you should probably declare the array with a known/fixed size from the start, instead of copying the entire array at every iteration; computing the size of the array before looping to populate it would probably perform better. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 20, 2018 at 15:53

CallumDA · Accepted Answer · 2018-02-20 15:48:13Z

3

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

edited Feb 20, 2018 at 15:48

answered Feb 20, 2018 at 15:39

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CallumDA Over a year ago

That works too, although I didn't mention it because I think it's better to explicitly state your base :)

user2261597 · Accepted Answer · 2018-02-20 16:15:30Z

0

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.

answered Feb 20, 2018 at 16:15

user2261597

2 Comments

Mike Mirabelli Over a year ago

Thanks I'll give it a go now. I didn't realize there was a limitation to using Preserve keyword

user2261597 Over a year ago

Sorry Mike, did not accuse or suspect you. Just curious who outside you did. Also Mike, if you know C++, a Collection is like a vector<item> in that language

Collectives™ on Stack Overflow

VBA re-dimensioning array based on a loop

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related