Runtime Error 13: Type-Mismatch

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
358
Office Version
  1. 2003 or older
Platform
  1. Windows
Hi,

I have a code from Stackexchange, which is giving a runtime error 13 with 2 of the 3 supporting examples. And, I have not been able to decode why. Sub Example 3 executes but Sub Example 1 and Sub Example 2 fail.

Need help in getting it rectified.

Code:
Function MultiSplitX(ByVal SourceText As String, RemoveBlankItems As Boolean, ParamArray Delimiters()) As String()
    Dim a As Integer, b As Integer, n As Integer
    Dim i As Integer: i = 251
    Dim u As Variant, v As Variant
    Dim tempArr() As String, finalArr() As String, fDelimiters() 'As String


    If InStr(TypeName(Delimiters(0)), "()") <> 0 And LBound(Delimiters) = UBound(Delimiters) Then
        ReDim fDelimiters(LBound(Delimiters(0)) To UBound(Delimiters(0))) 'If passing array vs array items then
        For a = LBound(Delimiters(0)) To UBound(Delimiters(0))            'build that array
            fDelimiters(a) = Delimiters(0)(a)
        Next a
    Else
        fDelimiters = Delimiters(0)
    End If


    Do While InStr(SourceText, Chr(i)) <> 0 And i < 251 'Find an unused character
        i = i + 1
    Loop
    If i = 251 Then 'If no unused character in SourceText, use single character delimiter from supplied
        For a = LBound(fDelimiters) To UBound(fDelimiters)
            If Len(fDelimiters(a)) = 1 Then i = Asc(fDelimiters(a))
        Next a
    End If
    If i = 251 Then 'If no single character delimiters can be used, error.
        MsgBox "SourceText uses all character type." & vbCrLf & "Cannot split SourceText into an array.", _
            vbCritical, "MultiSplitX Run-Time Error"
        Exit Function
    End If
    Debug.Print i




    For a = LBound(fDelimiters) To UBound(fDelimiters) 'Sort Delimiters by length
        For b = a + 1 To UBound(fDelimiters)
            If Len(fDelimiters(a)) < Len(fDelimiters(b)) Then
                u = fDelimiters(b)
                fDelimiters(b) = fDelimiters(a)
                fDelimiters(a) = u
            End If
        Next b
    Next a


    For Each v In fDelimiters 'Replace Delimiters with a common character
        SourceText = Replace(SourceText, v, Chr(i))
    Next


    tempArr() = Split(SourceText, Chr(i)) 'Remove empty array items
    If RemoveBlankItems = True Then
        ReDim finalArr(LBound(tempArr) To UBound(tempArr))
        n = LBound(tempArr)
        For i = LBound(tempArr) To UBound(tempArr)
            If tempArr(i) <> "" Then
                finalArr(n) = tempArr(i)
                n = n + 1
            End If
        Next i
        n = n - 1
        ReDim Preserve finalArr(LBound(tempArr) To n)


        MultiSplitX = finalArr
    Else: MultiSplitX = tempArr
    End If
End Function
Sub Example1()
    Dim myString As String, c, n


    n = 0
    myString = "The,Quickupside-downBrownjelloFox_Jumped[Over]             ThegiantLazyjelloDog"


    For Each c In MultiSplitX(myString, True, ",", "-", "upside-down", "jello", " ", "[", "]", "giant", "_")
        Debug.Print "(" & n & ") = " & c
        n = n + 1
    Next c
End Sub
Sub Example2()
    Dim myString As String, c, n


    n = 0
    myString = "The,Quickupside-downBrownjelloFox_Jumped[Over]             ThegiantLazyjelloDog"


    For Each c In MultiSplitX(myString, True, ",", "-", "upside-down", "jello", " ", "[", "]", "giant", "_")
        Debug.Print "(" & n & ") = " & c
        n = n + 1
    Next c
    Debug.Print myString
End Sub
Sub Example3()
    Dim myString As String, c, n
    Dim myDelimiters As String


    n = 0
    myString = "The,Quickupside-downBrownjelloFox_Jumped[Over]             ThegiantLazyjelloDog"
    myDelimiters = ",|-|upside-down|jello| |[|]|giant|_"


    For Each c In MultiSplitX(myString, True, Split(myDelimiters, "|"))
        Debug.Print "(" & n & ") = " & c
        n = n + 1
    Next c
    Debug.Print myString
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You've declared fDelimiters as an array, but in examples 1 and 2, Delimiters(0) is not an array, it's a string, so this will fail with a type mismatch:

Code:
fDelimiters = Delimiters(0)
 
Upvote 0
The function is expecting an array for the last argument, not a load of strings, try
Code:
    For Each c In MultiSplitX(myString, True, Array(",", "-", "upside-down", "jello", " ", "[", "]", "giant", "_"))
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top