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

Question

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?

AFAIK this is anomalous behaviour of certain built in functions. You can't replicate it without using one of those functions. This is discussed on CPearsom here — chris neilsen
– chris neilsen, Commented Oct 20, 2019 at 10:37

JvdV · Accepted Answer · 2019-10-20 12:41:28Z

2

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

edited Oct 20, 2019 at 12:41

answered Oct 20, 2019 at 9:16

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

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

8 Comments

im_chc Over a year ago

You can test the vba program, the loop hasn't started, indicated that the array is empty

chris neilsen Over a year ago

@jvdv the OP is entirely correct. If you single step their code and look at spl in a Watch, you'll see it's Dim'd 0 To -1

im_chc Over a year ago

@jvdv if you comment out the Split() line, the array would be unallocated, and the for-loop line will produce the "for loop not initialized" error 92

ProfoundlyOblivious Over a year ago

"I'll be curious to know if you can have an allocated empty array." "An array is said to be allocated if it consumes memory, has valid lower and upper bounds, and contains data (even if that data is the default values for the data type of the array, such as empty strings of an array of String type variables). A static array is by definition always allocated." cpearson.com/excel/IsArrayAllocated.aspx

JvdV Over a year ago

@im_chc, depending on where you look, the recommendation between an ArrayList or Collection can differ. Personally I like ArrayList. I'm glad it's sorted for you =)

|

Paco Verschut · Accepted Answer · 2022-07-27 00:07:01Z

0

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

edited Jul 27, 2022 at 0:07

answered Jul 27, 2022 at 0:06

Paco Verschut

113 bronze badges

Comments

awi2b · Accepted Answer · 2023-08-24 12:03:08Z

0

Create the array with an empty initialisation list.

Dim arrstr As String() = {}
'arrstr is a String array with 0 elements.
Dim arrNothing As String()
'arrNothing is Nothing, so calling arrNothing.Count or for-looping over it throws an error.

Println("arrstr Is Nothing = " & (arrstr Is Nothing))
'arrstr Is Nothing = False
Println("arrstr.Count = " & arrstr.Count)
'arrstr.Count = 0
For Each strI As String In arrstr
     mod13_debugout.Println("arrstr(i) = " & strI)
Next
'prints nothing.

answered Aug 24, 2023 at 12:03

awi2b

571 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related