1

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?

ZygD
  • 22,092
  • 39
  • 79
  • 102
  • One reason your add-in may not be loading is if you are using code to open a new instance of Excel which you are then trying to run the code in. This might help if that is the case: http://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically – Dave Mar 25 '15 at 08:23
  • Are you using the Excel addin manager to add your addin to the list of loaded addins? – Charles Williams Mar 25 '15 at 10:44

2 Answers2

3

Indeed, Excel DOES have a common code file, similar in concept to Word's normal.dotm. It is called Personal.xlsb. I use it myself for common functions that I need for several linked yet independent spreadsheets.

Using Personal.xlsb has some disadvantages too, so you'll have to decide if that works better than the Add-in approach. Note that Personal.xlsb works best when its just one person needing common functions across spreadsheets; its not well suited for multi-user access to the spreadsheets in an enterprise environment.

Some useful links are below to get started. Also just google search "excel Personal.xlsb" and you will find a lot more information:

cybermike
  • 1,107
  • 1
  • 9
  • 14
  • At first I did not have the file in its place, but after recording some macro to the "Personal Macro Workbook" and saving it, the file appeared just in place it had to be in. The **Personal.xslb** seems to be designed for VBA code only, as there are no worksheets if you open the file. Thanks! – ZygD Apr 23 '15 at 14:16
2

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)
    enter image description here
    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"
    enter image description here
  3. Press Alt+F8 or Alt+F11 to edit the VBA code

Extra: VBA example for SaveAs:

Application.Dialogs(xlDialogSaveAs).Show
MrCalvin
  • 1,675
  • 1
  • 19
  • 27