CODEWITHSUNDEEP
excelvbafiletemplatesadd-in
ZygD
ZygD

Reputation: 24478

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

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?

Upvotes: 1

Views: 9074

Answers (2)

MrCalvin
MrCalvin

Reputation: 1825

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

Upvotes: 2

cybermike
cybermike

Reputation: 1147

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:

Upvotes: 4

Related Questions