VBA is a variant of Visual Basic 6.0 which is built into certain Microsoft Office applications.
The VBA editor can be accessed via Alt+F11 from 8 MS Office Applications:
VBA is not supported for Expression Web, Publisher, or OneNote.
Automation and VBA support are two separate things. Nearly all
Microsoft applications support automation through COM, but only a handful of
them also support macros using VBA.
VBA code is usually stored in the Office document where it is created. An Office document becomes
analagous to a VB Project - it defines COM references, and a set of VB modules. Most of the VB 6.0 language
is supported by VBA. VBA is also optimized compared to VB in that it makes access to the MS Office application
easier by implicitly referencing the object model of the MS Office application. VBA 7.0 also defines 64-bit
integer and pointer types (which VB 6.0 does not) to make working on 64-bit machines easier.
Since VBA is stored by default in the document where it is created, it can be difficult to deploy
a common trusted VBA library to multiple users and machines which can be used across all documents without having
to deal with security warnings. There are a few solutions to this problem:
- Add-Ins - If the document containing the VBA library is saved as an add-in file, and deployed to the user's
MS Office Add-Ins directory, and the user 'installs' the Add-In by checking a box within the Office application (or
this is done for them in the registry), then the library will be available in all documents for that user.
- Startups - Word, Excel, and Outlook support certain specially named documents loaded automatically on startup. These documents
are treated as trusted based on the location they are in, and the VBA code in these documents
is available to all documents on the machine without security warnings.
- Excel - Excel has a list of trusted locations, which includes the XLSTART folder.
This folder is stored in two places: in [AppDataRoaming]\Microsoft\Excel\XLSTART, and in [ProgramFiles]\Microsoft Office\Office[N]\XLStart.
The full list of trusted location folders is stored in the registry in the subkeys of:
Any XLS or XLSB files in this folder get loaded automatically when Excel starts.
Historically, this was used for the PERSONAL.XLS/XLSB file, but now any file
can be created and used from here.
- Word / Outlook - Word has a Normal.dotm file which is
stored in [AppDataRoaming]\Microsoft\Templates\Normal.dotm. Outlook's
file is the same, but it is called NormalEmail.dotm in that same folder.
- PowerPoint / Visio / MapPoint / Project - none of
these have anything analogous - you would have to create an Add-In to
have a global VBA library for these applications.
For example in Excel, if you define a VBA function in a file in the
XLSTART folder, then you can use it by fully referencing the function call
with the filename, or by naming the VBA project within the file, and including
a COM reference to that named library. If you define the reference, then
no filename is needed (but the referenced file must exist in the same location
on all machines where your workbook is opened).
Suppose that you created a function
SqlQuote(string) in the module XSql in the XLSTART file UGTS.XLSB, and that this
VBA Project was named UGTSVBA. Then in your workbook, you could include a COM
reference to the UGTSVBA library, and then call SqlQuote directly. Furthermore,
there would be no security warning because the UGTS.XLSB file was loaded from
a trusted location.
You can also modify the list of trusted locations through Excel Options,
Trust Center, Trust Center Settings, Trusted Locations, Add New Location. If you
do so, all files in this location will be trusted. However, if you attempt
to add COM references to files outside the standard trusted locations, they
will add, but not be retained, so this is a fruitless exercise if you want
to store common library files outside of the standard trusted locations.
Add-Ins are treated much like startup documents, except that in addition
to being loaded from a special designated directory, they must also be 'installed'
(enabled) to be active. To install an Add-In, just save a workbook as an XLA
or XLAM file, and then save it to the Add-Ins directory, and enable the
add-in through the Trust Center for Excel. Here is a
for doing this.
To easily deploy a
specialized macro-enabled workbook to a user without security prompts, the
simplest way is to put the macro VBA code in the workbook itself, and then
have the end-user save the file to a location that they have added to their
trusted locations list. When the user then opens the file from that
location or a subfolder, it will automatically be trusted, and they won't
get any security warnings.
If there are multiple workbooks which all rely
on a common codebase on a central development machine or production server,
then save that common library to an XLAM file. Make sure to change the
project name of that workbook (in the Alt+F11 code view) to something unique
and distinctive (so that when you go to add a reference to it from other
workbooks, you can recognize the name of the library more easily. Then place
the workbook in the Add-Ins
folder (or somewhere else that you've configured Excel to load on startup), and add
references to the Add-In to the workbooks that need it (through Alt+F11,
Tools, References). Note - when adding references, sometimes Excel can
remove them silently from your project without warning, even when you save
your workbook. To make sure that a reference to another workbook
'sticks', open the 'library' workbook (the one you'll reference) first, then
open the client workbook (the one that will reference the library workbook),
add the reference from the client to the library, save and close the client
workbook, and then exit Excel. Then open the client workbook and
verify the reference is still there.
If you develop a common XLAM file to hold Excel
VBA functions, and get into the habit of copying and pasting code modules
from this common XLAM file to standalone XLSM files, you will encounter an
odd quirk / hazard in Excel. Excel likes to assume that if a function
is defined identically in both in the current file, and in an add-in file
that has been loaded (such as an XLA or XLAM), then you intended that
function call to point to the version in the XLA / XLAM file. It will
furthermore enforce this assumption by prepending the full path to that
XLA/XLAM file before the function call (even though you will only see the
function name and not the full path in the cell editor).
This behavior in
Excel makes it very difficult to have portable VBA libraries distributed in
XLSM files, because every time you (as a VBA developer) want to edit your
UDFs and copy/paste that code back to the top level XLA/XLAM file, Excel
will 'break' the portability of the file and fully qualify your function
calls with the full path to the XLA/XLAM file. Further aggravating the
issue, if the XLAM file is stored on a drive letter created with the SUBST
command, then you'll have one of the worst possible situations:
- Formulas will be silently updated to point to the XLAM file.
- Since this XLAM file is on a SUBST drive, Excel will convert the
path incorrectly to the physical drive letter, which will break the path
of the SUBST drive letter was mapped to a subdirectory of the actual
- The next time you open the XLSM file, all of your formulas will be
broken because the XLAM file is no longer reachable.
If this happens to you, you'll first have to fix the foreign links using
Bill Manville's FindLinks Excel add-in. Note that this add-in will
only catch links in cells. Links can also be found in conditional
formatting rules, so you'll have to check there too. If you think
you've searched everywhere but there are still links in the document, you
can save the Excel file to the XML Spreadsheet 2003 format and then open the
XML file in a text editor to search for the references. Once you know
where they are, you can open the original XLSM file and go there to fix
To understand why this is happening requires us to understand three
points. First, Excel has a long and tortuous history of backwards
even back to Excel version 1. Excel can only have a single file with a given basename open a time
because internally links store only the base filename and not the full path
of the linked file, and there is only a single workbook-wide mapping from
these base filenames to the full path where the outside linked file is
stored. The mapping is updated each time the workbook is saved or the
linked file is saved while both workbooks are open.
Second you must understand that if you have an XLA/XLAM file opened first
in Excel, and then open a workbook (XLSM) in that same process, and the XLSM
defines the same functions as the XLAM, then Excel will silently convert all
UDF functions to point to the XLAM file when you save the XLSM file.
In this way, Excel causes the UDF definitions in an XLA/XLAM file to
'override' anything defined in a workbook.
Finally, when the file is saved and the base filename is mapped to a full
code which gets the full path to the workbook file doesn't handle SUBST
drive letters correctly, causing the links to the XLA/XLAM file to be
broken. They can also become broken as a matter of course if the user
takes the XLSM file and gives it to another user, and that user does not
have the XLA file, or the XLA file is in a different location.
Now that we understand how this happens, how can it be prevented?
There are three primary methods:
- Store XLA/XLAM files on the network. If the
full path is a UNC path, then it will be the same for everyone and links
won't get broken. This enables a centrally updated VBA library,
but it also breaks portability - files using the library don't work
outside the internal network. This is a pain when you use a laptop
and are not connected to the network, or when you try to send the file
to an outside recipient. The first case can be mitigated by using
offline files - Windows will provide a cached copy of the network
library file even when you are disconnected from the network so that the
file still runs. But, outside recipients still won't be able to
use the file.
- Don't put library UDFs in XLA/XLAM files. Since
Excel only 'mangles' UDF paths when overridden by a pre-loaded XLA/XLAM
file, put your common code in simple XLSM files instead, and choose
function names which all share a common prefix that is unlikely to be
used by any other developer. This keeps your files fully portable,
but it also makes it more tedious to synchronize your library code
between the individual files and the common XLSM library file.
- Install XLA/XLAM files to an invariant path. Install
the XLA/XLAM files explicitly to an exact path on the C: drive such as "C:\Program
Files\MyAddIn\MyAddIn.xla" and install this add-in to that path on
every machine which will use the Excel files. This solution is
really worse than both of the two previous solutions. Files are
neither fully portable because they require an add-in to be installed to
be usable, and there is no centrally maintained library because every
machine has its own copy of the library which must be updated by some