Compile throws a User-defined type not defined error but does not go to the offending line of code

Question

Symptoms  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  gt  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

User · Answer

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.

User · Answer

Late Binding   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

User · Answer

For the Scripting Dictionary type  you can either use late binding  as already pointed out   with   Dim Dict as Object Set Dict   CreateObject  Scripting Dictionary     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   Dim Dict as Scripting Dictionary Set Dict   New Scripting Dictionary       and auto completion will work

User · Answer

A bit late  and not a complete solution either  but for everyone who gets hit by this error without any obvious reason  having all the references defined  etc   This thread put me on the correct track though  The issue seems to originate from some caching related bug in MS Office VBA Editor   After making some changes to a project including about 40 forms with code modules plus 40 classes and some global modules in MS Access 2016 the compilation failed    Commenting out code was obviously not an option  nor did exporting and re-importing all the 80  files seem reasonable  Concentrating on what had recently been changed  my suspicions focused on removal of one class module    Having no better ideas I re-cereated an empty class module with the same name that had previously been removed  And voli   the error was gone  It was even possible to remove the unused class module again without the error reappearing  until any changes were saved to a form module which previously had contained a WithEvents declaration involving the now removed class   Not completely sure if WithEvents declaration really is what triggers the error even after the declaration has been removed  And no clues how to actually find out  without having information about the development history  which Form might be the culprit     But what finally solved the issue was    In VBE - Copy all code from Form code module In Access open Form in Design view and set Has Module property to No Save the project  this removes any code associated with Form   Not sure if need to close and re-open Access  but I did it  Open Form in Design view and set Has Module property back to Yes In VBE paste back the copied out module code Save

User · Answer

I had the same error yesterday  I had two classes  cProgress and cProgressEx  in my project  one of which was no longer used  and when I removed cProgress class I was given this same compilation error   I managed to fix the error as follows    Exported all modules  forms and classes to the hard drive Removed everything from the project Saved the project Reimported all modules  forms and classes  removed cProgress class  and compiled   The error disappeared

User · Answer

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

User · Answer

For future reference -  I had this issue with this piece of code in Microsoft Access with the debugger highlighting the line with the comment   Option Compare Database Option Explicit  Dim strSQL As String Dim rstrSQL As String Dim strTempPass As String   Private Sub btnForgotPassword Click   On Error GoTo ErrorHandler  Dim oApp As Outlook Application   lt ---------------------------------Offending line Dim oMail As MailItem Set oApp   CreateObject  Outlook application    this is the  instance  of Outlook Set oMail   oApp CreateItem olMailItem   this is the actual  email    I had to select references that were previously unselected  They were  Microsoft Outlook 15 0 Object Library   Microsoft Outlook View Control

User · Answer

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

User · Answer

An different problem with the same symptom  I had a class implemented which was not defined  It was wrapped with a  if that I thought should have not allowed the compiler to see it  but not so  Remove the comment out the Implements statement and all is well  I assume importing the definition would also work

User · Answer

I had the same issue  if you enable Microsoft Scripting Runtime you should be good  You can do so under tools   references   and then check the box for Microsoft Scripting Runtime  This should solve your issue

User · Answer

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

User · Answer

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   For example   Dim objXML As MSXML2 DOMDocument Set objXML   New MSXML2 DOMDocument   Replaced by   Dim objXML As Variant Set objXML   CreateObject  MSXML2 DOMDocument

User · Answer

I had this same problem when I inherited another analyst s workbook  He assigned macros to each button on his worksheet  When I changed the name of the module  not the procedure name   it kicked up this error    The fix was easy  Have the button run an  OnClick   event procedure in the worksheet and have that procedure explicitly Call MyProcedureName

User · Answer

I ve seen this error too when the code stopped at the line   Dim myNode As MSXML2 IXMLDOMNode  I found out that I had to add  Microsoft XML  v6 0  via Tools   Preferences   Then it worked for me

User · Answer

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   -E

User · Answer

I don t have an answer as to what is actually wrong or to directly fix it   I can report that exporting all the code and forms  then importing them into a fresh workbook fixed it for me   All the work was recovered  and nothing more needed to be done to get back to work   Since that works  it seems clear that it must be a bug in Excel  or whatever it is would still be missing in the new version    I do have a possible clue to how this Excel  2007  bug works   It seems to be associated with the removal of a class module  or two     But not every class module  every time for some reason   No code was using any part of the removed modules  or the new imported version would not work either    One module had been cut down to nothing but some comments  in hope of removing anything that could be  missing  later  so that there was no code visibly connecting it to any other part of the project   But the error came up shortly after I removed the code module entirely   The error also stopped when the removed  but exported just in case  modules were returned   So for convenience  this project had been packing two unused code modules to avoid this error   It appears then  that somehow some element of some departed modules remains in the VBA environment  even if nothing is being used from these  there two in this case  class modules anywhere in the project   Consequently  the debug compile finds that element  and it is no surprise that something is missing because it is  Since the thing that is missing is nowhere in the project there is nothing to highlight so there is a message only and nothing else happens   That would explain the observed behavior

User · Answer

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

User · Answer

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   Jim Luedke

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Compile throws a "User-defined type not defined" error but does not go to the offending line of code

Examples related to excel

Examples related to vba

Examples related to debugging

Examples related to compiler-errors