Excel VBA Strings: Common Errors When Using Strings

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:

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”:

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

One thought on “Excel VBA Strings: Common Errors When Using Strings”

  1. Ateev Prakash says:

    Given examples are very useful thanks very much but I have an other quairy – I have created a timer in excel, it is working fine but when I open another excel file read only then it gives type mismatch error , and if I open writeable file then timer shows on that file so please give me solution for this problem, it should be work on only that macro file where timer is created other file should not be effected.

Leave a Reply

Your email address will not be published. Required fields are marked *