Multiple workbooks, one VBA module?

LarryJ · Mar 26th, 2002, 10:25 AM

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).

Juan Pablo González · Mar 26th, 2002, 10:26 AM

How about creating an AddIn ? or, setting a reference to a "main" workbook in each of the 18 books ?

LarryJ · Mar 26th, 2002, 10:39 AM

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.

davers5 · Mar 26th, 2002, 10:50 AM

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

LarryJ · Mar 26th, 2002, 10:58 AM

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.

LarryJ · Mar 27th, 2002, 05:02 AM

Wow, I was sure thinking I would get more possible solutions than this!

Oh well, I guess I'll just keep hunting.

Tommy Bak · Mar 27th, 2002, 06:23 AM

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

nisht · Mar 27th, 2002, 07:41 AM

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

nishith desai
http://www.pexcel.com

LarryJ · Mar 27th, 2002, 08:13 AM

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.

Mar 26th, 2002, 10:26 AM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	How about creating an AddIn ? or, setting a reference to a "main" workbook in each of the 18 books ? __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 10:25 AM	#1
LarryJ Board Regular Join Date: Mar 2002 Location: Houston, TX Posts: 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).

Mar 26th, 2002, 10:39 AM	#3
LarryJ Board Regular Join Date: Mar 2002 Location: Houston, TX Posts: 60	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.

Mar 26th, 2002, 10:50 AM	#4
davers5 Board Regular Join Date: Feb 2002 Posts: 255	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

Mar 26th, 2002, 10:58 AM	#5
LarryJ Board Regular Join Date: Mar 2002 Location: Houston, TX Posts: 60	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.

Mar 27th, 2002, 05:02 AM	#6
LarryJ Board Regular Join Date: Mar 2002 Location: Houston, TX Posts: 60	Wow, I was sure thinking I would get more possible solutions than this! Oh well, I guess I'll just keep hunting.

Mar 27th, 2002, 06:23 AM	#7
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	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

Mar 27th, 2002, 07:41 AM	#8
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	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 nishith desai http://www.pexcel.com

Mar 27th, 2002, 08:13 AM	#9
LarryJ Board Regular Join Date: Mar 2002 Location: Houston, TX Posts: 60	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.