?
Solved

Making VBA Class Module Library

Posted on 2011-10-28
7
Medium Priority
?
566 Views
Last Modified: 2012-05-12
Hi,

I have made an Excel Project library with some class modules following the examples on this link http://www.excelforum.com/excel-programming/526431-make-vba-class-module-library.html So i have been able to create a reference in a new workbook to 'MyProj'.

When I define Obj as MyProj.MyClass it works fine to, the intellisense works. The issue comes on the line Set ObjMyProj.GetMyClass1() (from the webpage) and I have checked the function name is correct and things like that. It just highlights the GetMyClass1 & says Method or data member not found? In the object browser though I can see the method??

Thanks,



0
Comment
Question by:mcs26
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Expert Comment

by:royhsiao
ID: 37045489
0
 

Author Comment

by:mcs26
ID: 37045516
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,
0
 
LVL 6

Expert Comment

by:royhsiao
ID: 37046206
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 37055535
Did you put this code:

Public Function GetMyClass1() As MyClass
 Set GetMyClass1 = New MyClass
 End Function

into a normal module in the MyProj workbook?
0
 

Author Comment

by:mcs26
ID: 37055807
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,
0
 

Author Comment

by:mcs26
ID: 37055810
Hi rorya,

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

Thanks,
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37055837
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. ;)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question