In this article I will explain some of the most common errors people encounter while working with strings. For more information regarding string commands please visit VBA Excel String Processing and Manipulation.

You can download the codes and files related to this article here.

Jump To:

• Type Mismatch (Run-time Error ’13’), When Adding (Concatenating) Strings
• Application-Defined or Object-Defined Error (Run-Time error ‘1004’) When Referencing Ranges Using the String Notation “A1:B1”
• Getting Incorrect Results When Adding (Concatenating) Strings in  a Loop

Type Mismatch (Run-time Error ’13’), When Adding (Concatenating) Strings:

You might be attempting to make the string “Data10” by adding the string “Data” and the number 10, and suddenly a msgbox shows up saying: Type Mismatch (Run-time Error ’13’). The reason is that strings can only be added (Concatenated) with another string. You can’t add a string with another data type.  The following examples will all cause the Type Mismatch (Run-time error ’13’) exception:

Sub Example1()
Dim strTemp As String
Dim intTemp As Integer
intTemp = 10
'Type Mismatch Error
strTemp = "Data" + intTemp
End Sub

Sub Example2()
Dim strTemp As String
'Type Mismatch Error
strTemp = "Data" + 10
End Sub

Sub Example3()
Dim strTemp As String
'Type Mismatch Error
Cells(1, 1) = "Data" + 10
End Sub


In order to overcome this error you must convert all the data types to a string:

Sub Example4()
Dim strTemp As String

'No Errors
Cells(1, 1) = "Data" + Str(10)
End Sub


Application-Defined or Object-Defined Error (Run-Time error ‘1004’) When Referencing Ranges Using the String Notation “A1:B1”:

For a full breakdown of this error, please see this article.

You are trying to loop through a range of cells using the “Ai” notation and you get this error. Below is an example of the code that causes this error:

Sub Example5()
Dim i As Integer

For i = 1 To 10
    Range("A" + Str(i)) = i
Next i
End Sub


Everything looks ok. You’ve converted the integer to a string, you’ve got everything right, so whats causing the error? The problem is with the Str() function. When Str converts an integer to a string it causes a leading space. The leading space will cause your string to become “A 1”, “A 2”, “A 3” … instead of “A1”, “A2”, “A3”. That space between the number and the character will cause the exception. In order to overcome this error you could use the Strings.Trim() function:

Sub Example6()
Dim i As Integer

For i = 1 To 10
    Range("A" + Strings.Trim(Str(i))) = i
Next i
End Sub


The String.Trim() function removes all leading and trailing spaces in a string.

Getting Incorrect Results When Adding (Concatenating) Strings in  a Loop:

Another common mistake is when you are trying to create a string by adding (concatenating) strings in a loop. You might be trying to create the string “Data1Data2Data3Data4Data5” but instead you get “Data5”:

Sub Example7()
Dim strTemp As String
Dim i As Integer

strTemp = ""

For i = 1 To 5
    'will create a new string on each loop
    strTemp = "Data" + Strings.Trim(Str(i))
Next i
Cells(2, 2) = strTemp
End Sub


The problem with the code above is that the instead of adding the string to the previous string on each iteration, a new string is being created each time. By adding strTemp to the left side of the equation the problem will be solved:

Sub Example8()
Dim strTemp As String
Dim i As Integer

strTemp = ""

For i = 1 To 5
    'will add to the previous string on each loop
    strTemp = strTemp + "Data" + Strings.Trim(Str(i))
Next i
Cells(2, 3) = strTemp
End Sub


You can download the codes and files related to this article here.

See Also:
VBA Excel String Processing and Manipulation.
If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com