VBA type mismatch in split function

Question

I have written a code where I need to get the number from a string like this: "3/1". I need to store those 2 numbers as integer in 2 different variables. I have written this code in 2 classes: This function is my split function in the class (BigOne)

Public Function SplitValues(pInput As String, pdelim As String) As String()
'Declaration of variables
Dim strSplit() As String
Dim countDelim As Integer

'Initialization of variables

countDelim = countCharacter(pInput, pdelim)
If countDelim > 0 Then
    ReDim strSplit(countDelim)
    strSplit = Split(pInput, pdelim)
    SplitValues = strSplit
End If
End Function

In the main class I have a function calling to this function that splits the number to get the values that I want. However I am getting a "Type Mismatch error" I am not able to detect the reason of this type mismatch.

Public Function get_MaxChars(pInput As String) As Integer
'declaration of variables
    Dim gen As cBigOne
    Dim values As String

   'Main code
    pInput = CStr(pInput)
    Debug.Print (pInput)
    values = gen.SplitValues(pInput, "/")
    get_MaxChars = CInt(values(0))

End Function

So, I am not able to see why it is not working correctly and I am getting the type mismatch error. Because, I believe that everywhere I am passing the same type.

Oriana

what is pInput at error? – Ictus

pInput at error is "1/3" or a similar string. – Oriana

Answer 1

SplitValues returns a String array, and you are trying to assign it to a String. Try dimming values as String() instead.

You'll still have an issue when calling SplitValuesas you haven't created an instance of your class, just said that gen will be one. After Dim gen As cBigOne, you should have Set gen As New cBigOne

Contributory

Answer 2

I had the same problem. Then I found that you have to declare the array as VARIANT

This is my code, where I save an attachment for every mail that arrives in Outlook to a specified folder

        Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
           Dim objOL As Outlook.Application

            Dim arr As Variant
            Dim i As Integer
            Dim ns As Outlook.NameSpace
            Dim itm As MailItem
            Dim m As Outlook.MailItem
            Dim j As Long
        Dim lngCount As Long
        Dim strFile As String
        Dim strFolderpath As String
        Dim strDeletedFiles As String
        Dim fs As FileSystemObject
        Dim mldat As Date
        Dim strsndr As String

            'On Error Resume Next
            Set ns = Application.Session
            arr = Split(EntryIDCollection, ",")



        '*******************************************************************************************

        ' Set the Attachment folder.
        strFolderpath = "z:\dropbox (AAA-DZ)\25_Attach\"

        ' Check each selected item for attachments. If attachments exist,
        ' save them to the strFolderPath folder and strip them from the item.
        For ij = 0 To UBound(arr)
        Set itm = ns.GetItemFromID(arr(ij))
                If itm.Class = olMail Then
                                With itm

                                ' This code only strips attachments from mail items.
                                ' If objMsg.class=olMail Then
                                ' Get the Attachments collection of the item.
                                Set objAttachments = .Attachments
                                lngCount = objAttachments.Count
                                strDeletedFiles = ""

                                If lngCount > 0 Then

                                ' We need to use a count down loop for removing items
                                ' from a collection. Otherwise, the loop counter gets
                                ' confused and only every other item is removed.
                                Set fs = New FileSystemObject

                                For i = lngCount To 1 Step -1

                                    ' Save attachment before deleting from item.
                                    ' Get the file name.


                                    strFile = Right("0000" + Trim(Str$(Year(.SentOn))), 4) + "_" + Right("00" + Trim(Str$(Month(.SentOn))), 2) + "_" + Right("00" + Trim(Str$(Day(.SentOn))), 2) + "_" + Right("00" + Trim(Str$(Hour(.SentOn))), 2) + "_" + Right("00" + Trim(Str$(Minute(.SentOn))), 2) + "_" + Right("00" + Trim(Str$(Second(.SentOn))), 2) + "_" + .SenderEmailAddress + "_" + .Parent + "_" + objAttachments.Item(i).FileName

                                    ' Combine with the path to the Temp folder.
                                    strFile = strFolderpath & strFile

                                    ' Save the attachment as a file.
                                          If Left(objAttachments.Item(i).FileName, 5) <> "image" Then
                                          objAttachments.Item(i).SaveAsFile strFile

                                            ' Delete the attachment.
                                            objAttachments.Item(i).Delete

                                            'write the save as path to a string to add to the message
                                            'check for html and use html tags in link
                                            If .BodyFormat <> olFormatHTML Then
                                                strDeletedFiles = strDeletedFiles & vbCrLf & "<file://" & strFile & ">"
                                                Else
                                                strDeletedFiles = strDeletedFiles & "<br>" & "<a href='file://" & _
                                                strFile & "'>" & strFile & "</a>"
                                            End If
                                        Else
                                        objAttachments.Item(i).Delete

                                        End If


                                Next i

                                      ' Adds the filename string to the message body and save it
                                      ' Check for HTML body
                                      If Len(strDeletedFiles) > 5 Then
                                              If .BodyFormat <> olFormatHTML Then
                                                  .Body = vbCrLf & "The file(s) were saved to " & strDeletedFiles & vbCrLf & .Body
                                              Else
                                                  .HTMLBody = "<p>" & "The file(s) were saved to " & strDeletedFiles & "</p>" & .HTMLBody
                                              End If

                                          .Save
                                      End If
                                  Else
                                  objAttachments.Item(1).Delete

                                  End If


                        End With

                End If
        Next ij

    ExitSub:

        Set objAttachments = Nothing
        Set objSelection.Item(ij) = Nothing
        Set objSelection = Nothing
        Set objOL = Nothing


        '********************************************************************************************


            Set ns = Nothing
            Set itm = Nothing
            Set m = Nothing
        End Sub

Skateboard

Answer 3

"Type Mismatch" Error 13 When we split as per below data type we can get this error,

**Public Sub Array_Split()

This data_Dim X() As Variant_type would be change to string that mean if we are entring wrong data type we will get Type Mismatch Error

Dim X() As Variant

Dim VALU As Variant

VALU = "Raj,Kumar"__ also we can select cell refrence like range("A2") or cells(1,1)

X = VBA.split(VALU, ",")

MsgBox X(0) & vbNewLine & X(1)

End Sub

Kyle

Answer 4

I´ve been having the same issue until I idicated Split the part of the string to be stored in the variable. With (0) right after the split. Example

Variable=Split("Your Text")(0)

Or

Variable=Split("Your Text", " ")(0)

Same result for both cases: Variable="Your"

Coneflower