Initialize a list (or array) of a variable length-CodePudding

I know that we could declare a list of a fixed length in VBA by, eg, Dim result as Variant followed by ReDim result(1 to 10). But I have difficulty in declaring a list of a variable length in VBA.

For instance, given a list arr, I would like to create another list result that removes all the empty and 0. I have written a code as follows:

Dim result As Variant
For i = LBound(arr, 1) To UBound(arr, 1)
    If arr(i) <> "" And arr(i) <> 0 Then
        ReDim Preserve result(1 To UBound(result, 1)   1)
        result(UBound(result, 1)) = arr(i)
    End If
Next i

This code does not run, because the length of result is not well initialised; and ReDim Preserve result(1 To UBound(result, 1) 1) raises an error in the very beginning.

Does anyone know how to fix this code?

PS: If ReDim inside a loop causes a bad performance, could anyone suggest a better way to restructure the code?

CodePudding user response：

How about you find the first value and then do result = Array(arr(I))?

I just checked in the immediate pannel ?UBound(Array()) gives -1, so

Dim result As Variant
result = Array()
For i = LBound(arr, 1) To UBound(arr, 1)
    If arr(i) <> "" And arr(i) <> 0 Then
        ReDim Preserve result(0 To UBound(result, 1)   1)
        result(UBound(result, 1)) = arr(i)
    End If
Next i

should work with a 0-based "result" array or

Dim result As Variant
ReDim result(1 To 1)
For i = LBound(arr, 1) To UBound(arr, 1)
    If arr(i) <> "" And arr(i) <> 0 Then
        ReDim Preserve result(1 To UBound(result, 1)   1)
        result(UBound(result, 1)) = arr(i)
    End If
Next i

CodePudding user response：

You can declare the largest possible size of the array (in this case, It would be UBound(arr, 1) assuming that LBound(arr, 1) is 1) outside of the loop first then resize it again at the end of the loop:

Dim result As Variant
ReDim result(1 to Ubound(arr, 1)) As Variant
Dim resultCount As Long

Dim i As Long
For i = LBound(arr, 1) To UBound(arr, 1)
   If arr(i) <> "" And arr(i) <> 0 Then
      resultCount = resultCount   1
      result(resultCount) = arr(i)
   End if
Next i

ReDim Preserve result(1 to resultCount) As Variant

Note: Above code is untested.