WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Type Mismatch Error

VBA Type Mismatch Error

Type mismatch Error or we can also call it as Error code 13 it occurs when we assign a value to a variable which is not of its data type, for example, if we provide a decimal or long value to an integer data type variable we will encounter this Type mismatch error when we run the code which is shown as Error Code 13.

What is VBA Type Mismatch Error?

VBA Type Mismatch Error in excel is a type of “Run Time Error,” and it is the number 13 error in this category.

To start off the learnings in VBA and for beginners, it is hard to find the error thrown by the VBA codes. Remember, VBA is not throwing an error. Rather, it is just highlighting our mistakes while writing the code.

We usually declare variables, and we assign data types to it. When we assign a value to those variables, we need to remember what kind of data it can hold. If the assigned value is not as per the data type, we will get “Run Time Error 13: Type Mismatch”.

vba type mismatch

How to Fix VBA Type Mismatch Run-time Error 13?

Let’s see some examples to understand this VBA Type Mismatch Error.

You can download this VBA Type Mismatch Excel Template here – VBA Type Mismatch Excel Template

VBA Type Mismatch – Example #1

For example, look at the below VBA code.

Code:

Sub Type_MisMatch_Example1()

Dim k As Byte

k = "Hiii"

MsgBox k

End Sub

vba mismatch example 1.1

I have declared the variable “k” as Byte.

This means the variable “k” can hold values from 0 to 255. But in the next line, I have assigned the value for the variable “k” as “Hiii.”

It is very clear that data type cannot hold the value of a text, so here comes the Type Mismatch Error.

vba mismatch example 1.2

VBA Type Mismatch – Example #2

Now take a look at one more example with a different data type. Look at the below code.

Code:

Sub Type_MisMatch_Example2()

Dim x As Boolean

x = 4556

MsgBox x

End Sub

vba mismatch example 2.1

We have declared the variable “x” as Boolean.

Boolean is a data type that can hold the value of either TRUE or FALSE.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

In the above code, we have assigned a value of 4556, which is not as per the data type values of TRUE or FALSE.

When we run this code, you would expect a type mismatch error, but see what happens when we run this code.

vba mismatch example 2.2

You must be wondering why this isn’t give run time error 13 of type mismatch error.

The reason for this is excel treats all the numbers as TRUE except zero. Zero value will be treated as FALSE. So that is why we got the result as TRUE instead of an error.

Now see, I will assign a numerical value with text.

Code:

Sub Type_MisMatch_Example2()

Dim x As Boolean

x = "4556a"

MsgBox x

End Sub

vba mismatch example 2.3

This will definitely throw Run Time Error 13: Type Mismatch.

vba mismatch example 2.4

VBA Type Mismatch – Example #3

Now, look at the below code for this example.

Code:

Sub Type_MisMatch_Example4()

Dim x As Integer
Dim y As String

x = 45
y = "2019 Jan"

MsgBox x + y

End Sub

example 3.1

Variable “x” is an integer data type, and “y” is a string data type.

X = 45 and y = 2019 Jan.

In the message box, I have added x + y.

But this isn’t the perfect code because we cannot add numbers with string texts. We will encounter Run Time Error 13.

example 3.2

VBA Type Mismatch – Example #4

Exceptional Cases

There are situations where excel forgives the wrong data assigned to the variable data type. For example, look at the below code.

Code:

Sub Type_MisMatch_Example3()

Dim x As Long
Dim y As Long

x = 58.85

y = "85"

MsgBox x & vbNewLine & y

End Sub

example 4.1

Two declared variables are “x” & “y.”

For this variable, the assigned data type is “Long.”

Long data type accepts only whole numbers, not decimal values.

So general perception is to get run time error 13 of type mismatch error.

But let’s see what happens when we run this code.

example 4.2

Wow!!! We got the values as 59 & 85.

This is because VBA will convert the decimal value 58.85 to the nearest integer value, and even though numbers are enclosed with double quotes still it converts to the integer value only.

Recommended Articles

This has been a guide to VBA Type Mismatch. Here we discussed VBA Type Mismatch Run-Time error 13 in VBA with examples and a downloadable excel template. Below are some useful articles related to VBA –

  • Data Type in Excel VBA
  • What is Type Statement in Excel VBA?
  • What is the Integer Data Type?
  • VBA Select Case
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Type Mismatch Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More