Multiple workbooks, one VBA module?

LarryJ

Board Regular
Joined
Mar 25, 2002
Messages
60
Is it possible to have multiple workbooks use the code in a single VBA module?

I have 18 workbooks that all have identical code behind them. When I need to make a change, I make it in one, the copy the completed code to each of the other workbooks, replacing the old code.

It sure would be easier to reference an external .bas file, or automatically suck it in before it's used.

I've tried to figure this out off and on for a year, but now the number of workbooks is growing and it's becoming more important to figure out.

I'm in Excel 97 on NT 4 (soon to move to 2002 on XP).
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I don't know how to create an Add-In, plus I assume each user would then have to add the add-in somehow. I want this to be "transparent" to the users.

If it's easy to create one and can be transparent, I might try to figure this out.

I'll see if I can figure out how to reference a "Main" workbook with the code in it.

I just thought pulling in a lone .bas file would be easier to maintain and manage, but I guess not.
 
Upvote 0
I would also like to know how to do this. I think you can store the module somewhere so that it's public and all other workbooks can see the module but I've never figured out how to do this either. Does anybody know?

Dave
 
Upvote 0
I just thought of a possible problem with the Main workbook solution.

The workbook would have to be opened, to be able to access the code in it. That could cause a problem if multiple people are using one of the other workbooks at the same time and they also try to open the Main one.

Plus, they could see another workbook has been opened and may switch to it to close it, causing problems.
 
Upvote 0
Wow, I was sure thinking I would get more possible solutions than this!

Oh well, I guess I'll just keep hunting.
 
Upvote 0
Hi
I'm not sure that this is what you're looking for but here it is.
It's possible to open and get a module that has been exportet fra VBA and placed on ex. a server.
These code has to be in the workbooks. the 1. one imports a module saved as a *.bas fil at startup and the second deletes it at close down.
Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library. In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library. This enables VBA to find the definitions of these objects.

For more info take a look at :
http://www.cpearson.com/excel/vbe.htm

Sub Auto_open()
Dim FName As String
FName = "C:ProgrammerMicrosoft OfficeOfficeModule13.bas"
ThisWorkbook.VBProject.VBComponents.Import FName
End Sub

Sub Auto_close()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("Module13")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

regards Tommy
 
Upvote 0
Friend,

One way and easiest is to have ADDIN but disadvantage of addin is that it will load whenever you work with excel.
second way (i make use of this) is to open your VBE and then go to tools and here you need to have a REFERENCE to your workbook where you get all your codes placed in a book.

TOOLS > REFERENCE > BROWSE

here browse for your workbook.

you can have your addin referred or your excel workbook referred.

i hope this will help

ni****h desai
http://www.pexcel.com
 
Upvote 0
I think the VBE code to include/remove the module is what I want.

I tried this last year, but could not get it to work (don't remember why now, I just remember the code). But I will try it again.

Thanks for the link, that might help me figure out what I did wrong last time.

I want to stay away from AddIn's b/c the only time this other code is needed is when working in these timesheet workbooks. I don't think each person on my team is going to want to add one. So I am wanting to do this all "seemlessly" behind the scenes so the users don't know/care what's going on.

Thanks again for the comments.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top