Assigning to arrays

Question

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

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

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"

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

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?

Just to add to the below answers, you can only assign one array to another directly if the destination array is dynamic and of the same type as the source, which is why 3 works without Redim and 2 doesn't work at all. — Rory
– Rory, Commented Jul 8, 2022 at 9:11

Pᴇʜ · Accepted Answer · 2022-07-08 08:48:19Z

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:

Dim Pieces() As Variant here the variable is defined as an array out of Variants
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.

FaneDuru · Accepted Answer · 2022-07-08 08:59:30Z

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

Collectives™ on Stack Overflow

Assigning to arrays

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related