What is the best way to write the same VBA code for multiple Excel workbooks? [modified]
-
Problem: Currently I have 12 Excel workbooks (each with 7 worksheets). The data is identical in nature. The common part is the VBA code. Every time I have to make a change to one of the worksheet's VBA code, I need to copy the changes to the other workbooks. I can use either the Excel buit-in VB editor, or any flavor of Visual Studio. I would like to be able to make the modification only once and have the workbooks reference the code instead of copying and pasting to each workbook. Question(s): 1. Is there a way that I can have one central location for the VBA code? 2. Would an Excel add-in be appropriate? 3. Should I program using VS (VSTO)? I am open to any approach to this problem.
Michael If we knew what it was we were doing, it would not be called research, would it? --Albert Einstein
modified on Friday, December 07, 2007 3:25:20 PM
-
Problem: Currently I have 12 Excel workbooks (each with 7 worksheets). The data is identical in nature. The common part is the VBA code. Every time I have to make a change to one of the worksheet's VBA code, I need to copy the changes to the other workbooks. I can use either the Excel buit-in VB editor, or any flavor of Visual Studio. I would like to be able to make the modification only once and have the workbooks reference the code instead of copying and pasting to each workbook. Question(s): 1. Is there a way that I can have one central location for the VBA code? 2. Would an Excel add-in be appropriate? 3. Should I program using VS (VSTO)? I am open to any approach to this problem.
Michael If we knew what it was we were doing, it would not be called research, would it? --Albert Einstein
modified on Friday, December 07, 2007 3:25:20 PM
VBA doesn't include any "include" support. It can't import code from some other source. The code is part of the workbook itself. I think you're taking the wrong approach. If the code needs to work with data from other files, it's the data that needs to be brought to the code, not the code to the data. Your code should be in one spot, and have the ability to use data from any workbook that you chose.
redjoy wrote:
1. Is there a way that I can have one central location for the VBA code?
Not in an external file, no. But, you can have an single workbook that can import data from other workbooks. Or, you can write the code as an Add-In to Excel.
redjoy wrote:
2. Would an Excel add-in be appropriate?
That depends on what your code is doing and what your business process dictates.
redjoy wrote:
3. Should I program using VS (VSTO)?
Probably. Do the research on VSTO here[^].
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
VBA doesn't include any "include" support. It can't import code from some other source. The code is part of the workbook itself. I think you're taking the wrong approach. If the code needs to work with data from other files, it's the data that needs to be brought to the code, not the code to the data. Your code should be in one spot, and have the ability to use data from any workbook that you chose.
redjoy wrote:
1. Is there a way that I can have one central location for the VBA code?
Not in an external file, no. But, you can have an single workbook that can import data from other workbooks. Or, you can write the code as an Add-In to Excel.
redjoy wrote:
2. Would an Excel add-in be appropriate?
That depends on what your code is doing and what your business process dictates.
redjoy wrote:
3. Should I program using VS (VSTO)?
Probably. Do the research on VSTO here[^].
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007First of all thanks for your reply. I am new to add-ins and VSTO. I only wrote the code behind the worksheets for my personal testing needs but my boss saw this and wants everyone to use this format. Hence the 15+ extra workbooks. I am now trying to re-write my code more efficiently (on my own time) as to make maintenance easier on me (and hopefully my test successors).
Dave Kreskowiak wrote:
I think you're taking the wrong approach. If the code needs to work with data from other files, it's the data that needs to be brought to the code, not the code to the data. Your code should be in one spot, and have the ability to use data from any workbook that you chose.
The code does not need to work with data from other files. All the data needed is already contained within each workbook.
Dave Kreskowiak wrote:
redjoy wrote: 2. Would an Excel add-in be appropriate? That depends on what your code is doing and what your business process dictates.
The format of each Workbook is the same. The code for each workbook is identical. I use my workbook for analysis purpose and generate a report for each of my workbooks (CSCI). I execute some code based of values in various columns for each row in worksheet 7. unique to each workbook: Columns A-D are used for showing my requirements, test cases and test conditions. The same in each workbook: Columns E and F are used to show the status of completeness and performance of current requirement. Column G shows the platform the current test case runs on (A, B, Both). Columns H and I shows the test result (P, F, N/A) for platform A chosen in G. Columns J and K shows the test result (P, F, N/A) for platform B chosen in G. Columns I-P: not important to code. Code behind Worksheet 7 for example (pseudo code):
Sub Worksheet_Changed(target as range) 'changes the color coding of active row based on columns E through K.
if a2<>""then
if e2="Complete" and f3="Met" then set the background colors of a2:p2 to GREEN.
...code for color RED.
...code for color YELLOW.
else color WHITE.
end if
else
if g3 = "A" and h3="P" and i3="P" then set the background colors of b3:i3 to GREEN.
... code for color RED.
else color WHITE.
end if
end ifCode behind worksheet 8 copies portions of worksheet 7 to worksheet 2 and 6 and updates a database with the content of worksheet
-
First of all thanks for your reply. I am new to add-ins and VSTO. I only wrote the code behind the worksheets for my personal testing needs but my boss saw this and wants everyone to use this format. Hence the 15+ extra workbooks. I am now trying to re-write my code more efficiently (on my own time) as to make maintenance easier on me (and hopefully my test successors).
Dave Kreskowiak wrote:
I think you're taking the wrong approach. If the code needs to work with data from other files, it's the data that needs to be brought to the code, not the code to the data. Your code should be in one spot, and have the ability to use data from any workbook that you chose.
The code does not need to work with data from other files. All the data needed is already contained within each workbook.
Dave Kreskowiak wrote:
redjoy wrote: 2. Would an Excel add-in be appropriate? That depends on what your code is doing and what your business process dictates.
The format of each Workbook is the same. The code for each workbook is identical. I use my workbook for analysis purpose and generate a report for each of my workbooks (CSCI). I execute some code based of values in various columns for each row in worksheet 7. unique to each workbook: Columns A-D are used for showing my requirements, test cases and test conditions. The same in each workbook: Columns E and F are used to show the status of completeness and performance of current requirement. Column G shows the platform the current test case runs on (A, B, Both). Columns H and I shows the test result (P, F, N/A) for platform A chosen in G. Columns J and K shows the test result (P, F, N/A) for platform B chosen in G. Columns I-P: not important to code. Code behind Worksheet 7 for example (pseudo code):
Sub Worksheet_Changed(target as range) 'changes the color coding of active row based on columns E through K.
if a2<>""then
if e2="Complete" and f3="Met" then set the background colors of a2:p2 to GREEN.
...code for color RED.
...code for color YELLOW.
else color WHITE.
end if
else
if g3 = "A" and h3="P" and i3="P" then set the background colors of b3:i3 to GREEN.
... code for color RED.
else color WHITE.
end if
end ifCode behind worksheet 8 copies portions of worksheet 7 to worksheet 2 and 6 and updates a database with the content of worksheet
redjoy wrote:
The code does not need to work with data from other files. All the data needed is already contained within each workbook. ... The format of each Workbook is the same. The code for each workbook is identical.
You're missing the point. The code (all 15 copies of it) DOES work with the data from every workbook. You're trying to avoid maintaining 15 copies of the code, so you need to have only a single copy of it somewhere. That single copy needs to get at the data for all 15 workbooks. But, as you post some of the code, you have a problem. You're handling the events fired by changes in the worksheet. This makes it very difficult to seperate the code from the data, and hence keep only a single copy of the code outside of the workbooks.
redjoy wrote:
I was not able to find out any thing about add-ins using the VBA behind a worksheet.
That's because you can't. An Add-In is written in C++, C#, VB.NET, or something else that generates "real code" and is completely seperate from any workbooks. I'd say your best bet is to write this as an Add-In to Excel.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Problem: Currently I have 12 Excel workbooks (each with 7 worksheets). The data is identical in nature. The common part is the VBA code. Every time I have to make a change to one of the worksheet's VBA code, I need to copy the changes to the other workbooks. I can use either the Excel buit-in VB editor, or any flavor of Visual Studio. I would like to be able to make the modification only once and have the workbooks reference the code instead of copying and pasting to each workbook. Question(s): 1. Is there a way that I can have one central location for the VBA code? 2. Would an Excel add-in be appropriate? 3. Should I program using VS (VSTO)? I am open to any approach to this problem.
Michael If we knew what it was we were doing, it would not be called research, would it? --Albert Einstein
modified on Friday, December 07, 2007 3:25:20 PM
I have my VBA script code in one XL file and then refer this file, from the file with the data. If you clear the data sheets leaving only one (empty sheet) and saves this file as say MyCode.xls Then open the VBA editor for this file, and goto project Explorer. rightclick on the the spreadsheet file, and choose properties. That should enable you to give you code a specific name, its practical if it could start with 'a'. If you then also open the spreadsheet containing only the data, again open the VBA editor, and in the project explorer select the data spreadsheet. Then goto "tools" and "references". This opens the ref's dialog. Here you should be able to locate your code among the many lines. (If the name of your code starts with 'a' it should sort to the top :) Check the box. That should make your code freely available from the datasheet. I use this approach from excel 2003, and the only downside I have found is that the references have absolute paths. Regrads *Henrik