Dynamically Populate VBA Array

Question

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

@A.S.H - no I want to add to a new array. Sorry for not making that clear — Michael Mormon
– Michael Mormon, Commented Jan 5, 2017 at 13:05

A.S.H · Accepted Answer · 2017-01-05 13:13:42Z

2

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

answered Jan 5, 2017 at 13:13

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Mormon Over a year ago

With this for my syntax to work as needed, I had to add an extra If statement to the For Each block with anotherArray since there were elements added to the array that did not fit the "C" criteria. So in order to remove those from being iterated in the For Each block, I added a second If Left(...) statement to eliminate processing them.

A.S.H Over a year ago

@MichaelMormon You can easily start the code with an empty anotherArray: just Dim anotherArray(). The way I understood your question is you wanted a general mechanism to add elements dynamically to an array. I thought that the Debug.Print statement was just for illustration.. If the final goal was really to print those names starting with "C", there's no need for another array at all.

A.S.H Over a year ago

I mean Dim anotherArray(): anotherArray = Array()

Gustav · Accepted Answer · 2017-01-05 13:19:55Z

1

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

answered Jan 5, 2017 at 13:19

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

1 Comment

Michael Mormon Over a year ago

This is super simple, thank you for this! I never can get the "ReDim Preserve" syntax exactly right which is what messes up my syntax every time. In this case, there is no need for it!

Collectives™ on Stack Overflow

Dynamically Populate VBA Array

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related