Reusing 2D array in VBA Excel?

Question

New here, and new to VBA and programming in general.

I am trying to reuse multiple arrays (through a loop), but I assume I need to clear them out before I reuse them? I have tried searching through the questions, but I can't find a solution, or frankly can't understand the solutions if they work.

Dim WS As Worksheet

For Each WS In Worksheets
    If Right(WS.Name, 4) = "Data" Then Comp = Comp + 1
Next WS

Dim CompArray(10, 50) As Variant
Dim RatesArray(10, 1 To 50) As Variant
Dim IndexArray(10, 50) As Variant
Dim ShortIndexArray(10) As Long
Dim MRow, DRow, FRow, RRow, Col As Long
Dim LastCol, LastRow As Long

MRow = 4
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


Do Until MRow > LastRow

    '**** MY CODE ****

''''***!!!*** Trying to Clear the array and reuse with the same dimensions for each loop ***!!!***
' not working

''Erase CompArray, RatesArray, IndexArray, ShortIndexArray
''ReDim CompArray(10, 50)
''ReDim RatesArray(10, 1 To 50)
''ReDim IndexArray(10, 50)
''ReDim ShortIndexArray(10)

MRow = MRow + 6 + Comp
Loop




End Sub

so when I go to the next step of the main loop, the arrays that I want to use have the same information. I want to keep the same names and dimensions of the arrays, but just clear out the contents. It sounds simple to me, but I have no idea how to do this.

I tried erasing and redim-ing (that was a solution to a similar problem on here) but it didnt work, it actually was saying I was declaring the arrays twice.

I also tried dim-ing the arrays while already in the loop. That didn't work either.

Any help would be appreciated!

Thanks.

@RBarryYoung Probably attempting to use the type in the statement, after it's already been declared. ReDim CompArray(10, 50) As Variant will fail. ReDim CompArray(10, 50) won't. — Sam
– Sam, Commented Dec 3, 2013 at 17:10
@Sam That's what I suspected too at first, but the code that's commented-out doesn't have that. As far as I can tell, if the ReDims were just uncommented, it should work. — RBarryYoung
– RBarryYoung, Commented Dec 3, 2013 at 17:12
ahh.. He needs to declare the arrays with no dimensions initially, or you get the Array already dimensioned error, when you try to ReDim. — Sam
– Sam, Commented Dec 3, 2013 at 17:14

Sam · Accepted Answer · 2013-12-03 17:09:04Z

4

If you want to reuse an array then you shouldn't specify the dimensions when you first declare it

Dim CompArray() As Variant

Then when you want to initialize it you use the keyword Redim

ReDim CompArray(10, 50)

If you redim your array again then you will loose any data already stored in it, unless you use the Preserve keyword

ReDim Preserve CompArray(10, 60)

And if you're redimensioning your array and preserving the content then you can only change the last dimension

so ReDim Preserve CompArray(10, 80) will work as you're changing the last element. ReDim Preserve CompArray(100, 80) will cause an error as you're attempting to change the first element and preserve the data.

ReDim CompArray(100, 800) will work fine, but you will lose all your currently stored data.

answered Dec 3, 2013 at 17:09

Sam

7,3333 gold badges27 silver badges37 bronze badges

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

2 Comments

RBarryYoung Over a year ago

+1: Oh right! I forgot that arrays could be dynamic or static in VBA and that you use the Dim to distinguish them. (they're always dynamic in VB.net)

lpau001 Over a year ago

Wow! Thanks Sam and RBarryYoung. You guys are excellent. Solution works great.

Collectives™ on Stack Overflow

Reusing 2D array in VBA Excel?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related