Using common VBA code for a series of Woorkbooks.

[=?iso-8859-1?q?Martin_S=F8rerensen?=]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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-us/dno97ta/html/msdn_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-us/dnexcel9/html/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

 

[=?iso-8859-1?q?Martin_S=F8rensen?=]

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

 

[Bob Phillips]

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)