Results 1 to 3 of 3

Thread: Runtime Error 13: Type-Mismatch

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Runtime Error 13: Type-Mismatch

    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

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,970
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime Error 13: Type-Mismatch

    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)

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Runtime Error 13: Type-Mismatch

    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", "_"))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •