Making VBA Class Module Library

hi,

Why not try the following tutorial?

http://www.cimaware.com/resources/article_39.html
http://www.cpearson.com/excel/classes.aspx

Hi Royhsiao,

I have just been looking at the cpearson example thanks. I cannot see where I have gone wrong. The workbook that contains the class modules is just a normal workbook, does that make a difference.

When I type "Set obj = MyProj." the intellisense list the the worksheets and the workbook.

If I just type "obj." the intellisense populates with the functions in the class, but obviously at run time it does not work with the error message 'object not set etc'

Thanks,

I think you are trying to distribute your code using references.
Here is the easier way to reference to the macro on another worksheet.
1) write the code in module start with public xxxx and change the VbaProject name
2) click tools, reference, browse, and select the workbooks.
3) make sure the workbook is in the references folder
4) you could just use call to use the code in another project

see the following picture and let me know if you have more questions.

Basically, you are creating a add in like xla file and here is the post about creating the add-in.
http://www.cpearson.com/excel/createaddin.aspx

reference.JPG

Hi Royhsiao,

Thanks for the link on the addinn. I understand what you are saying and agree that this way is fine. However I'm just trying to find out how to make a library.

Cheers,

Hi rorya,

Thanks for the reply. I put the Public Function GetMyClass1 inside the class module - is that wrong?

Thanks,

Yes - it needs to be in a normal module, since it returns an instance of the class.

If the code is in the class, then you need an instance of the class to call the code, and you can't get an instance of the class until you call the code, so it's Catch 22. ;)