I'm having trouble understanding when brackets are/aren't required when declaring arrays of String and Variant types.

1. This works as expected (declaring Pieces WITHOUT brackets, as Variant):

Dim str
str = "The quick fox"
Dim Pieces As Variant
Pieces = Split(str)
MsgBox(Pieces(1)) 

OUTPUT: quick

2. This doesn't work (declaring Pieces WITH brackets, as Variant):

Dim str
str = "The quick fox"
Dim Pieces() As Variant
Pieces = Split(str)
MsgBox(Pieces(1)) 

"Type mismatch"

3. This works (declaring Pieces WITH brackets, as String):

Dim str
str = "The quick fox"
Dim Pieces() As String
Pieces = Split(str)
MsgBox(Pieces(1)) 

OUTPUT: quick

4. This doesn't work (declaring Pieces WITHOUT brackets, as String):

Dim str
str = "The quick fox"
Dim Pieces As String
Pieces = Split(str)
MsgBox(Pieces(1)) 

Won't compile "expected array"

I can understand 4. not working. But I can't understand the reasons for the Variant versions (1. and 2.) not working in the same manner and how a dynamic string array (3.) works without ReDim being used?

CodePudding user response：

According the manual of the Split function it returns a zero-based, one-dimensional array containing a specified number of substrings.

This means the Split() function returns an array of strings which is the same as Dim Pieces() As String. But if you declare Dim Pieces() As Variant this means Pieces is declared to be an array of variants and you cannot put an array of strings into an array of variants.

If you declare Dim Pieces As Variant then the variable Pieces is Variant and Variant can contain any type. So it can also contain an array (or value of any other type or an object).

So the difference here is:

1. Dim Pieces() As Variant here the variable is defined as an array out of Variants
2. Dim Pieces As Variant here the variable is defined to be anything.

Dim Pieces() As String which is the return value type of Split() is not an array of Variants and therfore not compatible with the first one.

Finally the following

Dim Pieces() As String
Pieces = Split(str)

works without ReDim because Dim Pieces() As String defines the variable as an array of any size. Split() returns an array so we can put that into the variable Pieces witout any problem.

CodePudding user response：

Please, look to the comments...

Sub playWithArrays()
Dim str  'Variant = any type of data which VBA can hold!
str = "The quick fox"
  MsgBox (str)      'it return the  str String
  str = Split(str)  'it creates an array of strings (for the same declaration As Variant - Implicite)
    MsgBox (str(1)) 'the second element of the array is returned
    
Dim Pieces() As String  'this declared an Array of String type elements
  Pieces = Split(str)   'Split function returns ONLY AN ARRAY OF STRINGS
                        'for x ="1 2 3 4" it returns an array of "1", "2", "3", "4". All strings...
    MsgBox (Pieces(1))  'the second element of the array is returned
    
Dim Pieces1() As Variant  'this is declared As Array of Variant type elements
 Pieces1 = Split(str)     'Split function returns ONLY AN ARRAY OF STRINGS
                          'VBA could be nice and accept ONLY strings as array elements, but it does not do it until declaring the array TO RECEIVE STRING elements, not Variant...
  MsgBox (Pieces1(1))     
  
Dim Pieces2 As String       'this declaration DOES NOT ACCEPT AN ARRAY. ONLY A STRING...
  Pieces2 = Split(str)      'it would return AN ARRAY (of strings) but its return is not a String...
   MsgBox (Pieces2(1))
End Sub