We all have – some less often, some more often – to repeat the same actions and operations in Excel. Any office work involves some kind of “routine” – the same weekly reports, the same actions for processing incoming data, filling out monotonous tables or forms, etc. The use of macros and user-defined functions allows you to automate these operations, shifting monotonous repetitive work on the shoulders of Excel. Another reason for using macros in your work may be the need to add missing, but necessary functions to Microsoft Excel. For example, the function of collecting data from different sheets into one summary sheet, posting data back, displaying the amount in words, etc.
A macro is a programmed sequence of actions (program, procedure) written in the Visual Basic for Applications (VBA) programming language. We can run a macro as many times as we want, forcing Excel to perform any sequence of actions we want that we don’t want to do manually.
Basically, there are a great many programming languages (Pascal, Fortran, C ++, C #, Java, ASP, PHP …), but for all programs in the Microsoft Office suite, the built-in VBA language is the standard. The commands of this language are understood by any office application, be it Excel, Word, Outlook or Access.
Method 1. Creating macros in the Visual Basic editor
To enter commands and form a program, i.e. To create a macro, you need to open a special window – the editor of VBA programs built into Microsoft Excel.
- In older versions (Excel 2003 and older), go to Tools – Macro – Visual Basic Editor (Toos – Macro – Visual Basic Editor) .
- New versions (Excel 2007 and later) to do this, first display the tab Developer (Developer,) . Choose File – Preferences – Customize the Ribbon (File – Options – Customize Ribbon) and turn on the right side of the window box developer (Developer,) . Now, on the tab that appears to us to be the main tools available to work with macros, including those necessary to us button (Visual Basic Editor)
Unfortunately, the VBA editor interface and help files are not translated into Russian by Microsoft, so you will have to put up with English commands in menus and windows:
Macros (that is, sets of VBA commands) are stored in code units. In any Excel workbook, we can create any number of program modules and place our macros there. One module can contain any number of macros. All modules are accessed using the Project Explorer window in the upper left corner of the editor (if not visible, press CTRL + R). There are several types of software modules for different situations:
- Regular modules – used in most cases when it comes to macros. To create such a module, select Insert – Module from the menu . In the appeared window of a new empty module, you can enter commands in VBA by typing them from the keyboard or copying them from another module, from this site or from somewhere else:
- The This Book module is also visible in the upper left corner of the Visual Basic Editor in a window called the Project Explorer. This module usually contains macros that should be executed when any events occur in the book (opening or saving a book, printing a file, etc.):
- The sheet module is available through the Project Explorer and through the context menu of the sheet, i.e. Right-click on the sheet tab – team source code (View Take the Source) . Macros are written here that should be executed when certain events occur on the sheet (changing data in cells, recalculating a sheet, copying or deleting a sheet, etc.)
A typical macro introduced into a standard module looks something like this:
Let’s take a look at the Test macro given above as an example :
- Any macro must begin with a Sub statement , followed by the name of the macro and a list of arguments (input values) in parentheses. If there are no arguments, then the parentheses must be left blank.
- Any macro must end with an End Sub statement .
- Everything between Sub and End Sub is the body of the macro, i.e. commands to be executed when the macro is run. In this case, the macro selects the cell, fills the selected range (Selection) with yellow color (code = 6) and then loops through all the cells, replacing formulas with values. At the end, a message box (MsgBox) is displayed.
It is immediately clear that right away, without prior preparation and experience in programming in general and in VBA in particular, it will be difficult to figure out which commands and how to enter so that the macro automatically performs all the actions that, for example, you do to create a weekly report to the company’s management. Therefore, we move on to the second way of creating macros, namely …
Method 2. Recording macros with a macro recorder
A macro recorder is a small program built into Excel that translates any user action into the VBA programming language and writes the resulting command to a program module. If we turn on the macro recorder for recording, and then start creating our own weekly report, then the macro recorder will start recording commands after each of our actions and, as a result, we will get a macro that creates a report as if it were written by a programmer. This method of creating macros does not require user knowledge about programming and VBA and allows you to use macros as a kind of analogue of video recording: turned on recording, performed an operation, rewound the tape and started performing the same actions again. Naturally, this method has its pros and cons:
- The macro recorder records only those actions that are performed within the Microsoft Excel window. As soon as you close Excel or switch to another program, the recording stops.
- The macro recorder can only record actions for which there are menu commands or buttons in Excel. A programmer can write a macro that does what Excel has never been able to do (sort by color, for example, or something similar).
- If you make a mistake while recording a macro with the macro recorder, the error will be recorded. However, you can safely press the button to undo the last action (Undo) – while recording a macro with a macro recorder, it not only returns you to the previous state, but also erases the last recorded command in VBA.
To enable recording, you must:
- in Excel 2003 and older – select in the menu Tools – Macro – Record New (Tools – Macro – Record New Macro )
- in Excel 2007 and newer – click Record Macro (Record macro) tab developer (Developer)
Then you need to configure the parameters of the recorded macro in the Record Macro window :
- Macro name – any name in Russian or English will do. The name must begin with a letter and not contain spaces or punctuation marks.
- Keyboard shortcut – will then be used to quickly run a macro. If you forget the combination or no enter, the macro can be run through the menu Tools – Macro – Macros – Run (Tools – Macro – Macros – Run ) or by pressing the Macro (Macros) tab developer (Developer) , or by pressing ALT + F8.
- Save to … – here you can set the place where the text of the macro will be saved, i.e. a set of VBA commands that make up the macro:
- This workbook – the macro is saved to the module of the current workbook and, as a result, will be executed only while this workbook is open in Excel
- New workbook – the macro is saved into a template, on the basis of which any new blank workbook is created in Excel, i.e. the macro will be contained in all new books created on this computer from the current moment
- A personal macro workbook is a special Excel workbook named Personal.xls that is used as a macro store. All macros from Personal.xls are loaded into memory when Excel starts and can be run at any time and in any workbook.
After starting recording and performing the actions you want to record, you can stop recording with the Stop Recording command .
Running and editing macros
Manage all available macros is done in a window that can be opened by pressing the Macro (Macros) tab developer (Developer) , or – in older versions of Excel – menu Tools – Macro – Macros (the Tools – the Macro – Macros) :
- Any highlighted in the list, you can run a macro click Execute (the Run) .
- Button Options (Options) allows you to view and edit a keyboard shortcut for quick start of the macro.
- Button Change (Edit Have) opens the Visual Basic Editor (see. Above) and allows you to view and edit the text of a macro to VBA.
This tutorial is an introduction to the Excel VBA (Visual Basic for Applications) programming language. After learning VBA, you can create macros and perform almost any task in Excel. You’ll soon realize that macros can save you tons of time by automating repetitive tasks and allowing flexible interactions with other users.
This tutorial is not a comprehensive guide to the Excel VBA programming language. Its purpose is to help a beginner to master writing macros in Excel using VBA code. For those wishing to learn this programming language in more depth, there are excellent Excel VBA books . The following is the content of the Excel Visual Basic tutorial. For beginner programmers, it is highly recommended to start with the first section of the tutorial and work through them in order. Those with experience in VBA programming can jump straight to the topics of interest.
- Part 1: Code Formatting in Excel VBA
- Part 2: Data Types, Variables and Constants in Excel VBA
- Part 3: Arrays in Excel VBA
- Part 4: Function and Sub Procedures
- Part 5: Conditional Operators
- Part 6: Loops
- Part 7: Operators and Built-in Functions
- Part 8: Excel Object Model
- Part 9: Events in Excel
- Part 10: VBA Errors
- VBA examples
| VLOOKUP | Graduation Course | Sarkari Yojana | India Top Exam | Excel Tutorial |