Compile throws a "User-defined type not defined" error but does not go to the offending line of code
This is a symptom specifically when compiling an Excel VBA project. The following error occurs:
User-defined type not defined
However, the code that produces this error is not highlighted by the compiler and so I cannot identify the issue.
What I already know and have tried
This is a «User-defined type not defined» error that I have seen before with simple issues such as naming something As Strig instead of As String . However, this particular error is only popping up during the Debug > Compile VBAProject menu option and when the error message box pops up it does not highlight the line of code that the error is occurring in.
After a lot of research I have found that this bug can be related to missing references and I have ruled this out as I have included all needed references and Toolbox objects.
To ensure I wasn’t missing any obvious missing Dim statements I have added Option Explicit to all code pages (forms included) to make sure nothing was missing. The error still shows when running a compile.
There is also this known bug that states the issue has been known to happen because of the VB6 projects using binary compatibility:
Turn off Binary Compatibility and compile the project. Visual Basic will highlight the line of code that contains the User Defined Type that is not defined. After resolving the problem, Binary Compatibility can be turned back on.
I found this article via this Question and Answer, however, I cannot find this option in the standard Excel VBA editor.
Help save mine and others’ sanity!
I know from Google searches and other questions that I am not the only one who has had this issue.
I have tried going through the code manually but there are simply too many lines to feasibly do so.
Is there a way of turning off Binary Compatibility in Excel VBA projects? How do people find this offending line of code if they can’t debug to what they need to change? Any help would be lovely!
Thank you in advance.
Edit: I have found the offending line of code and so my particular issue is solved The problem is still here after removing that particular line — it was a misspelt control name on a form being referenced in its code. This still does not solve the particular issue of how you would go about finding this offending code was the issue. Are we able to find a good way of finding the offending code when this bug happens so others in the future can avoid this agony?
21 Answers 21
My solution is not good news but at least it should work.
My case is: I have a .xlsm from a coworker. 1) I open it and click the button: it works fine. 2) I save the file, close excel, open the file again: now it doesn’t work anymore. The conclusion is: the code is OK but excel can’t manage references correctly. (I’ve tried removing the re-adding the references without any success)
So the solution is to declare every referenced object as Variant and use CreateObject(«Foo.Bar») instead of New Foo.Bar .
I had exactly the same problem (always seems to occur when I try to implement a Interface onto a userform. Download and install Code Cleaner from here. This is a freeware utility that has saved me on numerous occasions. With your VBA project open, run the «Clean Code. » option. Make sure you check the «backup project» and/or «export all code modules» to safe locations before running the clean. As far as I understand it, this utility exports and then re-imports all modules and classes, which eliminates compiler errors that have crept into the code. Worked like a charm for me! Good luck.
Since it sounds like you’ve tried many different potentional solutions, you’ll probably have to do this the long methodical way now.
Create a new blank workbook. Then piece by piece copy your old workbook into it. Add a reference, write a little bit of code to test it. Ensure it compiles, ensure it runs. Add a sub or function, again, write a little test sub to run it, also ensure it compiles. Repeat this process slowly adding and testing everything.
You can speed this up a bit by first trying larger chunks, then when you find one that triggers the problem, remove it and break it into smaller peices for testing.
Either you will find the offender, or you’ll have a new workbook that magically does not have the problem. The latter would be due to some sort of hidden corruption in the workbook file, probably in the binary vbproject part.
Welcome to the world of debugging without debuggers or other helpful tools to do the heavy lifting for you!
Just letting you all know I had this problem too. Rather than the code, the issue lay with what macro a button was calling. (It had been calling the ‘createroutes.createroutes’ macro, but I had renamed the ‘createroutes’ module to ‘routes’.) Therefore the problem was fixed by pointing the button to the correct location.
Had a similiar experience, but it was because I had renamed an enum in one of my classes. I exported and re-imported the Classes that had referred to the old enum and the error message disappeared. This suggests it is a caching issue in the VBA environment.
I was able to fix the error by
- Completely closing Access
- Renaming the database file
- Opening the renamed database file in Access.
- Accepted various security warnings and prompts.
- Not only did I choose to Enable Macros, but also accepted to make the renamed database a Trusted Document.
- The previous file had also been marked as a Trusted Document.
- Successfully compile the VBA project without error, no changes to code.
- After the successful compile, I was able to close Access again, rename it back to the original filename. I had to reply to the same security prompts, but once I opened the VBA project it still compiled without error.
A little history of this case and observations:
- I’m posting this answer because my observed symptoms were a little different than others and/or my solution seems unique.
- At least during part of the time I experienced the error, my VBA window was showing two extra, «mysterious» projects. Regrettably I did not record the names before I resolved the error. One was something like ACXTOOLS. The modules inside could not be opened.
- I think the original problem was indeed due to bad code since I had made major changes to a form before attempting to update its module code. But even after fixing the code the error persisted. I knew the code worked, because the form would load and no errors. As the original post states, the “User-defined type not defined” error would appear but it would not go to any offending line of code.
- Prior to finding this error, I ensured all necessary references were added. I compacted and repaired the database more than once. I closed down Access and reopened the file numerous times between various fix attempts. I removed the suspected offending form, but still got the error. I tried other various steps suggested here and on other forums, but nothing fix the problem.
- I stumbled upon this fix when I made a backup copy for attempting drastic measures, like deleting one form/module at a time. But upon opening the backup copy, the problem did not reoccur.
I had this problem with an ordinary VB6 program. It turned out that I had omitted a class definition, not a user-defined type. Apparently VB saw something like «Thing.name» and assumed Thing was a UDT. Yes, it’s a serious VB6 bug, but you could hardly expect Microsoft to support something they sold sixteen years ago. So what versions of the various products involved are you using? This is only interesting if it occurs with a product that MS supports.
I know this is old, but I had a similar problem and found a fix:
I had the same issue with a module I ported from Excel into Access, in an unrelated UDF I was dimming ‘As Range’ but ranges don’t exist in Access. You may be using a variable type without having the proper reference library turned on.
If you have any non-standard dims google them and see if you’re missing the reference to that library under tools.
For future reference —
I had this issue with this piece of code in Microsoft Access with the debugger highlighting the line with the comment:
I had to select references that were previously unselected. They were
Microsoft Outlook 15.0 Object Library
Microsoft Outlook View Control
For the Scripting.Dictionary type, you can either use late binding (as already pointed out ) with:
Which works, but you don’t get the code auto completion. Or you use early binding, but you need to make sure that VBA can find the Scripting.Dictionary type by adding the reference to the Microsoft Scripting Library via VBA—>Tools—>References—> «Microsoft Scripting Runtime». Then you can use:
. and auto completion will work.
Possible solution, you are trying to work with Powerpoint via Excel VBA and you didn’t activate Powerpoint Object Library first.
To do this, in the VBA editor top menu select Tools, References, then scroll down to click the library called Microsoft Powerpoint xx.x Object Library. Office 2007 is library 12, each Office version has a different library. FYI, I’ve experienced some odd errors and file corruption when I activate the 2007 library but someone tries to open and run this macro using Excel 2003. The old version of Excel doesn’t recognize the newer library, which seems to cause problems.
When I had this error in a MS Access database I used the /decompile command line switch along with the Compact/Repair option. Worked for me. I had removed a reference that I was sure my code no longer used and started getting this error.
This error can occur due to a missing reference. For example when changing from early binding to late binding, by eliminating the reference, some code may remain that references data types specific the the dropped reference.
Try including the reference to see if the problem disappears.
Maybe the error is not a compiler error but a linker error, so the specific line is unknown. Shame on Microsoft!
After years I have discovered one, if not the, answer to the Microsoft bug of the ‘User-defined type not defined’ error in Excel. I’m running Excel 2010 for Windows.
If you have a UDF named for example ‘ xyz() ‘, then if you invoke a non-existent entity beginning with that name followed by a period followed by other chars — e.g., if you try to invoke non-existent range name ‘ xyz.abc ‘, the stupid app throws that wrong msg., after which it returns you to your sheet.
In my case it was especially unnerving, because I have UDFs named with just one letter, e.g. x() , y() , etc., and I also have range names that include periods—‘ x.a ‘, ‘ c.d ‘, etc. Every time I, say, misspelled a range name—for example, ‘x.h’, the ‘User-defined …’ error was thrown simply because a UDF named ‘ x() ‘ existed somewhere in my project.
It took several hrs. to diagnose. Suggestions above to incrementally remove code from your project, or conversely strip all code and incrementally add it back in, were on the right track but they didn’t follow thru. It has nothing to do with code per se; it has only to do with the name of the first line of code in each proc, namely the Sub MyProc or Function MyProc line naming the proc. It was when I commented out one of my 1-letter-named UDFs in a completely unrelated part of the project that the bugged error msg. went away, and from there, after another hr. or so, I was able to generalize the rule as stated.
Perhaps the bug also occurs with punctuation characters other than period (‘ . ‘) But there aren’t very many non-alpha chars permitted in a range name; underline (‘ _ ‘) is allowed, but using it in the manner described doesn’t seem to throw the bug.
Jon Schneider’s Tech Blog
An Excel VBA subroutine that involves interaction with the Windows Clipboard fails to run with the error: “Compile error: User-defined type not defined.” The line of code highlighted by Excel as being the cause of the error includes a reference to the “DataObject” object type.
Solution
(Tested on my machine with Excel 2003 SP3)
In the Microsoft Visual Basic editor:
- Stop debugging by clicking the “Stop” button on the toolbar (if debugging isn’t already stopped).
- Tools menu | References
- In the list of available references, find “Microsoft Forms 2.0 Object Library”, check it, and click OK. (For me, this was the 2nd unchecked item from the top of the list; it wasn’t sorted alphabetically in the list like most of the rest of the listed items.)
- Run the macro again.
That’s it! Hope this helps!
1 comment:
Today I heard about a good software-Excel file fix.I found this tool accidentally and to my surprise utility recovered some of my excel files.Moreover program helped me in seconds and free of cost.
Non-spammers: Thanks for visiting! Please go ahead and leave a comment; I read them all!
Attention SPAMMERS: I review all comments before they get posted, and I REPORT 100% of spam comments to Google as spam! Why not avoid getting your account banned as quickly — and save us both a little time — by skipping this comment form and moving on to the next one on your list? Thanks, and I hope you have a great day!
Fix Compile Error User-defined type not defined – VBA Excel/Outlook
VBA Error User-defined type not defined Error
How to fix User-defined type not defined error in VBA ?
User-defined type error message usually occurs when the VBA compiler cannot recognize a custom data type or class that you’ve used in your code. This can be caused by various reasons, including missing references, incorrect syntax, or a simple typo.
To solve this error, the first thing you need to do is to identify the source of the problem. Check your code carefully and make sure that all the custom data types or classes are properly defined and referenced. If you’re using external libraries or APIs, make sure that you’ve added the appropriate references to your VBA project.
If you’ve confirmed that all your data types and classes are defined and referenced correctly, you can try resetting the VBA environment. Close all instances of Excel and then reopen the workbook to clear any cached or corrupted data.
Another solution is to explicitly declare the custom data type or class in your code. This can be done by using the “Option Explicit” statement at the beginning of your module and then declaring the custom type/class using the “Type” or “Class” keyword.
Selecting the right References from the available reference list will solve this isssue. In this example, we show how to fix “User-defined type not defined” compile error when “Outlook” called in a statement.
- Go to Tools Menu -> Click References
- Scroll down and select (tick) “Microsoft Outlook 16.0 Object Library” from the Available References list.
- Click “Ok”
Now you will be able to run the code without
VBA User-defined type not defined Outlook
Compile error in Access: User-defined type not defined
If you are used to programming in Access 97 then you may encounter a problem if you upgrade to Access 2000 or higher and you wish to continue to use the DAO object model in your VBA. If you try to run or compile your code you will probably be faced with the following error: "Compile error: User-defined type not defined" and you will notice that one of your DAO variables will be highlighted.
Fortunately this is easily remedied. The problem here is that your database does not have a reference set to the Microsoft DAO Object Library. With Access 97 this reference was already set by default, however Access 2000 and later versions have a reference set to the Microsoft ActiveX Data Objects Library instead. To set the necessary reference simply select Tools — References from the VBE code editor as shown in Figure 1.
If you scroll down the list of available libraries then you will eventually find the Microsoft DAO 3.6 Library as shown in Figure 2. (Version 3.6 is the newest version available). All you have to do is check the box next to this library and click on the OK button. You should now be able to compile your code by selecting Debug — Compile from the main menu.
Occasionally the problem might be that Access is unable to locate the reference, which will be indicated by the word "Missing" shown next to the reference location. If this is the case then uncheck the reference and close the references dialog. Next, open the references dialog again, locate the library from the list and re-check the reference that was missing.
While on the subject, it is also good practice to refer explicitly to your DAO variables with the term "DAO." as shown in the example function below, incase you decide to add ADO code to your database later on (this is known as disambiguation — and is shown in the following example).
Finally, whether programming in Access 97, 2000 or later it is also recommended that you also explicitly close and destroy your DAO objects after use to free up system resources and prevent database bloat. This is accomplished using the "close" method and setting the object variable to "Nothing", shown at the end of the example function above.
(This article is provided "AS IS" with no warranties or guarantees.)
Jamie Czernik
Jamie Czernik is the webmaster of Jamie’s Software providing downloads, articles and tips for MS Access developers.
He has developed a number of applications, including an image management package and a water & sewerage compliance program (utilitycompliance.com) for use in the US. He established Jamie’s Software over 4 years ago and has been helping Access developers with downloads, articles and tips ever since.
Please visit Jamie’s Software for more MS Access downloads, articles and tips.