CODEWITHSUNDEEP
excelvba
nvuono
nvuono

Reputation: 3363

Excel isn't saving VBA reference

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?

Upvotes: 5

Views: 9212

Answers (4)

Brian Gonzalez
Brian Gonzalez

Reputation: 1354

In addition to adding code, you can also add a module, user form, or class module. I think the reason for that is that VBA needs one of those four things to determine that a file is a macro-enabled workbook. My guess is that this was done to distinguish between files that contain macros (and therefore have to be enabled) and files that can contain macros but do not (e.g. xlsm, xlsb, etc. file with no macros.)

As would be expected, if you later remove the code or modules / userform later and there are no more in the file, Excel will remove your custom name. And the custom name reverts back to VBAProject

Upvotes: 0

Chad Crowe
Chad Crowe

Reputation: 1338

Following @nvuono

You need to add some kind of module/reference to the excel file for it to save the references you have added. 

Private Function addJunkModuleToGetReferencesToSave(ByRef wb As Workbook)

    Set new_module = wb.VBProject.VBComponents.Add(vbext_ct_ClassModule)
    new_module.name = "Junk"
    new_module.CodeModule.AddFromString ""

End Function

Upvotes: -1

Charles Williams
Charles Williams

Reputation: 23540

The usual method of achieveing this is by saving your vba_code.xlsm as an addin (XLA or XLAM) and storing it in the shared location, then adding the addin to Excel in your 4 different sites.
You can also extend this approach by using your own Addin Loader instead of Excel's.
There is a working example of an Addin Loader at http://www.decisionmodels.com/downloads.htm

Upvotes: 1

nvuono
nvuono

Reputation: 3363

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

Upvotes: 19

Related Questions