Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using common VBA code for a series of Woorkbooks.
I am tester in a group where we use Excel to generate datasets. As our
tests are sufficiently similar there is a lot of the VBA that can be reused between projects, and it is only necessary to change the message/tag format in a template version of the workbook when starting a new project. The functionality includes: - genereation of the tag structure. - insertion of randomised/default data in all fields, including min/max strings. - Upload of test data set to mainframe. - generation of expected result. - generation of SQL insert to staging DB. We usually generate a number of Workbooks for each project, in order to have a better overview and to be able to work more testers in parallel. Until now we have had the VBA in every Workbook. This is a rather sub-optimal solution when something happens that means the VBA must be corrected; we have to re-import the module(s) in all Workbooks. Of couse we are likely to miss some, and it takes time. Is there a clever way to have the code in a central place? As an add-in on a network drive? TIA Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using common VBA code for a series of Woorkbooks.
Create an add-in
http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 http://support.microsoft.com/default.aspx?kbid=156942 XL97: How to Create an Add-in File in Microsoft Excel 97 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Martin Sørerensen" wrote in message ups.com... I am tester in a group where we use Excel to generate datasets. As our tests are sufficiently similar there is a lot of the VBA that can be reused between projects, and it is only necessary to change the message/tag format in a template version of the workbook when starting a new project. The functionality includes: - genereation of the tag structure. - insertion of randomised/default data in all fields, including min/max strings. - Upload of test data set to mainframe. - generation of expected result. - generation of SQL insert to staging DB. We usually generate a number of Workbooks for each project, in order to have a better overview and to be able to work more testers in parallel. Until now we have had the VBA in every Workbook. This is a rather sub-optimal solution when something happens that means the VBA must be corrected; we have to re-import the module(s) in all Workbooks. Of couse we are likely to miss some, and it takes time. Is there a clever way to have the code in a central place? As an add-in on a network drive? TIA Martin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using common VBA code for a series of Woorkbooks.
Yes, an addin would be a good way. You would need to create a menu that
access the code in the addin as the subs won't be visible in Tools=Macro=Macros http://msdn.microsoft.com/library/en...n_addins97.asp Distributing Microsoft Excel 97, Word 97, and PowerPoint 97 Solutions (March 3, 1997) http://www.microsoft.com/exceldev/tips/addins.htm Protecting the Code in an Add-in http://msdn.microsoft.com/library/en.../xlcraddns.asp Creating Add-ins in Microsoft Excel 97 http://support.microsoft.com/?id=167909 XL: Securing Visual Basic Code in Microsoft Excel http://support.microsoft.com/?id=156942 XL97: How to Create an Add-in File in Microsoft Excel 97 -- Regards, Tom Ogilvy "Martin Sørerensen" wrote in message ups.com... I am tester in a group where we use Excel to generate datasets. As our tests are sufficiently similar there is a lot of the VBA that can be reused between projects, and it is only necessary to change the message/tag format in a template version of the workbook when starting a new project. The functionality includes: - genereation of the tag structure. - insertion of randomised/default data in all fields, including min/max strings. - Upload of test data set to mainframe. - generation of expected result. - generation of SQL insert to staging DB. We usually generate a number of Workbooks for each project, in order to have a better overview and to be able to work more testers in parallel. Until now we have had the VBA in every Workbook. This is a rather sub-optimal solution when something happens that means the VBA must be corrected; we have to re-import the module(s) in all Workbooks. Of couse we are likely to miss some, and it takes time. Is there a clever way to have the code in a central place? As an add-in on a network drive? TIA Martin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using common VBA code for a series of Woorkbooks.
Bob Phillips skrev: Create an add-in http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 http://support.microsoft.com/default.aspx?kbid=156942 XL97: How to Create an Add-in File in Microsoft Excel 97 Thanks, that looks promising. I have followed the instructions, now I have to change the existing workbooks to use the add-in. Any tips? BTW, we are using Excel 2003, some may still be on Excel 2000. best regards Martin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using common VBA code for a series of Woorkbooks.
It is hard to say Martin without knowing what the code does, and how it is
invoked. For instance, if you have menus, there is very little change, just make sure that you direct the code at the correct workbook (i.e. not ThisWorkbook, which is the add-in). If you are invoking the add-in code from the other workbooks, you need to look at using Application.Run Application.Run "'my add-in.xla'!myMacro" You may also want to look at application events (do a google), which creates events that can work on any workbook.worksheet. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Martin Sørensen" wrote in message oups.com... Bob Phillips skrev: Create an add-in http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 http://support.microsoft.com/default.aspx?kbid=156942 XL97: How to Create an Add-in File in Microsoft Excel 97 Thanks, that looks promising. I have followed the instructions, now I have to change the existing workbooks to use the add-in. Any tips? BTW, we are using Excel 2003, some may still be on Excel 2000. best regards Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
synchronizing two woorkbooks | Excel Worksheet Functions | |||
Scatter plot with two data series having common x-values | Charts and Charting in Excel | |||
plot 4 time series in a common graph | Excel Discussion (Misc queries) | |||
If I have two reoated data-sets and a common code, how can I get i | Excel Worksheet Functions | |||
dynamic range reference and use of common code | Excel Programming |