*.xlam add-in and missing references in a new VBA project / new Excel's instance with the add-in turned on

Question

Repro:

• Start Excel and open VBE ALT+F11.

• Find Tools and click References.

• Scroll down til you find Microsoft Scripting Runtime and tick it.

• Save the file as Addin.xlam in the default location which should be something like:

  C:\Users\username\AppData\Roaming\Microsoft\AddIns

• Close this Excel instance

• Start a new instance and open VBE (you should now see the Addin.xlam as the second VBA Project available for the workbook. If you don't open the Developer tab then add-ins and tick the box next to the name to turn it on - save, close, open a new Excel instance)

• Add a new standard Module

• Open References and you should see only 4 ticked (default)

• Scroll down and try to find Microsoft Scripting Dictionary - it ain't there!?

So, this basically means you can't add the reference to the new VBA Project (Book1) because your *.xlam is already using it

but

you can't cross-use the Scripting library because trying

Dim d as Dictionary

in that newly created Module1 fails as it doesn't recognise Dictionary object....

Is this somehow a known bug I have never realised or have I just completely overlooked something? Any workarounds?

Answer 1

The IDE keeps recently used references near the top of the list. Don't scroll down. It's right at the top. I'll admit. This threw me for a loop for a second too.