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.
Bookmarks