Debug Type Mismatch Error in VBA Excel – debugvba.com

Objective

To debug the “Type Mismatch Error”.

Cause of Error

This error is raised when we try to assign a value of one particular data type to a variable of another data type. For example, if we have a variable named as “x” which has data type as integer. If we shall try to assign a string value called as “Test” to variable “x”, then it will throw the error.

There can be many scenarios where this error can occur, some of which are listed below.

Assigning value of different data type

In the below code we are trying to assign a string data type value (i.e. “SampleString”) to a numeric variable “MyNumber” which is of Long data type. So, due to mismatch of data type this error occurs.

 

Sub TypeMismatchError1()

Dim MyNumber As Long

'Type mismatch error as we tried to assign string in integer data type

MyNumber = "SampleString"

End Sub

 

Assigning string value to a numeric array

In the code below we have assigned a string value (i.e. “Wrong_Sample_Value”) to numeric array.

 

Sub TypeMismatchError2()

Dim MyArray() As Integer

'Type mismatch error as we tried to store string in integer type array

ReDim MyArray(10)

MyArray(1) = "Wrong_Sample_Value"

End Sub

 

 

Adding variable of different data type

Here we are adding 2 variables, out of which one is of numeric data type (i.e. var1) , while the other variable (var2) is of “String” data type.

 

Sub TypeMismatchError3()

Dim var1 As Double

Dim var2 As String

var1 = 2

varSum = var1 + var2

End Sub

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/type-mismatch-error-13

Post you may like