Distribute your VBA project to other Microsoft Users

We often spend a lot of time to write VBA code to assist us in completing tasks in Microsoft Applications whether an Excel spreadsheet, Access database, Outlook Message, Word Document. This code can be used by other users on the same scenario. We can make our code available to other users.

This code can be protected to ensure integrity.

The procedure below describe how to create distributed software for an Excel application. The process for other Microsoft applications will be similar.


1. Once you have completed, debugged and tested your program and are happy that it works, protect      your code as follows:

• Right click on your module or project name and select VBAProject Properties. Assume your sub name is "ProtectedCode"
• In the General Tab type in the name of your project e.g. "CodeForDistribution".
• In the Protection Tab select Lock project for viewing  and enter a password for your project.
• Click on OK.

2. Save your project but select the file type as .xlam.

• This will save your project with an .xlam extension (this is an Add-In file type) in the following directory of your computer: C:\Users\yourname\AppData\Roaming\Microsoft\AddIns
• You can then close the Excel workbook and the project. You do not have to save the workbook.
• Assume the name of the file is "Distribute VBA" then it will reflect as follows in the directory: C:\Users\yourname\AppData\Roaming\Microsoft\AddIns\Distribute VBA.xlam

3. Open Excel again and create a new Module in a new Workbook.

• In this module you are going to call the .xlam file that you  have created in the previous steps.
• Your code will look like this assuming you name the sub Main :

                     Sub Main()

                     Application.Run "'Distribute VBA.xlam'!Module1.ProtectedCode

                     End Sub  

• A reference needs to be created. On the menu select Tools -> References -> 
• Select the Browse button
• Make the Files of type: Microsoft Excel Files (*.xlsm; *.xlam; *.xls; *.xla)
• Make the Look in: C:\Users\yourname\AppData\Roaming\Microsoft\AddIns\
• Select the "Distribute VBA.xlam" file.
• Click Open.
• Now you will see that a reference "ProtectedCode" has been added. Select OK.
• Save this file with this Main subroutine as a *.xlam file in the same directory as the "Distribute VBA.xlam" file. In this case I will name it "Main Code.xlam"
• C:\Users\yourname\AppData\Roaming\Microsoft\AddIns\Main Code.xlam
• You can now close the Excel application and the VBA editor without saving any files.

4. There will now be two *.xlam files in the directory "C:\Users\yourname\AppData\Roaming\Microsoft\AddIns\"

• Distribute VBA.xlam - protected code
• Main Code.xlam - not protected

These two files can be distributed to anyone to use it.

5. The receiver of the files can use them as an Add-In. This Add-In can then be added to a toolbar.

• The user save the two files in their default Microsoft Add-In directory: C:\Users\receivingusername\AppData\Roaming\Microsoft\AddIns\
• The user can add it as follows in Excel: Right click anywhere on the Ribbon or Quick Access Toolbar and select "Customize The Quick Access Toolbar"
• Select Add-Ins ad Click on Go...
• Select Browse and it will take the user to their default Microsoft Add-In directory; C:\Users\receivingusername\AppData\Roaming\Microsoft\AddIns\ 
• Select the Add-In that is not protected "Main Code.xlam". This will internally call the protected code in "Distribute VBA.xlam.
•  Select OK. You will now see that it is added to the Add-Ins list and that it is selected.
• The user can now add it in the Quick Access toolbar.  Right click anywhere on the Ribbon or Quick Access Toolbar and select "Customize The Quick Access Toolbar".
• Under the Drop Down Menu "Choose Commands From" select Macros. Scroll through this listing and you will find the "Main Code" file in the listing. Select this and Add it to the list on the right. The user can change the name and icon by selecting the Modify button.