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

Question

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

Answer 1

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.

Answer 2

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