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
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.
Dim HLink As Hyperlink
Dim strAddress As String
For Each HLink In ActiveDocument.Hyperlinks
strAddress = HLink.Address
HLink.TextToDisplay = strAddress
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 convertedin 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
- This opens the VB Editor (VBE).
When the VBE opens in this mannerthrough 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 therethen
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
- 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
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.
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
boxwith 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
- 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 filewhich could lead to
frustrations with some users, meaning they'll give up and never get to take
advantage of your wonderful codeyou 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.
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 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