How to fix type mismatch VBA error in Excel

In this tutorial we’ll learn why the “type mismatch” error occurs in VBA and how to fix this error through simple modifications in code. 

Reasons for type mismatch Error

This error usually occurs due to the following very simple reason;

• The data type of the variable doesn’t match with the data assigned to it 

Let’s dive into an example scenario and discuss the solutions for this error by following the steps mentioned below.

Variable is an Integer and assigned data is a String

Let’s look at the code in this example above. It simply defines a variable named my_number as an integer. However, while assigning the value to it the code mistakenly assigns a String value to the variable. Therefore, the error occurs.

Let’s fix this error by making appropriate amendments in the code.

Microsoft Office has provided programmers the liberty of automating their tasks through Visual Basic for Office which is the official programming language for Microsoft Office Applications, i.e., Excel, Word, PowerPoint, Access etc. While writing programs in VBA we can encounter many errors and one of the very common errors is “type mismatch” error.

Step 1 – Change the type of data in the code to remove error

– As mentioned earlier the root cause of the error was the wrong data type. Now we’ll rectify the error by changing the value of data assigned to the variable my_number. We’ll assign an integer value to it and run the code again to see that the error has been resolved successfully.