Creating an Excel add-in, deploying it to users across a network.

Creating your first Excel add-in can be a daunting task, I put it off for a long time preferring to use modules in standalone workbooks, and the Personal.xlsx workbook. 

If you need a way to update and distribute your code in a managed semi-secure way then the add-in is the route to keep your sanity. I will discuss in another post the route I took to deciding to finally stop keeping all my code fragmented  and learn what I needed to learn to get things in one place. 

The best guide to making the add-in comes from a post on thespreadsheetguru.com step-by-step-instructions-create-first-excel-ribbon-vba-addin, there is no need for me or anyone else to rehash the advice there, it’s extremely good. 

There you will find a wonderful explanation of where to put your code, how to set up the labels, and even a download an example spreadsheet that will automatically set up your toolbar buttons that only needs you to name them and paste your code into modules. It’s pretty foolproof!

You can head over to www.microsoft.com to find a downloadable word document that will list all of the excel icons that can be used by default in your add-in. Be warned, it’s a tedious job to look through them all, and the names must be used exactly as shown. 

If you are looking to deploy this over a network for shared users then next step on the road to salvation is to save this add-in on a networked drive. You will want to keep your copy of the add-in on your local machine, the aim here is you will have a copy that is only on your machine that you can edit without anyone hitting the dreaded debug screen, and a stable copy that your users interact with. 

Your copy should be in a folder similar to;  C:\Users\IMTHEBUS\AppData\Roaming\Microsoft\AddIns

Your distribution copy would be on a shared drive that everyone has access, all the time. The best practice is to set it to Read Only, you can freely not do this, but anytime you need to make even the slightest change you will be forced to shout and call around the office to boot everyone off the system. 

I like Ken Puls code (below) from an older post on www.excelguru.ca, this will act as a deploy button that pushes your add-in from your machine out to the network location and keeps it as a read-only copy. 

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 = “N:\Dean” & 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

I link that snippet of code to a button labeled ‘Deploy’ in my add-in that is only set to be visible on my machine. 

whois = Environ(“UserName”)
If whois = “IMTHEBUS” Then           

Select Case control.ID   “aButton01”: MakeVisible = True             

End Select
End if  

Now whenever you make any changes to the local add-in on your machine, you press deploy and a copy is saved in the network location. As a rule, I update the ribbon name with a version number so i can instantly see if users have the most up to date version if they have a problem. I also keep a change log updated that can be called from the add-in so users can see what has been updated. 

Now you have it up and running on your machine, the only thing left to do is to set up the add-in on other machines on the network, and there is an extremely important step not to miss out.  

You will be asked if you want to copy the add-in to your personal add-in folder, you do NOT want to do this. Remember the only person who has a local copy of the add-in is you, all other machines need to reach out to the shared network drive to get a copy of the file each time they open Excel. By pressing No here you will make sure that every time the user opens a workbook, they fetch the most up to date version of the add-in.

If you have any questions or comments, feel free to let me know! 

The Excel cheat sheet mugs below will give you a head start to becoming a Spreadsheet wizard!