Introduction

The purpose of this article is to provide corporate developers with a method to release, maintain and update an add-in in a network environment. This is based upon my strategies for doing this same.

Assumptions made in this article are:

• You are a developer of some caliber, understanding enough VBA to create your own routines.
• You know that your add-in routines should be targeted at the ActiveWorkbook object primarily, but may refer to ThisWorkbook on occasion. (Not the other way around.)
• You have a network environment to work with.
• You need to develop your add-in, publicly release it (within your company), and maintain/upgrade it in future.

Installing an add-in in a Standalone Environment

Installing an add-in in a standalone environment is easy. You pretty much make sure that all of your code is geared to run on "ActiveWorkbook" instead of "ThisWorkbook", give it a menu structure, if you so desire, and save it as an .xlam file. Upon doing so, you immediately reap the following benefits:

• You've got a code library that you can snap in/out of excel whenever you desire.
• It's far more portable than the personal.xlsb file.
• You can "secure" the project, which pretty much just means that you don't have to see the modules all pop up every time you enter the VBE. (A VBProject password can easily be stripped, so don't think of it as true security.)

But what if you work in a network environment? What if you want all your users to work from the same add-in and get the benefit of the "single update" method to coding?

Strategies for installing an add-in in a Network Environment

This is actually pretty easy, in truth, but there's a couple of "gotchas" that you want to be on the lookout for. The following is the strategy that I use for deploying and maintaining my network add-ins. We're going to go through all these steps in detail, but a summary to begin with.

• Create the add-in on your local PC
• Program a routine to update the add-in from your local copy and save it to the network drive
• Remove any previously badly set up add-ins if necessary
• Install the add-in for your users

Create the add-in on your local PC

It is a best practice to keep your development copy and live version separate. To this end, we'll create our add-in on our local PC, and the "live" version on the network.

The initial benefits that you can expect to see from this approach are:

• Some minor performance benefits due to running locally versus across the network.
• An isolated environment to test and debug your application in, without having to worry about some user opening your file.

The bigger benefits, however, are realized once you've released your add-in to the general corporate public; You can work on your development copy without affecting the live copy. This allows you to:

• Track down bugs by trial and error testing, if necessary,
• Add/modify/delete code,
• Create/enhance or completely revamp features,

.. all without having to worry about a user who may stumble into a broken procedure as you are building or testing.

So the first step in our installation process is therefore to convert our xlsm file to a local xlam file on our development PC. Simply complete the xlsm file as far as you are able, set up a custom Ribbon structure if you desire, and save it as an xlam file.

Create a procedure to save the add-in to the network drive

With many, if not all, programs, the first time a file is opened, it is locked by the user who opened it. Excel is no exception. The first user in gets full command of the file. It cannot be edited or overwritten so long as that user still has control of it. It is therefore extremely important that you save your network version as read only.

"Now hang on," you may say, "I've shared an add-in without marking it read only!". It's true that you can. Unlike regular workbooks, they give no warning when one or more subsequent users open the file. Excel is designed to read a copy of the add-in into memory for you if someone already has it locked. From a day to day processing perspective, there is no difference to the end user.

But we've already identified that you are a developer, or you wouldn't be reading this. Chances are then pretty good that you'll want to update your add-in at some point, and Murphy's law will dictate that you'll want to do that when a user has your add-in open. So long as you have not saved the network version of your add-in as read only, the first user that opens it prevents you from saving over the file. If you have multiple users working from that file, you'll need to track down who it is, kick them out, and get that file saved before the next person jumps in.

True, there are ways to find out which user has the file open. Ivan F Moala has an awesome example on his site of how to find out who has the file open. I used that method for a long time, just phoning the person and kicking them out, but the read-only method is superior as the phone call becomes unnecessary. It also takes a whole lot less code.

The following routine is a variant of a procedure that I use to save my development add-in file as a new, or over the existing, network add-in. For the purposes of this demonstration, we'll assume that the drive you wish to use for your network add-ins is "F:\Addins". I would suggest that you modify the routine by moving the strAddinDevelopmentPath variable to a module level Private variable which holds the actual path to your addin, but felt that this code would be enough to get you started with the method.

Sub DeployAddIn()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To deploy finished/updated add-in to a network
'               location as a read only file
    Dim strAddinDevelopmentPath As String
    Dim strAddinPublicPath As String

    'Set development and public paths
    strAddinDevelopmentPath = ThisWorkbook.Path & Application.PathSeparator
    strAddinPublicPath  = "F:\Addins" & Application.PathSeparator

    'Turn off alert regarding overwriting existing files
    Application.DisplayAlerts = False

    'Save the add-in
    With ThisWorkbook
        'Save to ensure work is okay in case of a crash
        .Save

        'Save read only copy to the network (remove read only property
        'save the file and reapply the read only status)
        On Error Resume Next
        SetAttr strAddinPublicPath & .Name, vbNormal
        On Error Goto 0
        .SaveCopyAs Filename:=strAddinPublicPath  & .Name
        SetAttr strAddinPublicPath & .Name, vbReadOnly
    End With

    'Resume alerts
    Application.DisplayAlerts = True
End Sub

Providing that this is the first time you have saved this add-in to the network, you can skip the next step. If you've already deployed your add-in once before, though, please read the next step and make sure it is set up correctly.

Remove badly set up add-ins if necessary

If you have previously installed the add-in on the network, and copied the add-in to local folders for your users, you will want to remove the prior installation. You should only need to do this once, as setting up in the manner I'm describing will negate the necessity of this step in future. For the purpose of this example, assume your add-in is called "Myaddin.xlam", which was installed to the local add-in folder on each client's machine.

To remove the bad install, on each client machine, do this:

Remember! Only do this on the client machines, not your development PC!

• Open Excel and make sure that your old version of the add-in is loaded
• Open the VBE, and enter the following code in the immediate window:

  ? Workbooks("Myaddin.xlam").FullPath

• Take note of the path
• Close the VBE, and deactivate the add-in (Developer -> Excel Add-ins -> Uncheck the box beside "Myaddin.xlam")
• Close Excel and open Windows Explorer
• Navigate to the path that you took note of, and delete the "Myaddin.xlam" file (you may want to make a backup first)
• Open Excel
• Try to install the add-in again (Developer -> Excel Add-ins -> Check the box beside "Myaddin.xlam")
• You should see the following error:
  
• Click okay, close the add-ins window
• Close Excel - do not attempt to install the new version now!
• Re-open Excel, and you're ready to install the new version

It is very important that Excel gets closed between uninstalling and installing a new add-in with the same name. The closure updates the registry keys associated with the add-in and actually removes the old path from the registry. Failure to close will leave you with a path still pointing to the old file (which no longer exists!)

It seems like a lot of steps, but it's actually quite easy, and again, only needs to be done once for each client.

Install the add-in for your users

Now that the add-in has been copied to the network, and now that we know that no hangover installs will get in our way, we can install the add-in for our users. The installation process is similar to installing a local add-in, but with one important difference.
The steps to install an add-in in a network environment are:

• Open Excel
• From the Developer tab choose Excel Add-ins
• Click Browse and browse to the "F:\Addins" directory
• Double click your add-in
• You may now see the following question:
  • 2000-2003 versions:
    
  • 97 version:
    

  Regardless of the exact verbiage, if you get prompted with this question, the answer is NO. You are not interested in copying it to the local Excel add-in folder or library.

• Once returned to the add-ins manager, make sure the box beside "Myaddin.xlam" is checked and say okay

Congratulations! You now have an add-in that is running off the read-only network installed version.

Final note

In future, you'll only need to update the development copy and run the routine to update your add-in. Because all users are using the read-only copy of the file on the network, you'll never have an issue overwriting it, and they will always get the most recent copy when they restart Excel. Deploying to a new user is as easy as installing a stand alone add-in as well, with the only critical piece to remember being to say no to storing a local copy.