UGTS Document #55 - Last Modified: 8/29/2015 3:23 PM
VBA Deployment

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:

  • Access
  • Excel
  • MapPoint
  • Outlook
  • PowerPoint
  • Project
  • Visio
  • Word

VBA is not supported for Expression Web, Publisher, or OneNote.

Note: 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.
Start-up Files
  • 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: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Security\Trusted Locations. 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

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 walkthrough for doing this.

Best Practices

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.

Hazards

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 physical drive.
  • 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 those links.

To understand why this is happening requires us to understand three points.  First, Excel has a long and tortuous history of backwards compatibility requirements 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 path, the 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 outside process.