Type mismatch (Error 13) when assigning Split Variant/String to Variant/Variant() Array-CodePudding

Why do I get a Type Mismatch error on the last line?

Dim LineArray()
Dim Line
Dim FilePath as String
FilePath = "X:\Path\To\File.txt"

Open FilePath For Input As #1
    Line Input #1, Line
Close #1

LineArray = Split(Line, vbTab)

LineArray is a Variant/Variant()

Line is a Variant/String

If I Dim them both as Variants I still get the error

CodePudding user response：

As written in the comments: Split returns an array of Strings.

So the correct syntax is

Dim LineArray() As String
LineArray = Split(Line, vbTab)

The VBA compiler knows that LineArray will be an array of Strings. However, as it is declared as dynamic array, it is not an array before you assign members to it. Usually, this is done using the ReDim-statement that reserves the necessary space for a certain number of variables of a specific type. When using the split-function, this ReDim (or something similar) is done inside the function.

Now Variant is a very special data type - it can hold any type of data. This can be an Integer, a String, a Date, an Object (reference), an Error, or the value Empty (which means nothing is assigned yet). And, it can even be an array of any datatype. You can get the information about what data it contains using the function VarType().

What you can`t do is assign the result to an static array:

Dim LineArray(0 to 3) As String
LineArray = Split(Line, vbTab)

This will throw a compile error: Can't assign to array. The variable is not only defined, the memory is already reserved (in this case 4 Strings).

What you also can't do is to assign the result to an array of Variant. Why? Because the Split creates an Array of Strings. Now if you would assign this to an array of Variant, it would be possible to assign, let's say, a Double to the first element. However, the memory was reserved by Split to hold an String, so horrible things would happen to the memory.

Dim LineArray(0) As Variant
LineArray = Split(Line, vbTab)
LineArray(0) = 3.1415926      ' Ouch!

What would be needed is an instance that

Reserve memory for n Variant variables and assign it to LineArray (n = number of strings returns by Split)
Loop over all elements of the string array and assign the strings one by one to the variant array. Now while this is possible, no instance will do this for you. The Split-function creates and returns the String array. The assignment operator ("=") is capable to assign any value to a Variant, but is not capable to reserve an array and copy elements one by one.