How best to distribute VBA code?

[doasidont]

In Excel 2007, where many users will be using the same VBA macros, what's the best way to store the code? Would it be in an Add-In workbook? If so, under which doc&settings id should the add-in be saved? Thanks.

[westconn1]

if you have vb6 (or .net), i would suggest an activex dll

i would suggest a template directory or similar, even shared (public) user, for any addin, certainly not under any specific user

[doasidont]

Thanks. I'm having a little trouble understanding how to create and manage (i.e. update) the macros that are in an Add-In that I place in one of the shared public folders. Can you please give some more detail? Thanks.

[doasidont]

If I place the add-in in a folder (on a network drive) other than the user's "\Documents and Settings\user\Application Data\Microsoft\Addins" folder, everytime I restart Excel I get an error message stating that the add-in cannot be found. Can I disable this message, or can I store the add-in another folder where all users can see it (without having to copy it to every user's \Application Data\Microsoft\Addins\ folder?

In the above case, where I save the add-in in another folder on a network drive, the user can actually use the add-in, but Excel still tries to find the ad-in in the user's \Microsoft\Addins\ folder and presents the error message.

Thanks.

[doasidont]

Here's a different explanation of my problem. Let's say anyone can get an email that has an Excel workbook as an attachment. Is there a way that I can store some macros somewhere on the server so that these macros can be used, preferably by use of a button, against the attachment once it is opened, regardless of who got the email and opened the workbook? Thanks.

[cholland3403]

Try checking if the addin is installed upon opening the file. If not installed, install it.

In a module, place the following code:

Sub CheckAndInstall()
    Dim myAIName As String
    Dim fileLoc As String
    
    myAIName = "Addin Name"                 ' Enter as it appears in addin dialogue box
    fileLoc = "S:\Addin Location\Addin.xla" 'If everyone is not mapped to the same letter drive
                                            'do something like "\\servername\disk\Addin Location\Addin.xla"
    If Not CheckAddin(myAIName) Then
        Call InstallAddIn(fileLoc, myAIName) 'Check if installed
    ElseIf Application.AddIns(myAIName).Installed = False Then  'Check if loaded
        Application.AddIns(myAIName).Installed = True  'load addin
    End If
End Sub


Sub InstallAddIn(ByVal fileLoc As String, ByVal myAIName As String)
    Dim myAddin As Excel.AddIn
    
    Set myAddin = Application.AddIns.Add(Filename:=fileLoc)
    myAddin.Installed = True
    Application.AddIns(myAIName).Installed = True
End Sub

Function CheckAddin(myAIName As String) As Boolean
    Dim x As Variant
    
    On Error Resume Next
    x = AddIns(myAIName).Installed
    
    On Error GoTo 0
    If IsEmpty(x) Then
        CheckAddin = False
    Else
        CheckAddin = True
    End If
End Function

Then place this code in the workbook_open event

Private Sub Workbook_Open()
    Call CheckAndInstall
End Sub

This code should work, but I did not test it fully.

[cholland3403]

Alternatively, if you wanted to use this as a self contained module for later code, you could pass the values to CheckAndInstall like so:

Private Sub Workbook_Open()
    Call CheckAndInstall("Addin Name", "S:\Addin Location\Addin.xla")
End Sub

Then modify the module to accomodate

Sub CheckAndInstall(ByVal myAIName As String, ByVal fileLoc As String)
    If Not CheckAddin(myAIName) Then
        Call InstallAddIn(fileLoc, myAIName) 'Check if installed
    ElseIf Application.AddIns(myAIName).Installed = False Then  'Check if loaded
        Application.AddIns(myAIName).Installed = True  'load addin
    End If
End Sub


Sub InstallAddIn(ByVal fileLoc As String, ByVal myAIName As String)
    Dim myAddin As Excel.AddIn
    
    Set myAddin = Application.AddIns.Add(Filename:=fileLoc)
    myAddin.Installed = True
    Application.AddIns(myAIName).Installed = True
End Sub

Function CheckAddin(myAIName As String) As Boolean
    Dim x As Variant
    
    On Error Resume Next
    x = AddIns(myAIName).Installed
    
    On Error GoTo 0
    If IsEmpty(x) Then
        CheckAddin = False
    Else
        CheckAddin = True
    End If
End Function

[doasidont]

Thanks for suggestion. But it sounds like this open-event and other code would be installed in the emailed workbook. This email attachment will be arriving daily and it would be impractical to install the code into it every time it arrives. What I need to happen is: an email recipient opens (or saves somewhere) the attached .xlsx workbook and then, without modifying the workbook in any way, starts a predetermined macro via a button (or control-key sequence) that pulls data out of the workbook to create another report.

[doasidont]

More info. If I place the add-in in the \All Users.Windows\Documents\Add-Ins\ folder and open Excel with the /Automation switch, the add-in is available. But if I open Excel normally, it tries to find the add-in in the \user\Application Data\Microsoft\Addins\ folder and gives an error message 'Cannot find add-in ...'.

[cholland3403]

How is the workbook generated? Is the button that they press in a separate workbook, or the one that is emailed to them?

[doasidont]

The workbook comes in as an email attachment as a plain vanilla workbook, no buttons, no VBA modules or code. The user simply opens it by double clicking the email attachment which then launches Excel and opens the workbook. At that point, I want to allow the user to invoke a macro either by clicking on a button (on the Quick Access Toolbar) or doing a cntrl-key sequence. I would create the button on the QAT, linking it to a macro stored somewhere on the server but obviously not in the workbook that comes in via email because the email arrives daily. I've gotten this to work somewhat by adding the add-in to each user's \Application Data\Microsoft\Addins\ folder, but that becomes a code management headache because the macro code is not centrally located in one place, one occurrence (it would be in multiple locations depending on how many users would need the add-in). I'm not sure what I'm asking can be done since I think each user's Excel environment is unique. If every instance of an Excel launch, regardless of who launched it, could have access to the same add-in, then I think my problem would be solved. Thanks.

[cholland3403]

Here's the best I got for now. Maybe I will get a better idea after I sleep on it.

I would suggest storing the addin on the network and placing the above code in the Personal.xls file for each of the users that need the addin. This way, you only have to mess with each person's computer once and only have to modify one file when updates are needed. Your addin would then control the addition of the toolbar button.

You could take this one step further and create an excel file that will automatically copy the module to Personal.xls workbook. Then you would only need to send the "installation" file to new users and have them open the file. If you do this, instead of using the Workbook_Open event you would add this code to the module.

Sub Auto_Open()
    Call CheckAndInstall("Addin Name", "S:\Addin Location\Addin.xla")
End Sub

If you want to do this, check out this link (about 1/4 the way down) for copying modules to the Personal.xls workbook.

[doasidont]

Thanks. I think I understand your second scenario. Whenever I change the macro, I send out a workbook with the updated code to all users. When they open it, the code is automatically written to their Personal.xlsb workbook and the updated code is thereby installed. Right?

But on the first scenario, if the add-in is already installed and active, what causes the updated add-in to replace what's already there? Also, can you please restate the code that would be put into each Personal.xlsb?

[cholland3403]

You would only have to send out the workbook one time (initial setup for user). That workbook would copy the code I pasted above into the user's Personal.xls file. From that point on, every time the user opens excel (any excel file), it will check to see if your addin is installed, and if not, install it.

Whenever you want to make changes to the addin, simply update the addin and that is it. No sending out updated code. Your users will automatically have all the additional features you add available to them (as long as they are connected to the network).

[RobDog888]

As suggested, either if using VB6 or .NET, you should write an AddIn (dll) to distribute your code. It can be installed like a program also. You can add code to check for the latest version and let the user update it or not etc.

Check for options for deployment
http://www.vbforums.com/showthread.php?t=406621

[cholland3403]

Here is the file you could distribute. Just change the comment on the code page for cell A1 to include your addin name and file location.

Copy Module.zip


I have tested this and it works. It copies the code to my Personal file (you will need to change it to Personal.xlsb) and then automatically installs the addin from the network location. When I updated the addin and opened excel back up, I had the new features available to me. You will probably just have to update the addin when no one else is using it.

[AD_Taylor]

Here is the file you could distribute. Just change the comment on the code page for cell A1 to include your addin name and file location.

Copy Module.zip


I have tested this and it works. It copies the code to my Personal file (you will need to change it to Personal.xlsb) and then automatically installs the addin from the network location. When I updated the addin and opened excel back up, I had the new features available to me. You will probably just have to update the addin when no one else is using it.

I've been keeping tabs on this thread since I have an add-in that could really do with being distributed in the same way.

I really like the approach of using the initial set up sheet to copy code to the Personal workbook, so thanks for writing this and posting it.

As a follow up question, could some code be written so that if the user wants to uninstall the add-in, it would:

1. Delete the inserted code module from the Personal workbook.
2. Then uninstall any copy of the add-in left over.

Thanks again,
Adam

[cholland3403]

There are a couple of ways you could do this. In the addin, you could make use of the Addin_Uninstall event to delete module, that way they could just use the check/uncheck option in the Tools---> Addins section. You could send out a worksheet with the code to do it.

My personal preference would be to have the addin create a menu item that would include an uninstall option. This allows the user to do it whenever they want. When they select uninstall, it could then ask if they would like to just uninstall the addin or the addin and the code, or just the code.

To remove a module from the Personal.xls workbook use the following:

Sub RemoveModule()
    On Error Resume Next

    Dim VBCCount As Long
    Dim VBProj
    Dim VBMod
    Dim ErrMsg As String

    VBCCount = Workbooks("PERSONAL.XLS").VBProject.VBComponents.Count

    If Err.Number = 1004 Then GoTo AccessDenied

    If VBCCount = 0 Then
        MsgBox "Sorry, the VBA Project of the PERSONAL Workbook is protected."
        On Error GoTo 0
        Exit Sub
    End If

    Set VBMod = Workbooks("PERSONAL.XLS").VBProject.VBComponents("CheckAddin")
    If VBMod Is Nothing Then
        MsgBox "The specified VBA Project was not found in the PERSONAL workbook."
        Exit Sub
    End If
    
    On Error GoTo 0
    Workbooks("PERSONAL.XLS").VBProject.VBComponents.Remove VBMod
    MsgBox "The specified VBA Project was removed from the PERSONAL workbook."

    Exit Sub

AccessDenied:

    ErrMsg = "You must allow access to the Visual Basic Editor." & vbNewLine & vbNewLine
    ErrMsg = ErrMsg & "Please go to the Tools Menu at the top of Excel." & vbNewLine & vbNewLine
    ErrMsg = ErrMsg & "Select Macros, Security, then Trusted Sources." & vbNewLine & vbNewLine
    ErrMsg = ErrMsg & "Tick ""Trust access to Visual Basic Project."""
    MsgBox ErrMsg
    On Error GoTo 0
End Sub

To uninstall an addin, use:

Sub UninstallAddin()
    Dim myAIName As String
    Dim myAddin As Excel.AddIn
    
    myAIName = "Addin Name"  'Addin name as it appears in the addins dialogue box
    Set myAddin = Application.AddIns(myAIName)
    myAddin.Installed = False
End Sub

This will leave the item in the addins dialogue just incase they want to reinstall without having to search for the addin or use the workbook.

[AD_Taylor]

Looks good to me, I'll put something together to test and see how it goes.

Also I was having trouble with the code not working if the Personal workbook hadn't been previously shown, e.g. by trying to record a macro into it.

Is there some code that could be included to make sure it is accessible in the editor before hand?

[cholland3403]

Try using

Application.ScreenUpdating=False
Windows("Personal.xls").Visible = True

'''Other code here

Application.ScreenUpdating=True
Windows("Personal.xls").Visible = False

[doasidont]

Great stuff. Thanks for the help to everyone that contributed especially by cholland3403.