How to create an allocated but zero-length array that are for-loopable?

user_214728 0
Viewed 374

I want to create a VBA array that has zero elements but is for-loopable.
You can look at the code below to see what I mean: 

Sub testloopemptyarr()    
    Dim spl() As String, v As Variant, count As Integer
    spl = Split(Empty, ",")    'lbound=0, ubound=-1, zero elements
    'ReDim spl(-1 To -1)        ' one element
    'ReDim spl(-1)              ' does not compile: subscript out of range
    'ReDim spl(-1 To 0)         ' two elements
    'ReDim spl(0 To -1)         ' does not compile: subscript out of range

    For Each v In spl
        count = count + 1
    Next
    MsgBox count
End Sub

The msgbox will pop 0 in this case, because splitting an empty string will return a zero element array. No errors thrown when the for-loop is encountered, implying that the array is an allocated array.

If you test it, you can find out that, after Split() is called, lbound(spl) is 0, ubound(spl) is -1 But it is illegal to do ReDim spl(0 To -1) (try uncomment the line and run)

So my question is:

How do I create an array that has the same behavior with the one produced by the Split() function?

vba excel arrays
2 Answers

I'll be curious to know if you can have an allocated empty array. While I don't think it's possible (the whole point IMO of arrays is that you have elements in them, at least 1) other than the way you retrieved your array using Split.

You may be interested in an alternative to an array as you could use an ArrayList object. ArrayList will allow you to still add an item to the "array" per index number as an allocated array would.

Sub EmptyArray()

Dim arr As Object: Set arr = CreateObject("System.Collections.ArrayList")
Dim item As Variant

Debug.Print arr.Count 'Will show 0

For Each item In arr 'Will skip iteration
    Debug.Print item
Next item

arr.Insert 0, "1st item"
arr.Insert 1, "2nd item"
arr.Insert 2, "3rd item"

Debug.Print arr.Count 'Will show 3

For Each item In arr 'Will now iterate
    Debug.Print item
Next item

End Sub
user_97581940

Instead of using Split you can also use IsArray with a dynamic array of any type.

Dim exArr() As Date  'can be any type
IsArray exArr
Debug.Print UBound(exArr) - LBound(exArr) + 1
' prints 0 without error
user_196287450
Related