Loop array issue

Question

I am trying to write a simple array, I have written many arrays but I don't understand why on this occasion I am not getting the desired result.

Below is the code I am using, so I am generating a report name and putting them in an array to retrieve later on by referencing the index number,

So the debug within the loop works and I can see theem being stored, when I am outside the loop cannot retrieve it so cannot recall.

Any ideas?

  For i = 1 To num
        reportname = API & SetPeriod & APIEnd
        ReDim retrieve(1 To i)
        retrieve(i) = reportname
        SetPeriod = SetPeriod + 1
        Debug.Print retrieve(i)
    Next i
    ReDim retrieve(1 To 4)
    Debug.Print retrieve(2)
    Debug.Print retrieve(2)

If you showed more code it would be helpful. It also looks like you ReDim'em but alos didnt preserve. that also might account for it — Doug Coats
– Doug Coats, Commented Mar 17, 2018 at 21:52
Why ReDim retrieve(1 To i) in the loop? Why not simply ReDim retrieve(1 To num) prior to the loop? (ReDim Preserve in the loop would probably give you the results that you expect but would be very inefficient since ReDim Preserve is an expensive operation). — John Coleman
– John Coleman, Commented Mar 17, 2018 at 21:55
that is a very good question @JohnColeman you are correct that will solve the problem as I will always know my array size which is driven by num. — Mohammad Hussain
– Mohammad Hussain, Commented Mar 17, 2018 at 21:57
That “ReDim retrieve(1 To 4)” is clearing the array. So don’t use it! — DisplayName
– DisplayName, Commented Mar 17, 2018 at 21:58

K.Dᴀᴠɪs · Accepted Answer · 2018-03-18 00:55:36Z

1

If you are wanting to keep the values inside your array when you resize it, then you will need to use the Preserve option when you ReDim.

So your statement would look like:

ReDim Preserve retrieve(1 To i)

But if it's at all possible, try to declare the size of your array one time as changing its size multiple times can cause performance issues, especially when using Preserve.

answered Mar 18, 2018 at 0:55

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

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

Comments

Shai Rado · Accepted Answer · 2018-03-18 06:38:24Z

1

Since you already set and get the value of variable num before entering your For i = 1 to Num loop, you can use that value to Redim your array to the right size, just use ReDim retrieve(1 To num), and you don't need to Redim every time you enter the loop`.

Modified Code

ReDim retrieve(1 To num) ' redim you array to the size of num

For i = 1 To num
    reportname = API & SetPeriod & APIEnd
    retrieve(i) = reportname
    SetPeriod = SetPeriod + 1
    Debug.Print retrieve(i)
Next i

answered Mar 18, 2018 at 6:38

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

Loop array issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related