[Solved]: User Defined Type Not Defined in Excel VBA (2 Quick Solutions)

One of the most common errors that we face while working with VBA in Excel is that the user-defined type is not defined. In this article, I’ll show you why this error is caused and how to solve this error.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Causes and Solutions to User Defined Type Not Defined in Excel VBA

There are 2 major issues behind this frequently encountered error in Excel VBA. Let’s explore them in detail and find out the ways to solve them.


1. Spelling Error While Declaring Variables

This is the major reason behind this error. Most of the time, the error “User-Defined Type not Defined” is caused due to a spelling error while declaring variables.

Look at the following lines of codes.

Sub User_Defined_Type_not_Defined()
Dim Name As Strng
Name = "ExcelDemy"
MsgBox Name
End Sub

VBA Code with the Error User Defined Type Not Defined in Excel VBA

If you run this code, you’ll get an error box notifying you of the presence of the error “User-Defined Type Not Defined”. Along with it, you’ll find the second line of the code highlighted in blue, where I wrote “strng” in place of “string”.

User Defined Type Not Defined Error in Excel VBA

I hope you get the point. VBA could not recognize the variable type “strng”, that’s why it considered it as a new variable type and thought you have defined it somewhere. But when it found you have not declared it anywhere, it raised an error.

Therefore, while running simple VBA codes, if you encounter this error anyhow, first check all the spellings of your variable types.


2. Lack of Proper Referencing

This is another cause behind this error. That is a lack of proper referencing. Sometimes we use some objects in our codes that need proper referencing from the Visual Basic Reference box. Otherwise, VBA doesn’t recognize the object.

Look at the following VBA code.

Sub User_Defined_Type_not_Defined()
Dim MyDictionary As Dictionary
Set MyDictionary = CreateObject("Scripting.Dictionary")
End Sub

Run this code. You’ll get the same old error “User-Defined Type not Defined”, highlighting the Dictionary object. This is because VBA couldn’t recognize the Dictionary object, and considered it as a user-defined variable, which has not been defined mistakenly.

User Defined Type Not Defined Error in Excel VBA

Now the biggest question is, how to solve this error? Easy. There are 2 ways to solve this error.

⧪ Solution 1: Checking the Prerequisite Reference from the Visual Box Reference Box

This is the smartest way to solve this problem. Go to the Tools > References button in the Visual Basic ribbon. Click on References.

Checking Reference to Solve User Defined Type Not Defined Error in Excel VBA

A dialogue box called References will open. Scroll and find Microsoft Scripting Runtime (This depends on the object that you are using. The Dictionary object needs Microsoft Scripting Runtime to be recognized. So, while using an object in a VBA code, you must know its prerequisite to be recognized by VBA. Visit this link for more information.). Check it.

Now come back and run the code. You’ll not get any errors. Because VBA can recognize the Dictionary object this time.

⧪ Solution 2: Declaring It as a Pure Object rather than a Specific Object

There is another way to solve this problem. You can declare the object as a pure object, rather than a Dictionary object.

Notice the following lines of codes.

Sub User_Defined_Type_not_Defined()
Dim MyDictionary As Object
Set MyDictionary = CreateObject("Scripting.Dictionary")
End Sub

Here I’ve declared the variable MyDictionary as a simple object, not a Dictionary. Now if you run this code, you won’t get any error.


Conclusion

Therefore, these are the main causes and solutions behind the error “User-Defined Type not Defined” in Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo