Normal.dotm equivalent in Excel for referencing the same single VBA code

Question

Just curiosity.

The only way I know so far is to create an add-in with code, put it in some trusted directory and hope it opens when you need it. The drawback is that it sometimes does not open together with application (e.g. I have a custom UDF in the add-in, I use it in the worksheet and an error is what I get, because the addin hasn't started). For this I have a button on my ribbon which calls a sub in the addin which does nothing, but then the addin is activated the UDF works.

Is there any other efficient way to reference code in another workbooks, like in Word we have normal.dotm template?

Answer 1

To create an equalevant to normal.dot in Excel do this (at least ver. 2016):

1. Record a macro from the Developer tab (you likely have to enable this tab first)
   
   This will create the file %appdata%\Microsoft\Excel\XLSTART\PERSONAL.XLSB which is Excel's equalevant to normal.dot
2. Now unhide the hidden workbook called "PERSONAL.XLSB"
   
3. Press Alt+F8 or Alt+F11 to edit the VBA code

Extra: VBA example for SaveAs:

Application.Dialogs(xlDialogSaveAs).Show