In Excel, Word, and Power Point, you can create your own commands, called macros. They automate the performance of routine work when you have to constantly apply the same design to text or cells, enter similar sets of formulas, etc. In fact, macros are small programs, because they are written in Visual Basic scripts. However, even a user far from programming can easily create and edit them.
The most commonly used Excel macros. With their help, you can even create calculator documents, for example, to calculate the monthly budget of a company.
Macro recording
There are two ways to create a macro. The first is to write a VB script from scratch. This is what programmers do. The second way is simpler: you need to enable recording of all actions in a special recorder and just perform the procedures that you want to automate again.
Let's see how to record macros in Excel 2007 using the macro recorder (second method).
The necessary tools are in the tab "Developer". Since the tab is often not displayed by default, you must first go to the application settings. To do this, open the round button with the image of the Microsoft logo and select "Excel Options". Next, “Personal settings”, “Basic settings ...”, check the box next to the item responsible for displaying the desired tab.
To make new Excel macros, you need the following:
- Go to Developer> Code> Macro Record.
- Write down the name from which it will be clear what the macro does.
- Choose the appropriate keyboard shortcut for quick macro invocation (it should not duplicate your frequently used Windows keyboard shortcuts, for example, CTRL + C / CTRL + V).
- The Save To ... list is responsible for the scope of the macro. So that your Excel macros can work in all documents opened on this computer, select the "Personal Book".
- Fill out the description (list all recorded actions).
- To start recording, click OK. A special icon will appear in the corner of the screen.
- Do the necessary actions with the cells.
- Click Stop Recording in the Developer tab> Code.
Note: Excel macros can remember relative or absolute cell references. If you did not select the appropriate option, during recording, the macro will remember that the actions must be applied to the cell that you selected. If you have to work with different cells, select the option for relative link.
Working with the program menu (switching tabs) and switching to other windows in the recorder is not recorded.
Security options
Despite assurances that the application can independently control macros for malicious code, it is not recommended to take them from unverified sources. For example, download ready-made from the Internet. You can trust the opinion of antiviruses, and the macros used on several computers in the office, it is recommended to provide a digital signature.
By default, the use of macros in documents is usually disabled. You can enable Excel macros if you go to the "Developer" tab and then "Code"> "Macro Security"> "Macro Settings"> "Enable All"> "OK". The changes will take effect the next time you open the file.