Arrays

[dave8]

Is there a way to do something like this:

Dim Array1 (1 to num, 1 to 5) as variant


Excel doesn't seem to like the variable, num. I want to be able to expand the rows as data is entered.

 

[jasonb75]

What are you trying to do with the data?

I'm nowhere near an expert on VBA so I'm not saying this is how it should be done, but more a common method would be something like

Dim lastrow as long
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Followed by a With block or a For / Next loop to process the data in a specifed range, ending at lastrow.

 

[Ruddles]

You need to start by Dimming the array as () and then immediately ReDimming it to your desired size, in this case (1 To 5, 0). Only the last dimension of an array can be resized.

In your VBA code, when your array pointer exceeds the upper bound of the second dimension, you ReDim the array with the second diimension one larger. Preserve ensures that the contents are preserved.

Public Sub Demo()
  Dim arr()
  Dim arrPtr As Integer
  
  ReDim arr(1 To 5, arrPtr)
  
  '
  ' do some stuff
  '
  ' now we need to store something in array
  '
  arrPtr = arrPtr + 1
  If arrPtr > UBound(arr, 2) Then ReDim Preserve arr(1 To 5, UBound(arr, 2) + 1)
  arr(1, arrPtr) = "something"
  arr(2, arrPtr) = "something"
  arr(3, arrPtr) = "something"
  arr(4, arrPtr) = "something"
  arr(5, arrPtr) = "something"
  '
  ' more stuff
  '
 
End Sub

ReDim Preserve is a very slow process so I'd recommend starting the array with a higher second dimension and ReDimming it plus 10 or 50 or 100 - select these values depending on the sort of size you think your array (a) usually is and (b) might reach.

 

[Ruddles]

I'd recommend starting the array with a higher second dimension and ReDimming it plus 10 or 50 or 100 - select these values depending on the sort of size you think your array (a) usually is and (b) might reach.

Does that make sense? What I mean is, you should try to minimise the number of times you ReDim an array. Start it at the sort of size your average dataset is and set the incremental amount at a level whereby you only have to ReDim a handful of times.

If your aim in all this is so you can use UBound to point to the last data item in the array, you'll find yourself ReDimming every time you add an item - not a good idea performance-wise. Set up a pointer (arrPtr in my example) and use that to keep track of your last item.

 

[xenou]

ReDim Preserve is a very slow process so I'd recommend starting the array with a higher second dimension and ReDimming it plus 10 or 50 or 100 - select these values depending on the sort of size you think your array (a) usually is and (b) might reach.

I've never noticed any problems redimming and I've done it fairly often. Is this well documented?

 

[DonkeyOte]

The Preserve is the potentially "expensive" part - ReDim itself is not an issue, eg:

Dim num As Long
Dim dblArray(1 to 1, 1 to 10) As Variant
num = 10 'this would be dynamic
ReDim dblArray(1 to num, 1 to 5)
'populate

That said using a Variant I'm not sure you really need to pre-define the dimensions at all... but we don't know how you're populating the Array.

 

[Ruddles]

I shouldn't have blandly stated "very slow". It's relative and if you're not doing it a lot it's not noticeable, however the effect exists and is measurable.

As an example, I wrote a loop which cycles a million times generating a random number, incrementing a pointer and checking the UBound() of an array: it ran in time taken approx one-tenth of a second.

Then I added code to ReDim the array and store a value in it, thus:-

  For iLoop = 1 To iLimit
    iValue = Rnd()
    arrPtr = arrPtr + 1
    If arrPtr > UBound(arr) Then
      ReDim Preserve arr(UBound(arr) + 1000)
    End If
    arr(arrPtr) = iValue
  Next iLoop

For 200k loops it took less than a second; for 400k loops, 6 secs; 600k loops, 14 secs; 800k loops, 24 secs; 1M loops, 38 secs; 2M loops, 156 secs; 3M loops, 347 secs. ReDim takes longer and longer as the program progresses and as the array gets bigger - obviously, because VBA has to make a copy of the entire array every time it ReDims it and as the copy gets bigger, each copy operation takes longer than the previous one. The increase is exponential.

I then tried increasing the size of the array by 1000 each time: this brought the run time for 1M loops down to 9 secs; for 2M loops, 40 secs; for 3M loops, 89 secs. The increase is effectively linear.

Okay, they're big numbers but if you're doing a lot of ReDimming, especially on slower machines, there is a performance downside. And it doesn't take long to rack up big numbers if you're storing data from large workbooks. For small applications I wouldn't worry too much about it but the code optimiser in me is always inclined to keep unnecessary operations down to a minimum.

Lots of info at http://www.google.co.uk/#q=redim+preserve+performance.

 

[DonkeyOte]

Dim dblArray(1 to 1, 1 to 10) As Variant

That said using a Variant I'm not sure you really need to pre-define the dimensions at all... but we don't know how you're populating the Array.

On my part apologies for above - should of course have read () as Variant (or ReDim) ... I put it down to it being Sunday

 

[Ruddles]

On my part apologies for above - should of course have read () as Variant (or ReDim) ... I put it down to it being Sunday

I've just checked and my wife confirms it's Sunday!

 

[xenou]

Okay. Good to know.