How to make a library I can access in VBA in Excel

Question

I am doing a lot of similar tasks among some VBA scripts I am writing and would like to develop a library (a bunch of convenience functions using the typelib I'm working with) which I can call from all my various scripts. I am new to the VBA world and do not know how this is done and have had a surprisingly hard time trying to figure it out.

Answer 1

You will need to use VB to do this. With Microsoft plug-ins to VB, you can manipulate Excel files without even opening them, much like you do now. The code will be very similar once you have the file open.

I would transfer all my code to VB and after its working like you have your VBA macros start making libraries out of the common stuff.

Lots of work, but if you really are doing a lot of this stuff, it will be great in the long run. (Job security too ;)

Answer 2

Does this example help? It appears to also have a fix to a common issue when setting this up - You didn't mention which version of office - But 2003 is mentioned in that thread, so should be the fairly straight forward common case.

http://socko.wordpress.com/2008/06/01/vba-code-library/

Answer 3

Check the comments here

http://www.dailydoseofexcel.com/archives/2004/10/15/code-libraries/

Answer 4

Another option you have though it depends on the nature of your functions, is to create a COM object that you can call from your VBA scripts.

You can easily create a COM object using many languages including Delphi and VB (old style) it is also possible in .NET though a little more involved. You can then do your calculations in your COM object and even pass in the excel worksheet etc you wish to manipulate if required.

Depending on the nature of your functions this may or may not be useful.

Answer 5

I think what I was looking for is an Add-In. Thanks everyone for your information.