I created an Excel .xlsm file with a bunch of functionality in some VBA Modules/Classes and now I've decided to separate out the code because it will be shared across 4 different sites.
I saved the vba_code.xlsm to a shared location and created my 4 different siteXYZ.xlsm files.
In each siteXYZ.xlsm file I would go to the "Tools | References" menu in the VBA editor and add a reference to the shared vba_code.xlsm at a shared file location \share_location\fileLocation\vba_code.xlsm
At this point, I tested out the functions in siteXYZ.xlsm and everything would work fine.
However, every time I saved siteXYZ.xlsm, closed Excel and then reopened the file it would lose the reference to my vba_code.xlsm file.
How can I keep the VBA references saved with my siteXYZ.xlsm file?
After spending hours searching for an answer and trying various methods such as adding a digital signature to the vba_code.xlsm file and trying to programmatically add the reference on Workbook_open I found a forum post describing the problem:
My siteXYZ.xlsm file had no VBA code or macros defined within it so Excel refused to save the VBA Project and as a result did not save the VBA Reference to vba_code.xlsm.
The solution was simple:
Add ANY VBA code to the siteXYZ.xlsm and save it.
I just double-clicked ThisWorkbook under the VBA editor and added a function to Workbook_open that doesn't do anything:
Private Sub Workbook_open()
End Sub