+ Reply to Thread
Results 1 to 2 of 2

Compile error: User-defined type not defined

  1. #1
    Registered User
    Join Date
    08-01-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    76

    Compile error: User-defined type not defined

    Here's what's going on:

    1) I create a macro-enabled workbook to contain a code library. I name the project "XmisterIS" (for the sake of argument).

    2) I save the workbook as an xlam file, then save and close the workbook containing the library.

    3) I create a new macro-enabled workbook in which I would like to use the code library.

    4) In the code window, I go into Tools -> References -> Browse. Select the xlam file that I would like to use.

    5) The name of the project now appears in the list of available references as "XmisterIS". This is a good thing!

    6) I ensure that the box is ticked for the reference named XmisterIS.

    7) I add a button form control and assign it to the macro Test_Click.

    8) In Test_Click, I type "XmisterIS."

    9) Houston, we have a problem! All that appears when I type "XmisterIS." is Sheet1, Sheet2, Sheet3 and ThisWorkbook. None of the modules or class modules appear.

    10) When I manually type the name of the class module (e.g. "Dim instance as XmisterIS.CSomeClass") and then I run the macro, I then get the error "Compile error: User-defined type not defined" on that line.

    So ... Excel can see my library ... but why can't it see the modules and class modules within it? What have I omitted to do?

    UPDATE: I have discovered (by accident) that if the project being referenced contains any modules which contain types, then only those modules and only those types are made available in the project from which the reference is made. What on earth is happening?!!

    EDIT:

    *** SOLVED ***

    I have now learned the difference between Private and Public Not Creatable instancing! Google was kind to me in the end (after a LOT of searching for the wrong thing ...) See here: http://support.microsoft.com/kb/555159

    The question remains though, why is there not a "Public Creatable" (or just plain "Public") instancing option in Excel? I am sure there is a good reason for it, I just don't know enough about Excel (yet) to know the reason.
    Last edited by XmisterIS; 03-21-2014 at 09:38 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Compile error: User-defined type not defined

    For class objects see explanation of 'Using classes in multiple projects'

    http://www.cpearson.com/Excel/Classes.aspx
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error message: "Compile error. User-defined type not defined"
    By freckles81 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-13-2012, 09:37 AM
  2. compile error - user defined type not defined
    By TMP123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2010, 01:42 PM
  3. VB Compile Error - User-defined type not defined
    By dbwiz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2010, 04:31 PM
  4. Error message: "Compile error. User-defined type not defined"
    By lottesfog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2007, 06:38 PM
  5. [SOLVED] Help: Compile error: type mismatch: array or user defined type expected
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1