CODEWITHSUNDEEP
excelvba
Berryl
Berryl

Reputation: 12853

file forgetting dependent reference

I have a spreadsheet with code called 'constants.xlsm', which other .xlsm spreadsheets use as a dependent reference.

I have a new spreadsheet file where I want to use a function from 'constants.xlsm' in a worksheet formula. The function is =ToOptionSymbol(...).

The function works as expected while the new file has a reference to 'constants', but does not want to remember that it has a reference to 'constants' when it is closed and reopened.

The 'new' file has no code modules of it's own. How can I make the 'new' file remember the reference between sessions?

Upvotes: 0

Views: 25

Answers (1)

EEM
EEM

Reputation: 6659

It seems that in addition to saving the workbook as .xlsm, it also needs to contain a VbaProject, to which the VbReferences to other files are added.

Try the any of these:

  1. Add a new module to the new workbook.

  2. If you don't want to have an empty module in the "new file" you can also add a dummy procedure into the ThisWorkbook code pane, such as this:

    Private Sub Dummy_Procedure()
End Sub

Any of the above suggestions will force the workbook to create a VbaProject, thus keeping the vbReference to the constants.xlsm vbProject.

Upvotes: 1

Related Questions