question

LaiKanLeon-4557 avatar image
0 Votes"
LaiKanLeon-4557 asked LaiKanLeon-4557 commented

Instead of using dynamic array (with numerous ReDim Preserve), can we use Static Array?

Hello,

I got the following code from the forum (and it's working perfectly)


 Dim array1 As Variant
  array1 = Array("... cats ...", " ...Dogs ...", " ...Cats and dogs ...", "another text")
        
  Dim array2 As Variant
  array2 = Array()
    
  Dim t
  For Each t In array1
      If InStr(1, t, "cats", vbTextCompare) > 0 Or InStr(1, t, "dogs", vbTextCompare) > 0 
  Then
          ReDim Preserve array2(UBound(array2) + 1)
          array2(UBound(array2)) = t
      End If
  Next


The only problem is that as my data increases, the VBA becomes very slow. Due to numerous ReDim Preserve.

I want to try something:
If we don't define array2 as a dynamic array, we can define it as a static array like this:

Dim Array2(0 To 1000000)

This will avoid the need to ReDim Preserve array2 at each loop.

Can anybody help me modify the above code so that array2 is populated correctly?

Suppose only 200,000 rows are copied to array2.
How can I delete the 800,000 unnecessary blank rows in array2?

Thanks
Leon











office-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Added

iIt seems that we can also use a Collection.
If this is better, how can we copy from Array1 to Collection and then to Array2?

Leon

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered LaiKanLeon-4557 commented


Check this code:

 Dim array1 As Variant
 array1 = Array("... cats ...", " ...Dogs ...", " ...Cats and dogs ...", "another text")
    
 Dim array2 As Variant
 array2 = Array()
 ReDim array2(LBound(array1) To UBound(array1))
    
 Dim i As Integer: i = LBound(array2) - 1
    
 Dim t
 For Each t In array1
     If InStr(1, t, "cats", vbTextCompare) > 0 Or _
        InStr(1, t, "dogs", vbTextCompare) > 0 Then
         i = i + 1
         array2(i) = t
     End If
 Next
    
 If i >= LBound(array2) Then
     ReDim Preserve array2(LBound(array2) To i)
 Else
     array2 = Array()
 End If

Wait for more, maybe better solutions.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, Sagar

I just tried your code. It works fine and is very fast - much faster than before.

I filtered 1,000,000 rows in array1 and it returned 300,000 rows in array2 - in just 3 seconds.

If I filter only 100,000 rows in array1, it takes <1s.

I won't ever use so many rows. So, your VBA is fast enough for me.

I just needed to dim i as Long instead of Integer.

Thanks a lot.
Closing
Leon

0 Votes 0 ·
LaiKanLeon-4557 avatar image
0 Votes"
LaiKanLeon-4557 answered Viorel-1 commented

Hi Sagar Singh

Thanks for replying.

I did not have time to test your code in detail as our Office is closing in 1/2 hour. Will do it tomorrow.

I just had time to examine your logic so that I can spend my night reflecting on your new solution.

  • Could you just please enlighten me on the foll line:


    If i >= LBound(array2) Then
    ReDim Preserve array2(LBound(array2) To i)

It seems to me that your ReDim Preserve is DECREASING the size of the array - i.e. kind of deleting blank rows?

Is this OK? I always thought that we could only INCREASE the size of an array using ReDim Preserve.

If we could decrease the size of an array, that would be great! We could oversize array2 at first. Then at the end, we remove the unused rows.

This will avoid having to do tens of thousands of ReDim Preserve. This is what takes nearly 99% of processing time!

Best Regards,
Leon












· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Yes, ReDim and ReDim Preserve can used to reduce the size.


0 Votes 0 ·