Complete error user defined type not defined

Are you sitting there staring at this error on your VBA screen and getting frustrated? No worries, we shall fix it.

But before deep diving into the root cause and solution to fix this error, let’s understand the correct procedure for using an object in our code. It will ease the debugging process.

For this example, let’s look at a Dictionary object.

DICTIONARY in VBA:

A DICTIONARY is an object similar to the VBA COLLECTION object with the following differences:

1. The values of the keys can be updated or changed later and
2. The key / item value can easily be checked for existence without completely iterating through all the items. This also helps to retrieve values easily.

If you’re a beginner, just imagine that this object is a real time dictionary where the keys are the words and items are the respective definitions. As in a dictionary, in the VBA object we do not need to iterate through all the keys to find the value of one specific key. 

And just like any other object in VBA, we can use a dictionary object by adding the corresponding reference through Tools menu. Declaration and definition of objects can be done through early or late binding methods per the developer’s convenience.

Resolving the Error

The error in the title is a compile time error that is encountered when you compile the code.

Analyze the meaning and “ROOT CAUSE” of the error:

Let us split and read the error to understand it better.

User-defined type | not defined

First, let’s try to understand we have encountered the error because something is

“not defined”.

A possible reason for the error to occur is that you are utilizing the early binding method to declare and define the object, but the required reference has not been added.

Refer to the sample code below to understand the difference between early and late binding.

Late binding:

' Create a dictionary object using the late binding method.
    Dim obdict As Object
    Set obdict = CreateObject("Scripting.Dictionary")

Early binding:

' Create a dictionary object using the early binding method.
    Dim obdict As New Scripting.Dictionary

Solution:

Try one of the following steps to resolve the error:

Method 1

Maybe VBA doesn’t understand that you have defined the object. In VBA, you need to add the respective reference for the object to let the language know that you have properly defined it.

1. Goto the menu Tools-> References
2. Select the library “Microsoft Scripting Runtime.” (This varies depending on the object used. Here the same dictionary object is considered for explanation purposes
3. Click on the “OK” button and close the dialog
4. Now you can compile the code and see that the error doesn’t appear anymore

Note: All this is NOT mandatory if you are following “late binding” method.

Method 2

Use the late binding method where you declare a generic object first, then define its type. This does not require any reference.

Syntax:

Dim <variable> As Object

Set <variable> = CreateObject("Scripting.Dictionary")

Example for an Excel sheet object:

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Sheet")

Example for a dictionary object:

'Example of creating a dictionary object

Dim odict As Object

Set odict = CreateObject("Scripting.Dictionary")

Video Example

The video below shows how to resolve the error using each of the two methods above.

User-defined type not defined

You can create your own data types in Visual Basic, but they must be defined first in a Type. End Type statement or in a properly registered object library or type library. This error has the following causes and solutions:

You tried to declare a variable or argument with an undefined data type or you specified an unknown class or object.

Use the Type statement in a module to define a new data type. If you are trying to create a reference to a class, the class must be visible to the project. If you are referring to a class in your program, you must have a class module of the specified name in your project. Check the spelling of the type name or name of the object.

The type you want to declare is in another module but has been declared Private. Move the definition of the type to a standard module where it can be Public.

The type is a valid type, but the object library or type library in which it is defined isn’t registered in Visual Basic. Display the References dialog box, and then select the appropriate object library or type library. For example, if you don’t check the Data Access Object in the References dialog box, types like Database, Recordset, and TableDef aren’t recognized and references to them in code cause this error.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник

PRB: ADO: Compile Error: User-Defined Type Not Defined

Symptoms

When you compile your ADO project, you receive the following error:

Compile error: User-defined type not defined

This can occur on either a Connection or Command object.

Cause

You may have referenced one of the following libraries instead of the Microsoft ActiveX Data Objects (ADODB) type library:

Microsoft ActiveX Data Objects Recordset (ADOR) type library.

Microsoft ActiveX Data Objects (Multi-dimensional) (ADOMD) type library.

Resolution

Remove the incorrect type library reference from your project, and add a reference to the correct type library.

Status

This behavior is by design.

More Information

Steps to Reproduce Behavior

Create a new project and add a command button (Command1) to a form.

Add a reference to the Microsoft ActiveX Data Objects Recordset Library.

Add the following code to the form:

Run the project and click the command button. The error appears.

Remove the reference, and add a reference to the Microsoft ActiveX Data Objects Library.

Click the command button. The error does not appear.

Microsoft Access users will have to use ADODB.Connection to avoid confusion with the DAO Connection object.

If the Intellitype feature is turned on, you should notice that it does not show Connection as a valid object with the ADOR type library, but does with the ADODB type library. This is a good indication that you do not have the correct type library referenced.

This error can also occur when referencing objects in other type libraries that aren’t referenced.

Источник

Good Afternoon (at least here it is afternoon),

I have this code block I’ve been using for years without issue. Today I copied it to another application to incorporate its use there. I am getting a compile error on this line     fDialog As FileDialog

The block in its entirety is;

Private Sub doc2docx_Click()
    Dim strFilename As String
    Dim strDocName As String
    Dim strPath As String
    Dim oApp As Object
    Dim oDoc As Object
    Dim fDialog As FileDialog
    Dim intPos As Integer
    Dim strPassword As String
    Dim strWritePassword As String
    Set fDialog = Application.FileDialog(4) ' msoFileDialogFolderPicker
    With fDialog
        .Title = "Select folder and click OK"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then
            MsgBox "Conversion Cancelled By User", , "List Folder Contents"
            Exit Sub
        End If
        strPath = fDialog.SelectedItems.Item(1)
        If Right(strPath, 1) <> "" Then strPath = strPath + ""
    End With
    Set oApp = CreateObject(Class:="Word.Application")
    If oApp.Documents.Count > 0 Then
        oApp.Documents.Close SaveChanges:=-2 ' wdPromptToSaveChanges
    End If
    If Left(strPath, 1) = Chr(34) Then
        strPath = Mid(strPath, 2, Len(strPath) - 2)
    End If
    strFilename = Dir(strPath & "*.doc")
    strPassword = InputBox("Enter password to open the document")
    strWritePassword = InputBox("Enter password to edit the document")
    Do While strFilename <> ""
        Set oDoc = oApp.Documents.Open(FileName:=strPath & strFilename, _
                                  PasswordDocument:=strPassword, _
                                  WritePasswordDocument:=strWritePassword, _
                                  AddToRecentFiles:=False)
        strDocName = oDoc.FullName
        intPos = InStrRev(strDocName, ".")
        strDocName = Left(strDocName, intPos - 1)
        strDocName = strDocName & ".docx"
        oDoc.SaveAs2 FileName:=strDocName, _
                     FileFormat:=12, _
                     CompatibilityMode:=14
        oDoc.Close SaveChanges:=0 ' wdDoNotSaveChanges
        strFilename = Dir
    Loop
    oApp.Quit
End Sub

My references are in this order;

Visual Basic For Application

Microsoft Access 16.0 Object Library

OLE Animation

Microsoft Office 16.0 Access Database Engine Object Library

Microsoft ActiveX Data Objects 6.0 Library

Microsoft Ward 16.0 Object Library

Thank you for your assistance with his issue.

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012