CODEWITHSUNDEEP
vbaexcelms-officems-word
pa1983
pa1983

Reputation: 274

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?

Upvotes: 2

Views: 1378

Answers (4)

Antonio Martos Montoya
Antonio Martos Montoya

Reputation: 1

Maybe can help somebody.

My solution has been set conditional compiler arguments in project properties:

  • In excel project: SOFT_EXCEL = 1 : SOFT_OUTLOOK = 0

  • In outlook project: SOFT_EXCEL = 0 : SOFT_OUTLOOK = 1

Then, in module:

Public Sub as_email()
    #If SOFT_EXCEL Then
        Debug.Print "this executes and compiles in excel"
    #End If
    #If SOFT_OUTLOOK Then
        Debug.Print "this executes and compiles in outlook"
    #End If
End Sub

Upvotes: 0

user4232746
user4232746

Reputation:

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.

Upvotes: 1

Mike Woodhouse
Mike Woodhouse

Reputation: 52326

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.

Upvotes: 2

Rory
Rory

Reputation: 34075

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.

Upvotes: 1

Related Questions