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.
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
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”.
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.
Read More: VBA Object Variable or with Block Variable Not Set in Excel
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.
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.
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.
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.
Read More: [Fixed!] Subscript Out of Range Error in Excel VBA
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Related Articles:
- Reasons And Solutions for Excel Object Required Error in VBA
- [Fixed!] Excel Application-Defined or Object-Defined Error in VBA
- How to Fix Compile Error in Hidden Module in Excel
- [Fixed!] Invalid Forward Reference in VBA
- [Fixed!] Unable to Set the Visible Property of the Worksheet Class
- Because of Your Security Settings Macros Have Been Disabled
- Sub or Function Not Defined in Excel VBA
- [Solved!] Excel VBA “Argument Not Optional” Error
- [Fixed!] Method Open of Object Workbooks Failed
