No addon making required!

Question

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?

Answer 1

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.

Answer 2

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.

• YouTube : How to Create a Personal Macro Workbook in Excel and Why You Need It

• Office Support : Create and save all your macros in a single workbook

• Office Support : Copy your macros to a Personal Macro Workbook

Answer 3

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

Answer 4

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.