Dynamically Populate VBA Array

问题:

In the example syntax below, how can I add each element to a new array instead of printing? I am unsure how to do such as I would not know the size of the array, since it could be 0 (no need to ever initialize the array) or it could be the Ubound of the array it is iterating.

Option Compare Database
Option Explicit

Function TestIt()
Dim animalarray As Variant, xyz As Variant
animalarray = Array("Cat", "Cow", "Camel", "Dire Wolf", "Dog", "Coyote", "Rabbit", "Road runner", "Cougar")

For Each xyz In animalarray
  If Left(CStr(xyz), 1) = "C" Then
    Debug.Print CStr(xyz)
  End If
Next

End Function

回答1:

You can use Redim Preserve:

Sub TestIt()
    Dim animalArray(): animalArray = Array("Cat", "Cow", "Camel", "Dire Wolf", "Dog", "Coyote", "Rabbit", "Road runner", "Cougar")
    Dim anotherArray(): anotherArray = Array("Lion", "Tiger", "Leopard")

    Dim xyz As Variant
    For Each xyz In animalArray
      If Left(CStr(xyz), 1) = "C" Then
        ReDim Preserve anotherArray(UBound(anotherArray) + 1)
        anotherArray(UBound(anotherArray)) = xyz
      End If
    Next

    For Each xyz In anotherArray
        Debug.Print xyz
    Next
End Sub

回答2:

It's even simpler:

Function TestIt()

    Dim animalarray As Variant
    Dim newarray As Variant
    Dim xyz As Variant

    animalarray = Array("Cat", "Cow", "Camel", "Dire Wolf", "Dog", "Coyote", "Rabbit", "Road runner", "Cougar")
    newarray = animalarray

    For Each xyz In newarray
        If Left(CStr(xyz), 1) = "C" Then
            Debug.Print CStr(xyz)
        End If
    Next

End Function