Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deploying a VBA-macro

I created a VBA-macro which will be used by some word-documents within my company. The macro detects tags and removes chapters from a document. This document is created by another program. So the macro should be separately distributable.

Is it possible to generate an executable which adds the macro to the user running the executable?

Is there another way to package macros and install them on a user's computer?

Thanks

like image 687
keevw Avatar asked Apr 21 '15 12:04

keevw


People also ask

How do I create a macro in Excel VBA?

You can create a macro by writing the code in the VBA editor. In this chapter, you will learn where and how to write the code for a macro. Before you start coding for a Macro, understand the VBA Objects and Modules. Open the macro-enabled workbook with your first macro.

How do I deploy a macro?

The easiest way to deploy Macros is via a template. Create your Macro and save the file as .dotm (macro-enabled template). I think you will get a suggestion where to save your .dotm-file.

Can outlook VBA macros be distributed?

Unlike other Microsoft Office programs, Outlook supports only one VBA project at a time. VBA macros are stored in a file that's named VbaProject.OTM. This file is a product storage file and isn't meant for distribution. Outlook doesn't provide a direct means to manage OTM files. Outlook VBA code wasn't designed to be deployed or distributed.

How do I call a macro from a VBA program?

You can also use Application.Run to call a macro. By using Application.Run you call a second macro, but tell the first macro to continuing running. Stop searching for VBA code online.


3 Answers

The easiest way to deploy Macros is via a template. Create your Macro and save the file as .dotm (macro-enabled template). I think you will get a suggestion where to save your .dotm-file.

Any colleague who wants to use your template simply has to put it in that directory (I think it's C:\Users\[UserName]\AppData\Roaming\Microsoft\Templates). After that, he should be able to use the macros while working on any word document.

like image 146
EngJon Avatar answered Sep 30 '22 16:09

EngJon


There are basically five ways to do this:

1) Send everyone a text file with your macro that they can paste into their own Normal template. This is fine for very simple macros that are unlikely to have any name conflicts with macros users create themselves, but it does require basic knowledge of the VB editor.

2) Send everyone a .bas file that you create by exporting a module that contains your macro(s). This gives you a little more control and avoids copy/paste errors. Still requires basic understanding of the VB editor (or decent instructions from you).

3) Package your macros in a template (.dotm file) that lives in the Templates folder. Users can apply that template to any document they're creating and gain access to your macro(s). No VB knowledge required; this is done through the standard Word New File process. Also allows you to include styles or other things if you want.

4) Package your macros as a global template (.dotm file) that lives in the Startup folder. Users will have access to your macro(s) in every file they work on, no need to apply your template. This is good if what you are doing is central to your team's workflow and doesn't require that you include styles with your macro. You can also build in UI elements. (There can be issues with this approach in Word 2011; users may not have immediate access to the global template but it is easy enough to get back.)

Both 3 and 4 do require that the user initially place the .dotm file in the right place. You can help them with this (one approach is to use another Word doc as a "setup" file that, when run, places the template in the presumed correct folder). Obviously that requires more work on your part so how far you'd want to go with that depends on you and your business needs.

5) Additionally, if you have control over the creation of the document itself (rather than just the macro) you can embed a macro in the document. You can place the macro itself in the document's ThisDocument module (find your document in the Project Explorer and then open Microsoft Word Objects). Then save the document as .docm (macro-enabled document). Users should be instructed to enable macros when they open the document (different versions of Word use slightly different interfaces for prompting the user about this, but it's always pretty obvious).

like image 20
Christina Avatar answered Sep 30 '22 15:09

Christina


Over the last 7 years I have been deploying my Word VBA in a different way. The software is a Word add-in that makes it easier for teachers to provide feedback on assignments. It is distributed as a 30 day trial and if the user buys it they are given a key which enables them to use eMarking Assistant for a year. You can test the deployment system at http://eMarkingAssistant.com

The deployment and licensing mechanism is given below:

  1. save the vba in a macro enabled document i.e. a .docm file
  2. in Windows rename the file to be a .doc file
  3. use Orlando's excellent "VBA decompiler and compacter" from http://orlando.mvps.org/VBADecompilerMore.asp to remove compiled code and references to specific Office versions from the .doc and compact the document
  4. ask the user to download the .doc file
  5. ask the user to open the .doc file and ensure that macros are enabled
  6. let the user trial the software in the document
  7. if they want to use the software in any document they click an "install" button in the document to copy the vba code to to a .dotm file in their Word startup folder (so it is loaded automagically)
  8. if they want to buy a subscription to use the software, they pay using paypal and I send them a key which unlocks the software until the end of the subscription

Advantages of this process are:

  1. a single document can be used on all versions of Office for Windows from Office 97 to Office 2016 (32 bit and 64 bit).
  2. the install and uninstall all happen within Office so the suer does not need to admin rights over their computer
  3. users do not need to install the software until they have used it in the document
  4. users do not need to use another program to unzip or install the software

Peter Evans

like image 42
Peter Evans Avatar answered Sep 30 '22 15:09

Peter Evans