32

Is it possible to write a VBA script in one workbook, and use it in other workbooks in the machine? I guess I'm looking for a way to write a macro package.

EDIT Following the instruction in the link given by Lance, I managed to create the addin, but I cannot see it in the 'Macros' tab. What am I missing?

Tamara Wijsman
  • 57,083
  • 27
  • 185
  • 256
bavaza
  • 573
  • 2
  • 6
  • 11

5 Answers5

16

No addon making required!

Store it in a special workbook called Personal.xlsb

Steps:

You can make macros available across all your workbooks, by saving them to a special workbook that Excel loads each time it is opened.

Community
  • 1
Dheeraj Bhaskar
  • 373
  • 2
  • 10
  • This is good only if other users does not have their own set of VBA codes in that .xlsb file. I have not tested what will happen if Sub name clash between multiple .xlsb file in XLSTART folder – PatricK Mar 03 '15 at 05:11
  • 1
    @PatricK Of course not. It works with multiple users in a windows machine. Each user gets their own `XLSTART` folder. Notice that path to `XLSTART` looks like `C:\Users\\AppData\Roaming\Microsoft\Excel\XLSTART` . Since there is a Username, its different for each user – Dheeraj Bhaskar Mar 03 '15 at 15:24
  • The linked video has been deleted. I'm editing the answer to remove it. Feel free to add a new link if you have one. – Kelderic Mar 13 '17 at 13:02
  • 1
    @AndyMercer thanks for editing the answer. The content on the web is so ephemeral. Hopefully, the `personal.xlsb` hint allows people to figure it out with the help of google. I shall update the answer if I happen to do this again. – Dheeraj Bhaskar Mar 16 '17 at 17:43
11

There are a few ways to do this, here's one link that explains how to create an Excel Addin with VBA code. You can also create other types of addins in other ways, but this is the simplest. There are thousands of references you can search for that talk about how to do this.

Lance Roberts
  • 8,563
  • 9
  • 49
  • 79
  • doesn't answer the question at all – johny why May 03 '21 at 08:01
  • 1
    @johnywhy Yes, an addin is something available to all workbooks. And if you read the question you'll see the OP used my link to do that. – Lance Roberts May 04 '21 at 13:47
  • i misread the question. Addin macros can be executed from the Macro ribbon. But, addin VBA functions aren't visible to other VBA projects in the VBA IDE -- they aren't listed in the Object Browser. – johny why May 05 '21 at 15:31
2

Take advantage of the UserProfile environment variable to aviod that 'different for each user' caveat.

so the standardized Path is: %UserProfile%\AppData\Roaming\Microsoft\Excel\XLSTART

Brian McCullough
  • 21
  • 1
2

When you record a Macro you can store it afterwards in your Personal Macro Workbook. When you Exit Excel it asks if you want to store it, once you click yes the Macro will become available for all workbooks under Developer => Macros. See also Microsoft support

Cerveser
  • 79
  • 7
1

How to locate the XLStart folder

-1.Click the Microsoft Office Button Office button image , and then click Excel Options.
-2.Click Trust Center, and then under Microsoft Office Excel Trust Center, click Trust Center Settings.
-3.Click Trusted Locations, and then verify the path to the XLStart folder in the list of trusted locations.

Atul
  • 11
  • 1