What does ReDim Preserve do?

Question

I am looking at someone else's vba excel code. they are doing ReDim Preserve dataMatrix(7, i) in both loops. What does this do?

Also, it seems like the second loop just overwrites the data in the first loop, is that correct?

Dim dataMatrix() As String

    Worksheets.Item("ETS").Select
    Do While Trim(Cells(r, 1)) <> ""
       Debug.Print "The line: ", Trim(Cells(r, 1)), r
        r = r + 1
        dataMatrix(1, i) = Trim(Cells(r, 1))    ''file name
        dataMatrix(2, i) = Trim(Cells(r, 2))    ''sample type
        dataMatrix(3, i) = Trim(Cells(r, 3))    ''sample name
        dataMatrix(4, i) = "ETS"    ''
        dataMatrix(5, i) = Trim(Cells(r, 5))    ''Response
        dataMatrix(6, i) = Trim(Cells(r, 6))    ''ISTD Response
        dataMatrix(7, i) = Trim(Cells(r, 10))   ''Calculated Conc
        i = i + 1
        ReDim Preserve dataMatrix(7, i)
    Loop

    r = 5
    Worksheets.Item("ETG").Select
    Do While Trim(Cells(r, 1)) <> ""
       Debug.Print "The line: ", Trim(Cells(r, 1)), r
        r = r + 1
        dataMatrix(1, i) = Trim(Cells(r, 1))    ''file name
        dataMatrix(2, i) = Trim(Cells(r, 2))    ''sample type
        dataMatrix(3, i) = Trim(Cells(r, 3))    ''sample name
        dataMatrix(4, i) = "ETG"
        dataMatrix(5, i) = Trim(Cells(r, 5))    ''Response
        dataMatrix(6, i) = Trim(Cells(r, 6))    ''ISTD Response
        dataMatrix(7, i) = Trim(Cells(r, 10))   ''Calculated Conc
        i = i + 1
        ReDim Preserve dataMatrix(7, i)
    Loop

aphoria · Answer 1 · 2010-05-26T19:25:11.743

19

Redim Preserve allows you to change the dimensions of an array while keeping the contents of the array.

The Redim Preserve at the end of each loop is adding another row to the array.

I think the second loop is appending to the array because the i variable is not changed between the loops.

edited May 26 '10 at 19:25

answered May 26 '10 at 19:19

aphoria

19,796
7
64
73

also, it seems like the second loop just overwrites the data in the first, loop, is that correct? – Alex Gordon May 26 '10 at 19:20
No, the second loop is appending to the array because `i` is not reset between the loops. – aphoria Sep 17 '12 at 13:34
And your point is? I got an upvote a couple days ago which caught my attention. I added the comment 2 days ago, but I also answered it in my answer. – aphoria Sep 19 '12 at 14:52
2

Well, the "answer" has been in my answer for the past two years. I didn't do it for your benefit. I was here, so I updated the comments for any one who might look at it in the future. – aphoria Sep 19 '12 at 17:48
I can't tell if you're just being funny or a jerk. If you're trying to be funny, I apologize for thinking otherwise. – aphoria Sep 19 '12 at 18:52
In that case, you're doing it very well. Although, I don't understand your intent. – aphoria Sep 19 '12 at 19:00

YasirA · Accepted Answer · 2010-05-26T19:28:40.523

14

ReDim preservers a data in an array when you modify its size. Also it shouldn't overwrite the data becasuse i is incremented every iteration of the loop: the first loop won't stop until it encounters an empty cell, changing array length every iteration by one, so that when the lenght is 8 it adds 1 to the i and the new length becomes 9. When the second iteration occurs it writes the new element to the bound of the array with index 9, modifies its length again so that it becomes 1 element longer and iterates until encounters an empty line.

edited May 26 '10 at 19:28

answered May 26 '10 at 19:19

YasirA

9,531
2
40
61

it seems like the second loop just overwrites the data in the first, loop, is that correct? – Alex Gordon May 26 '10 at 19:21
The second loop doesn't overwrite the data in the first loop, because i doesn't get reset after the first loop. The second loop appends stuff to the data matrix generated in the first loop. – Francis Gagné May 26 '10 at 19:37

What does ReDim Preserve do?

2 Answers2

Linked