Type mismatch on a "Split" statement

[GraeagleBill]

I can't figure out what the issue is with a "type mismatch" with the "Split" statement below.



Dim result as variant ' result will be assigned a cvs string from an API call
Dim ResultArr() as variant

ResultsArr = Split(result, ",")

In debug, result equals the string: "12/5/14","Alerian MLP ETF","AMLP",17.97,1.13,6.23,4514800

In the desired results of the split would be that some of the array elements would be quoted strings while others would be numbers.

[June7]

I suspect the issue is with the array declaration. For one thing, you declare ResultArr but use ResultsArr. Always include Option Explicit line in the module header and errors like that can be revealed.

This works for me:

Sub test()
Dim ResultsArr As Variant, sText As String, i As Integer
Open "C:\test.txt" For Input As #1
Do Until EOF(1)
Input #1, sText
Debug.Print sText
ResultsArr = Split(sText, ",")
For i = 1 To UBound(ResultsArr)
Debug.Print ResultsArr(i)
Next
Loop
Close #1
End Sub

[GraeagleBill]

I typed in the code statements in this post rather than a copy/paste as I usually do, so the "s" issue you raised was a typo on my part. (The module does include Options Explicit)

When I drop the parenthesis off the Dim of the array variable my code works as desired. I have no idea why Access treats things differently when the array declaration is coded ResultsArr(). Try adding the parenthesis to your test case to see if it fails.

Dim ResultsArr As Variant

ResultsArr = Split(result, ",")