Can I edit a macro in a hidden workbook?
The tutorial shows how to create and use the Personal.xlsb file to make Excel macro available in all workbooks. Show
Normally, when you write a macro in Excel, it can only be accessed from that specific workbook. But what if you want to use it in other files too? Recreating the same macro for each and every workbook does not sound like a good idea. Instead, store your popular VBA codes in the Personal Macro Workbook. This will make the macros accessible every time you open Excel, in any workbook! What is Excel Personal Macro Workbook?Personal Macro Workbook is a special file named Personal.xlsb in Excel 2007 – 2019 or Personal.xls in earlier versions. Any macros stored in this file are available to you whenever you start Excel. A great thing about the personal workbook is that it's hidden by default, so you won't ever notice it opening in the backend. No actions on your side are required. Microsoft Excel just knows the location where this file lives in your system and looks for it automatically on every launch. Aside from macros, the Personal Macro Workbook can store your custom functions (user-defined functions) written in VBA. A fly in the ointment is that the Excel Personal Macro Workbook does not exist by default. To create it, you will need to record at least one macro. Once the Personal.xlsb file is created, you can add any other macros to it by using the VB Editor. Where is Personal Macro Workbook?The Personal.xlsb file is stored in the XLSTART folder in the following locations. On Windows 10, Windows 7, and Windows Vista:
On Windows XP:
Please note that the AppData folder is hidden by default. So, to get to the XLSTART folder in Windows Explorer, check the Hidden items box on the View tab. Note. If the XLSTART folder does not exist on your computer, that means your personal macro workbook is not created yet. Please follow the below steps to make one. How to create Personal Macro Workbook in ExcelTo make your personal macro workbook, you need to record any macro, which is done via the Developer tab. So, before we get started, please make sure the Developer tab is activated in your Excel. To create the Excel Personal Macro Workbook, carry out these steps:
Done! The Personal.xlsb file is created and will be automatically opened in the background every time you start Excel. Now that you have a personal macro workbook, how do you place macros into it? There are four different ways to do this:
How to view, create, copy and edit macros in Personal Macro WorkbookOnce the Personal.xlsb file is saved on your computer, you can view, create and edit the macros the Visual Basic Editor. Also, you can copy the code you wish to reuse from another workbook. To create or edit a macro in your personal workbook, this is what you need to do:
How to use macros stored in Personal WorkbookMacros stored in your personal workbook can be used in any Excel file. For this, just open the worksheet in which you want to run the macro, and do the following:
You can also assign a custom shortcut to a macro or run it by clicking a special macro button. How to export Personal Macro WorkbookLike any other VBA code, the macros stored in Personal.xlsb can be exported to a .bas file. To export your personal macro workbook, here's what you need to do:
To import a .bas file that someone shared with you, right-click the module under PERSONAL.XLSB into which you want to place the macros, and select Import File. In case you'd like to share your personal workbook with other people, proceed in one of the following ways. Share the Personal.xlsb fileThe easiest way to share a personal macro workbook with someone is to send them a copy of your Personal.xlsb file as attachment, or save it to a cloud storage or your local network. And then, other persons can put your Personal.xlsb file in their XLSTART folder. See where Personal.xlsb is located. This simple approach, however, can be dangerous in case the other person already has his/her own macros in the Personal.xlsb file. So, I'd recommend replacing this file only on your own devices when you are absolutely sure you won't mess up anything. Copy macros to another workbookIf you want to share just a few macros from your Personal.xlsb file, you can copy those macros to another workbook, and email that workbook to other users. For the detailed steps, please see How to copy macros to another workbook. Export macros to a .bas fileA professional-like way is to move all the macros you wish to share into one module and export that module as a .bas file. Send the .bas file to other users by email, and they will be able import it as a new module into their Personal.xls file. Note. Please be aware that some macros from your personal workbook may not work for other users, e.g. VBA code that refers to a specific sheet. How to enable Personal Macro Workbook in ExcelIf one day you open Excel and find out that your favorite macros are no longer there, don't panic! Most likely, the Personal.xlsb file was disabled by Excel after a crash, update, or for whatever reason. Here are the steps to get your personal workbook up and running again:
How to prevent Personal.xlsb from opening but keep macros runningBy default, the Personal Macro Workbook should not be visible in Excel. If the Personal.xlsb file keeps opening whenever you use macros and this really bugs you, you can hide it in this way:
That's it! From now on, the personal workbook will be hidden from view but still launch in the background, so you could access the macros stored there. How to unhide a personal workbook in ExcelTo make your personal macro workbook visible, this is what you need to do:
How to remove Personal.xlsb fileIf you'd like to completely remove the Personal Macro Workbook from your computer, delete the Personal.xlsb file from the XLSTART folder. Here's how:
Note. To remove Personal.xlsb, you must close the Excel application first. If the file does not delete, that means it's still running in the background. To fix this, open the Task Manager and kill all running Excel processes. That's how to make your favorite Excel macros available in all workbooks. I thank you for reading and hope to see you on our blog next week! You may also be interested inWill macro work on hidden sheets?Your macro can access any cells in the workbook, unless the worksheet containing the cell is hidden. When you hide a worksheet, it is even hidden from normal macro operations.
Why can't I edit a macro in Excel?To edit and run macros, you must set the security level to temporarily enable all macros: On the Developer tab, in the Code group, click Macro Security. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.
Can you run a macro in any open workbook?A Personal Macro Workbook is a hidden workbook in your system that opens whenever you open the Excel application. It's a place where you can store macro codes and then access these macros from any workbook.
|