How to permanently add macros to Excel as part of the program?
Solution 1
In excel 2010, the personal macro workbook is located at C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb
. The best way to create this is to record a macro into your personal macro workbook. You do this by:
- Enabling the developer tab by going to
File - Options - Customize Ribbon
and checkingdeveloper
on the right side of the screen
- Go to the developer tab and click on
record macro
-
Store Macro In
yourPersonal Macro Workbook
-
Stop Recording
macro
- When you close out of excel or the workbook, it will prompt you Do you want to save the changes you made to PERSONAL.xlsb select
save
You will now have a personal macro workbook that is visible anytime you open excel. If you have macros stored in other workbooks you can transfer them to the personal macro workbook by moving the modules within
Visual Basic
in excel. Additionally, if you have a workbook with a lot of macros, you can navigate to the file location and rename that workbook to PERSONAL and save it as .xlsb
Solution 2
If you want to create a method to use and distribute your VBA solutions (e.g. allow other users to access UDF's you've created and used in your workbooks), you can easily create an Excel Add-in. Generally, you'll:
- Create a new, blank workbook and create your VBA solution.
- Save your new workbook as a .XLAM (Excel add-in).
- Use Excel to add your add-in; then distribute or make available as necessary.
See Microsoft's detailed directions here.
Related videos on Youtube
Rapier
Updated on September 18, 2022Comments
-
Rapier over 1 year
I have a macro where I'm not sure when I will need to use it.
I need them built into Excel to be able use them at every start of the program – like you would install some add-on (e.g. Kutools).
How can I do it?
-
Raystafarian over 10 yearsWhat you're looking for is the personal macro workbook
-
-
Rapier over 10 yearsIt is not working to me. I have that macro in my Personal macros file. But when I pick it, click: run. It doesnt work. It must be an issue during recording it. There must be some some order how to record it. Probably I did it in wrong way in detail while recording.
-
Rapier over 10 yearsI have tried it also. Can't make it work. Also have some problems with kind of permission in excel. It is saying me to run as 'unhidden'. I don't know where to enable it.