Sharing VBA modules across MS Office Applications

问题:

I have a substantial bank of VBA modules written in an Excel 2010 add-in. Some of these are specific to Excel, but many are more general. For example one takes a part number and re-formats it; another contains a Case Select function to find a file in a network drive.

I want to use the common functions in Word and Outlook. I could copy and paste from the Excel to Word add-ins, but this makes it difficult to keep my code up to date - when I make an edit in one application, I must remember to copy to all the others.

My question is, is there any means of writing common code in one place (e.g. in the Excel add-in, or some other common location) so that all MS Office applications can access it as it if it's just another module?

回答1:

The way this used to be done was to take your code and compile it into a COM (or ActiveX) DLL using Visual Basic 6. Then you could add that DLL, using the VBA editor's "Tools...References" dialog, from any Office (or other) product that supported VBA, the same way you might use, say, the Microsoft Scripting Runtime, which is super-handy for things like Dictionary, FileSystemObject and TextStream.

Problem is, VB6 was released sometime in 1998 and has not been available from or supported by Microsoft for years now. There seem to be quite a few download sites offering the package - I can't offer any advice about the legality or security issues that might be experienced by using them...

Shamefully, Microsoft dropped the VBA ball years ago - it seems they mostly wish it would just Go Away.

回答2:

You would need to create a COM add-in (.dll) for that, which would require Visual Studio or some other tool capable of creating COM exposed addins. There hasn't been any facility for this in Office since the old Office Developer edition.

回答3:

VB6 is best way. VB6 is still supported for compiled programs. The IDE been tested and found to work up to Windows 10 (32 bit only) by MS but is unsupported.

If you want to convert to vbscript you can use wsc files instead of a dll.

From Script Components Overview

Windows® Script Components are an exciting new technology that allows you to create powerful, reusable COM components with easy-to-use scripting languages such as Microsoft® Visual Basic® Scripting Edition (VBScript) and Microsoft® JScript®.

Make one, make a type library, set a reference to typelibrary in Word and Excel.