Contents
If you are not yet familiar with macros in Excel, then I envy you a little. The feeling of omnipotence and the realization that your Microsoft Excel can be upgraded almost to infinity that will come to you after getting to know macros is a pleasant feeling.
However, this article is for those who have already “learned the power” and have begun to use macros (foreign or written by themselves – it doesn’t matter) in their daily work.
A macro is a code (several lines) in the Visual Basic language that makes Excel do what you need: process data, generate a report, copy-paste many repetitive tables, etc. The question is where to store these few lines of code? After all, where the macro is stored will then depend on where it can (or cannot) work.
Если макрос решает небольшую локальную проблему в отдельно взятом файле (например обрабатывает внесенные в конкретный отчет данные особым образом), то логично хранить код внутри этого же файла. Без вопросов.
And if a macro should be relatively universal and needed in any Excel workbook – like, for example, a macro for converting formulas to values? Why not copy his Visual Basic code into every book every time? In addition, sooner or later, almost any user comes to the conclusion that it would be nice to put all the macros in one box, i.e. have them always at hand. And maybe even run not manually, but with keyboard shortcuts? This is where the Personal Macro Workbook can be of great help.
How to create a Personal Macro Book
In fact, Personal Book of Macros (LMB) is a regular Excel file in binary workbook format (Personal.xlsb), which automatically opens in stealth mode at the same time as Microsoft Excel. Those. when you just start Excel or open any file from disk, two files are actually opened – yours and Personal.xlsb, but we do not see the second one. Thus, all macros that are stored in the LMB are available for launch at any time while Excel is open.
If you have never used the LMB, then initially the Personal.xlsb file does not exist. The easiest way to create it is to record some unnecessary meaningless macro with the recorder, but specify the Personal Book as the place to store it – then Excel will be forced to automatically create it for you. For this:
- Click the developer (Developer). If tabs developer is not visible, then it can be enabled in the settings through File – Options – Ribbon Setup (Home — Options — Customize the Ribbon).
- On the Advanced tab developer click Macro recording (Record Macro). In the window that opens, select Personal Macro Book (Personal Macro Workbook) as a place to store the written code and press OK:
- Stop recording with button Stop Recording (Stop Recording) tab developer (Developer)
You can check the result by clicking on the button Visual Basic right there on the tab. developer – in the opened editor window in the upper left corner on the panel Project — VBA Project our file should appear PERSONAL. XLSB. Its branch of which can be expanded with a plus sign on the left, reaching Module1, where the code of the meaningless macro we just recorded is stored:
Congratulations, you’ve just created your own Personal Macro Book! Just do not forget to click on the save button with a floppy disk in the upper left corner of the toolbar.
How to use Personal Macro Book
Then everything is simple. Any macro you need (i.e. a piece of code starting with Sub and ending End Sub) can be safely copied and pasted either into Module1, or in a separate module, adding it previously through the menu Insert – Module. Keeping all macros in one module or laying them out in different ones is only a matter of taste. It should look something like this:
You can run the added macro in the dialog box called with the button Macros (Macros) tab developer:
In the same window, by clicking the button Parameters (Options), you can set a keyboard shortcut to quickly run a macro from the keyboard. Be careful: keyboard shortcuts for macros distinguish between layout ( or English) and case.
In addition to the usual macro-procedures in the Personal Book, you can also store custom macro functions (UDF = User Defined Function). Unlike procedures, function code starts with a statement Functionor Public Function, and end with End Function:
The code must be copied in the same way to any module of the PERSONAL.XLSB book, and then it will be possible to call the function in the usual way, like any standard Excel function, by pressing the button fx in the formula bar and selecting a function in the window Function Wizards in category User Defined (User Defined):
Examples of such functions can be found in large quantities on the Internet or here on the site (amount in words, approximate text search, VLOOKUP 2.0, converting Cyrillic to transliteration, etc.)
Where is the Personal Macro Book stored?
If you use the Personal Book of Macros, then sooner or later you will have a desire:
- share your accumulated macros with other users
- copy and transfer the Personal Book to another computer
- make a backup copy
To do this, you will need to find the PERSONAL.XLSB file on your computer disk. By default, this file is stored in a special Excel startup folder called XLSTART. So all that is needed is to get to this folder on our PC. And this is where a little complication arises, because the location of this folder depends on the version of Windows and Office and may vary. This is usually one of the following options:
- C:Program FilesMicrosoft OfficeOffice12XLSTART
- C:Documents and SettingsComputerApplication DataMicrosoftExcelXLSTART
- C: Usersyour-account-nameAppDataRoamingMicrosoftExcelXLSTART
Alternatively, you can ask Excel itself for the location of this folder using VBA. To do this, in the Visual Basic editor (button Visual Basic tab developer) нужно открыть окно immediate keyboard shortcut Ctrl + G, type in the command ? Application.StartupPath and click on Enter:
The resulting path can be copied and pasted into the top line of the Explorer window in Windows and click Enter – and we will see a folder with our Personal Book of Macros file:
PS
And a few practical nuances in pursuit:
- when using the Personal Macro Book, Excel will run a little slower, especially on weaker PCs
- it is worth periodically clearing the Personal Book from informational garbage, old and unnecessary macros, etc.
- corporate users sometimes have difficulties using the Personal Book, tk. this is a file in the system hidden folder
- What are macros and how to use them in your work
- Usefulness for the VBA programmer
- Training “Programming macros in VBA in Microsoft Excel”