3

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?

im_chc
  • 1,023
  • 15
  • 24
  • 2
    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](http://www.cpearson.com/excel/IsArrayAllocated.aspx) – chris neilsen Oct 20 '19 at 10:37

3 Answers3

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
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    You can test the vba program, the loop hasn't started, indicated that the array is empty – im_chc Oct 20 '19 at 09:52
  • 1
    @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` – chris neilsen Oct 20 '19 at 10:15
  • @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 – im_chc Oct 20 '19 at 10:16
  • 1
    **"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."* http://www.cpearson.com/excel/IsArrayAllocated.aspx – ProfoundlyOblivious Oct 20 '19 at 10:19
  • @im_chc, i understand but I thought you wanted to replicate the Split behaviour through some `spl(? To ?)` syntax. At ChrisNeilsen, I thought this was meant as allocated. But I couldn't replicate an empty array using such syntax. And the link provided by Profoundlyoblivious indicates that indeed you can't have an empty array using any such syntax. Does it make sense to keep the answer in place as an alternative? – JvdV Oct 20 '19 at 11:11
  • I can use scripting runtime's collection object instead, it's more "native" than using the .NET class lib... but nevertheless your answer's creativity is worthy of an upvote :) – im_chc Oct 20 '19 at 13:51
  • 1
    @im_chc, depending on [where](https://excelmacromastery.com/vba-arraylist/) you look, the recommendation between an `ArrayList` or `Collection` can [differ](https://analystcave.com/vba-arraylist-using-vba-arraylist-excel/). Personally I like `ArrayList`. I'm glad it's sorted for you =) – JvdV Oct 20 '19 at 14:09
  • @JvdV yeah tbh I've never thought of using .NET classes in a VBA project, so props be to you – im_chc Oct 20 '19 at 15:35
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
Paco Verschut
  • 11
  • 3
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.
Johann Wiengold
  • 45
  • 6