Sharing Macros

Folks often ask about the various techniques for sharing macros. In this article, I'll show you a bunch of ways that you can accomplish passing your favorite macros around to your colleagues. Then you can decide which way is the best for your needs and office user skill level. I'll also give you a few tips to help you enhance the end user's experience when they get around to using your macros.

What a Macro Can Do for You
First let's get acquainted with our macro for this demonstration. Our assistant for this article will be a stylish and useful, little macro called ConvertHLink. ConvertHLink will be assisting us in this article to show the many ways it can get around. First let's look at the ConvertHLink code.

Sub ConvertHLink()    
    Dim HLink As Hyperlink 
    Dim strAddress As String

    For Each HLink In ActiveDocument.Hyperlinks
        strAddress = HLink.Address
        HLink.TextToDisplay = strAddress
    Next HLink
End Sub

This is a subprocedure (macro) that when run, will go through your entire open document. Every time it finds a hyperlink in said document, it'll capture the actual URL address of the hyperlink and sets the display text to the URL address it captured from the link. A handy conversion tool!

Say you had a document with some hyperlinks in it. The creator of the document highlighted various chunks of text in the document and hit Ctrl/K to add a hyperlink field to the highlighted text. Like a web page, you can now click the hyperlinked text to go to the web page. But you can't actually see the web page address, or URL, unless you convert the hyperlink to a field code. And what if you needed to convert the document to a text file for some reason? The link would be lost and you'd be left without the visible URL. This is what a user recently encountered. Her boss asked her to go through several long documents, swapping the highlighted text for the actual URL address. There had to be a better way, right? I gave her this macro and now she could run this macro against these documents and have all the links converted—in a nanosecond.

How to Make Code You Receive Work on Your Computer
I passed the above code to her in an email. But now what should she do with this cryptic email information to make it work on her computer? She needs to copy it and add it to her Normal.dot (master template) in Word as a macro. To do this, she can follow either of the two methods below.

Accessing the VB Editor via the Menu

• Click Tools/Macro/Macro.
  
  
• When the macro dialog box opens as shown below, she will enter the name of a new macro. In this case it's called ConvertHLink. Then, after making sure that the Macros in: dropdown shows the correct location in which to store this macro, such as the Normal.dot (global template), so it'll be available to her whenever she is within Word, she can then click Create.
  
  
  
  
• This opens the VB Editor (VBE).
  
  
  
  When the VBE opens in this manner—through the Tools/Macro/Macro menu, she will automatically be located in a module. And because she selected the Normal.dot template already, the NewMacros module is the default. Because she already gave the macro a name, some basic information will already be entered for her, such as the opening and closing code for the macro, as shown in the image above.
  
  
• Now she should be able to just copy in the code and all is finished, right? Well, not quite. If she simply pastes in the code from the email, look what she'll get in the image below.
  
  
  
  I've colorized the image a bit so you can more easily see the problem. Notice the two pink arrows are pointing at the text called Sub ConvertHLink( ) and End Sub. Now notice that the green arrows are pointing at the text called Sub ConvertHLink( ) and End Sub. Sound familiar?
  
  If she attempted to run this code now, she would receive the error shown in the message box below, which says:
  
  Compile error: Ambiguous name detected: ConvertHLink.
  
  
  
  
• This means that you have two macros or subprocedures using the same name of ConvertHLink contained within the same code module. So she'll need to do a little clean up, first.
  
  It doesn't matter which set of beginning and ending code she removes. In the sample image below, you can see she has removed one set and she has also modified the comments to provide her with more accurate information about where she acquired the code and what it does. Any line with an apostrophe in front of it will not be read by the VB engine, so you can use this method to add reminder notes within your code.
  
  
  
  
• Now she can close the VBE and run this macro against any document to have the hyperlinks converted from text hyperlinks to actual URL hyperlinks.

Accessing the VB Editor via the Alt/F11 Shortcut
She could have done this a little faster if she knew how. Rather than going through the Tools/Macro/Macro menu and having to remove the autogenerated macro text that was entered, she could have popped into the VBE by just hitting Alt/F11. However, if you don't know what you're doing in the VBE, that way can be very confusing to a code newbie.

Take a look at the image below and look at the mess you could walk into if you were to hit Alt/F11 on my laptop! Forms, modules, code, projects! Where would you put your new code?

Or, what if you never created a macro before? If that were the case and you entered through Alt/F11, you wouldn't have any modules in there—then what?

Well, if you spend the time to get more familiar with the VBE, you'd learn to organize your modules and projects and be more familiar with this environment. But the best bet, when you're unfamiliar with this environment, would be:

• Locate the NewMacros default module. If you don't have any modules yet, click Insert/Module and add one. You can rename it to NewMacros or leave it with the default name of Module1.
  
  
  • To rename a module, highlight it, click in the Name input box in the Properties box below the Project Files and give the module a new name. See the bottom of the above image for reference to this location.
    
    
• Double click a module to open it.
  
  
• Go to the end of whatever code might already be there (if you had macros already) and paste in the new code.
  
  
• A new separator line will be inserted, automatically, when you paste in the new procedure, as shown in the image below.
  
  

Passing a Macro to Others
Okay, so now our user has this really cool macro that will do the conversion work for her and she was able to successfully insert the code to work on her computer. She would like to share this gem with some of her colleagues. What's the best way to go about doing this? Well, it depends on your colleagues and how macro savvy they are. If she emailed it to them, would they know what to do with the information or would she have to spend a day running around helping everyone else install it? Would they know what to do if she passed it in a template? What about sending them a code module? Right! I can hear the screams coming down the hallway from here!

Of course, she can always pass them the email code and include a link to this article and tell them to read it! Might be too much to hope for, eh? Well, just in case she has colleagues who actually enjoy learning, I'll details some ideas below and she can decide how savvy her colleagues are to choose the best method.

We've already covered the directions for inserting the macro code directly into a module, so let's look at alternative ways to share.

Passing Code With a Module
One alternative would be to create a custom code module and pass the module to others. When they receive it, they would insert that module into their Normal.dot.

To do this, you'd just have to click Insert/Module to add a new module. Name it whatever you want through the Properties box. Copy the code from your own module and paste it into this new module. Then you can click File/Export and export out the code module. It will be exported as a .bas file, as shown in the image below.

Pay attention to where you save the file! Then you can email it. When the recipient receives it (assuming they have their email program set to allow them to receive a code module), they would save the file off to some directory. They would then open Word, hit Alt/F11 to enter the VBE. Being sure to highlight the proper template project, such as Normal.dot, they could then click File/Import, locate the file on their hard drive and add the code module to their computer.

They would then see the macro available to run when they click Tools/Macro/Macro or when they click Tools/Customize/Macros, if they wanted to add the macro to a custom toolbar, hotkey combination or menu. If you need help customizing a menu or toolbar, see this link: www.mousetrax.com/toolbars.html.

Passing Code With a Template
Passing macros in a module is usually how developers do it, but it can be a little intimidating to those folks without much macro experience. A more friendly way to do it is via a template.

To use this method, you start by clicking File/New, choosing the Blank (default) document, but making sure to choose the template option to create a new template from the general template dialog box versus creating just a new document. See the image below to locate this option button.

Note! Although you can pass macros in a document in later versions of Word, it's usually best to stick with a template to make life less confusing.

Now you can use the Organizer to pass macros or modules over to your new template and use this as a transport template. Click Tools/Macro/Organizer to open the Organizer dialog box as shown below. Notice that you can also use this dialog box to share Styles, AutoText and Toolbars, as well as Macro code. Just choose the appropriate tab and pass over whatever you want to add to this sharing template.

If you happen to have other templates with other code modules you want to share, you can click the Close File button to close the current template (normal.dot in this case) and open any other file that contains items you want to share. Highlight the item and click Copy to pass it to the new template. Note that you can also use this dialog to easily Delete or Rename items. When finished, click Close.

Now the items you want to share are contained inside this new template. Save it.

Now What? More choices, What Else!
You've successfully created a new template and loaded it with all the goodies you want to share. What do you do next? Again, this will have to be up to your best judgment, so I'll lay out your choices. Then you can decide what your colleagues can handle. And as I said, you can always just point them to this article and let them decide what method to use.

• Pass the template to users and tell them to copy it back via the Organizer.
  You can email the newly loaded template to folks with directions to tell them how to open the Tools/Macro/Organizer dialog, click on the Macro tab, make sure the new template is in one project side of the dialog and their Normal.dot is on the other. Note that this should be the default layout if they just open the template. Then highlight and copy the code, styles, autotext or toolbars over to their own Normal.dot default template. This will make the code available on their system.
  
  
• Load the template in their Startup folder.
  Just like Windows has a Startup folder, where applications can be inserted to automatically startup when Windows starts, Office and some of it's applications have their own Startup folders. For Word, you'll find a ...word/startup directory somewhere on your hard drive. The real trick here may be finding the path. You can try searching for Startup. It's most likely in the ...application data/microsoft/word/startup directory. But then again, if your admin has blocked or hidden that path, you may not be able to easily find it. Likewise, some Windows versions come this way out of the box—with file extensions turned off and some folders, such as the Application Data folder, hidden. If you can't find the path in a search, you will most likely have to first go into your Windows Explorer to reset some of the default folder and file viewing options.
  
  However, once you find the path, simply save the template in the ...word/startup folder. Word will check that folder and any templates (dot files) it finds in there will be referenced as global templates so that any styles, autotext, toolbars or macro code modules contained within them will be available to you when you open the appropriate dialog box within Word, nearly the same as if they were contained within your Normal.dot.
  
  Note! This path can also become a problem if the code you wrote is causing Word to have troubles opening or closing! It's common for third party applications to install their custom templates in the Startup directory. If the templates are outdated and no longer will work with your newer version of Office, this can cause problems. So a common troubleshooting technique is to start Word with the /a switch. This stops all addins from being loaded and would thereby stop your template from loading it's macros. I always tell my users to either rename the dot file to [whatever].OLD or move it out temporarily to find the troublemaker. But I know some support folks suggest just deleting any files in there. NOT WISE ADVICE! It's best to rename until you understand the problem better and then contact the creator for help!
  
  
• Place the template in a shared network folder.
  Rather than emailing the template around the office and making each user stumble around to find the correct place to save the file—which could lead to frustrations with some users, meaning they'll give up and never get to take advantage of your wonderful code—you might consider dedicating a network drive as a shared workgroup folder. Then you can place the template in that shared folder. If your netAdmin was wise enough to do this already, you're done with your work. Just let folks know the new code will now be available to them. When they open Word, Word will see the new global template in the workgroup template folder and you can all get back to your work.
  
  If your admin hasn't set up a path like this for you, once you get them to do it, you'll have to let all the users know to click Tools/Options/File Locations. Select the Workgroup Template option in the dialog and click Modify and point to this new path. Then when they restart Word, they will be able to access the new code contained in your template that now resides in that directory.
  
  A shared group folder is really a great way to do it. This way, when it's time to share new code, you just add new code to the master template and pass it into the shared folder to update the old one. Users will not have to do a thing! Then you can email them to let them know the name of the new code so those who are excited about it can add it to an easily accessible menu or toolbar. See www.mousetrax.com/toolbars.html if you or they need help learning how to do this.

Enhancement Advice
You have gathered your solution VBA code, inserted it into your own computer and, using one of the above methods, you've been able to share the template with others. Great! And that's really all there is to it. However, as a designer, developer and user of this type of stuff, let me pass along a few other tips that will make you the star in the eyes of your colleagues and will only take a little more effort on your part. They will love you for this added help.

Have you ever received a template from someone with no instructions explaining what to do with it? Frustrating, isn't it! So think about your users and give them some help. Here are two ways you can greatly enhance your custom template, making the users more likely to take advantage of your code.

• Use that White Space!
  Since your template, in this case, is basically just a container to transport your custom tools to others, there's no need to have any information typed on the page of the template. But why waste all that empty space? Wouldn't it be nicer to fill it up with some instructions? You know they're going to lose the email you sent them with details about how to use the macros. So break out the safety pins and pin the instructions right to the template by typing them right onto the page.
  
  Think of how much better it would be to open a template that looks like the image below, with a title and info about what macros are contained, who created them and how to use them, versus a blank page!
  
  
  
  
• Create a Customized Menu
  Another great enhancement you can add for your users is a customized menu. Toolbars can be great, but they're not as easily recognized as a new menu item. After your custom template has been loaded up with code, click Tools/Customize. This opens the customization dialog. Go to the Command tab and scroll down the left until you find New Menu.
  
  I can now click and hold the new menu item and drag it up to the Menu Bar and then add whatever macros I want. See the animated gif image below to see just how it's done!
  
  Note! If the animation has stopped, hit the F5, refresh, key in your browser to start it up again.
  
  
  
  Also, look at the bottom of the image above and notice that I've made sure that these customizations are being done to the DianDotSample.dot and not the default Normal.dot template.
  
  When the New Menu has been dragged to the menu bar, leave the Customize dialog box displayed to stay in this mode, then you can right click the new menu and you'll see other options. Click in the Name option to change the name from New Menu to something your users will notice, like: Our Company Tools. You can also choose the Begin a Group option. This will add a vertical bar on the menu so it will more easily be recognized as a separate entity. And adding it to the end of the Menu Bar is usually better than mixing it up somewhere within the standard menu items.
  
  Since the template is loaded in the user's ...word/startup directory, it'll be loaded when they open Word. And so will this new menu to allow them to easily access all your cool tools. If you update the code and add to this custom menu and then update the template, when they open Word the next time, this menu will display the new macros. You can even add a group within the menu to section off groups of macros.
  
  As you can see in the image below, anyone who gets my sample template can easily access my macros.
  
  
  
  Granted, because I have several code modules, I've left the full name so I know where to find each macro. But if you have all the code contained within one module, you can modify the names on the menu to something more descriptive and simple.
  
  And you can add an ampersand in front of the letter you want to become the hotkey. This adds an underline under the hotkey letter, so users can access the menu via the keyboard. I didn't bother for my own macros, but it's an important consideration for users. Not everyone likes to reach for the mouse and blind users need to use the keyboard!

Take the time to make your customized code sharing templates more user friendly. I guarantee, your user's will be impressed and appreciative!

Need more help getting your VBA code working right? Join our free VBA support groups. See these links for details: http://groups.yahoo.com/group/Word_VBA/ and/or http://groups.yahoo.com/group/ExcelVBA/.