Excel VBA: Using external modules

[Azerty99]

Hi Guys,

I juts don't seem to be able to formulate the right question to Google today, so maybe you can help me out.

I have code in several modules (saved as .bas files) and want to use them in different workbooks. How do I do this?

All I have found is code for importing the code / module. I don't want to do this. I simply want to use the module subs. Or do I do this then remove them?

have found this:
http://www.mrexcel.com/forum/showthread.php?t=29747&highlight=external+modules

Doesn't solve the issue. I have user functions which Excel returns #NAME? etc...

Thanks

 

[Azerty99]

FYI:

According to others here, people have asked the question before, and no real solution has ever been given.

So I'm going to combine all my workbooks into one BIG one and have all the code in one place.

Not the greatest solution, but will have to do for now.

 

[VoG]

Why not put them in your Personal workbook http://www.rondebruin.nl/personal.htm

 

[MrKowz]

Try saving them in your personal.xls file. If you do not have one of these yet, do the following:

1) Click the Record macro button
2) Store Macro In: Personal Macro Workbook
3) Record a short macro
4) Go to the VBA editor, and you should now have a Personal.XLS file

In the personal.xls file, import the modules you want to be able to run all the time. Make sure that they are all Public Sub and not just Sub or Private Sub.

HTH

 

[Azerty99]

Guys,

Thanks very much for pointing me in the right direction. My solution of combining into one workbook was not a solution as I use range names which would all have to be changed as they would be triplicated across the workbook.

Using the personal.xls is not a solution as I am building a solution for others to use.

But the link points to creating an Add-In which has solved my problem.

 

[FireFighter1005]

Guys,

Thanks very much for pointing me in the right direction. My solution of combining into one workbook was not a solution as I use range names which would all have to be changed as they would be triplicated across the workbook.

Using the personal.xls is not a solution as I am building a solution for others to use.

But the link points to creating an Add-In which has solved my problem.

I would be carefull using this method. Eventually you will face a moment where you will need to update an external module used in many other files.

• If those are stored in an unknown location, you will most certainly run into user complaints that the file is no longer working.
• If the files referring the external module are stored in a known location, you still need to figure out which ones are using this module and update them accordingly