Creating macros and user-defined functions in VBA

Contents

Introduction

We all have to – some less often, some more often – repeat the same actions and operations in Excel. Any office work involves a certain “routine component” – 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 the monotonous repetitive work onto 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 to one final sheet, posting data back, displaying the amount in words, etc.

Macro is a programmed sequence of actions (program, procedure) written in the Visual Basic for Applications (VBA) programming language. We can run the macro as many times as we want, forcing Excel to perform a sequence of any actions we need that we don’t want to do manually.

In principle, there are a great many programming languages (Pascal, Fortran, C ++, C #, Java, ASP, PHP …), but for all programs of the Microsoft Office package, 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: Create 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 VBA program editor built into Microsoft Excel.

In older versions (Excel 2003 and older), for this we go to the menu Service – Macro – Visual Basic Editor (Toos — Macro — Visual Basic Editor).
In newer versions (Excel 2007 and later), this requires first displaying the tab developer (Developer). We choose File – Options – Ribbon Setup (File — Options — Customize Ribbon) and check the box on the right side of the window developer (Developer). Now on the tab that appears, we will have access to the main tools for working with macros, including the button we need Visual Basic editor (Visual Basic Editor)
:

Unfortunately, the VBA editor interface and help files are not translated into by Microsoft, so you will have to put up with English commands in menus and windows:

Macros (i.e., sets of VBA commands) are stored in program modules. 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 it is not visible, press CTRL+R). Program modules come in several types for different situations:

Regular modules – are used in most cases when it comes to macros. To create such a module, select from the menu Insert – Module. In the new empty module window that appears, 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:
Module This book – Also visible in the upper left corner of the Visual Basic Editor in a window called 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.):
Letter module – available through the Project Explorer and through the context menu of the sheet, i.e. right-click on the sheet tab – command Source text (View Source). Macros are written here that should be executed when certain events occur on the sheet (changing data in cells, recalculating the sheet, copying or deleting the sheet, etc.)

A normal macro introduced into a standard module looks something like this:

Let’s take the above macro as an example. Zamena:

Any macro must begin with the statement Sub, followed by the name of the macro and a list of arguments (input values) in parentheses. If there are no arguments, then the brackets must be left empty.
Any macro must end with the statement End Sub.
Everything in between Sub и End Sub – macro body, i.e. commands to be executed when the macro is run. In this case, the macro selects a 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’s 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 exactly 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 for company management. Therefore, we move on to the second way to create macros, namely …

Method 2. Recording macros with a macro recorder

Macro recorder is a small program built into Excel that translates any user action into the VBA programming language and writes the resulting command into a program module. If we turn on the macro recorder for recording, and then start creating our 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 way of creating macros does not require the user’s knowledge of programming and VBA and allows you to use macros as a kind of analogue of video recording: turn on the recording, perform the operation, rewind the tape and start 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 that have menu commands or buttons in Excel. A programmer can write a macro that does something that Excel has never been able to do (sort by color, for example, or something like that).
If you make a mistake while recording a macro with a macro recorder, the error will be recorded. However, you can safely press the button to cancel 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:

in Excel 2003 and later – select from the menu Tools – Macro – Start Recording (Tools — Macro — Record New Macro)
in Excel 2007 and later, click the button Macro recording (Record macro) tab developer (Developer)

Then you need to configure the parameters of the recorded macro in the window Macro recording:

macro name – any name in or English will do. The name must begin with a letter and contain no spaces or punctuation marks.
Keyboard shortcut – will then be used to quickly run the macro. If you forget the combination or do not enter it at all, then the macro can be run through the menu Service – Macro – Macros – Run(Tools — Macro — Macros — Run) or using the button Macros (Macros) tab developer (Developer) or by pressing ALT+F8.
Save to… – here you specify the place where the text of the macro will be saved, i.e. a set of commands in VBA of which the macro consists.:

This book – the macro is saved in the module of the current workbook and, as a result, will be executed only while this workbook is open in Excel
A new book – the macro is saved to a template, on the basis of which any new empty workbook in Excel is created, i.e. the macro will be contained in all new books created on this computer starting from the current moment
Personal Macro Book is a special Excel workbook named Personal.xls, which is used as macro storage. All macros from Personal.xls are loaded into memory when Excel starts and can be launched at any time and in any workbook.

After enabling recording and performing the actions that need to be recorded, recording can be stopped with the command Stop Recording (Stop Recording).

Running and editing macros

All available macros are managed in a window that can be opened using the button Macros (Macros) tab developer (Developer) or – in older versions of Excel – through the menu Service – Macro – Macros (Tools — Macro — Macros):

Any macro selected in the list can be launched with the button Run (run).
Button Parameters (Options) allows you to view and edit a keyboard shortcut for quickly running a macro.
Button Change (Edit) opens the Visual Basic editor (see above) and allows you to view and edit the macro text in VBA.

Creating a Button to Run Macros

In order not to remember the keyboard shortcut for running a macro, it is better to create a button and assign the desired macro to it. The button can be of several types:

Toolbar button in Excel 2003 and later

Open the menu Service – Setup (Tools — Customize) and go to the tab commands (Commands)… In the category Macros easy to find a cheerful yellow “bun” – custom button (Custom button):

Drag it to your toolbar and then right-click on it. In the context menu, you can assign a macro to the button, select a different icon and name:

Button on the Quick Access Toolbar in Excel 2007 and later

Right-click the Quick Access Toolbar in the upper left corner of the Excel window and select Command Customizing the Quick Access Toolbar (Customise Quick Access Toolbar):

Then select a category in the window that opens. Macros and with the button Add (Add) move the selected macro to the right half of the window, i.e. to the Quick Access Toolbar:

Button on sheet

This method is suitable for any version of Excel. We will add a macro launch button directly to the worksheet as a graphical object. For this:

In Excel 2003 and later, open the toolbar Forms through the menuView – Toolbars – Forms (View — Toolbars — Forms)
In Excel 2007 and later, open the dropdown list Insert (Insert) tab developer (Developer)

Choose an object Button (Button):

Then draw a button on the sheet by holding down the left mouse button. A window will automatically appear where you need to select a macro that should run when you click on the drawn button.

Creating Custom Functions in VBA

Creating user-defined functions or, as they are sometimes called, UDF-functions (User Defined Functions) is not fundamentally different from creating a macro in a regular program module. The only difference is that the macro performs a sequence of actions with the objects of the book (cells, formulas and values, sheets, charts, etc.), and the user-defined function only with those values that we pass to it as arguments (the initial data for the calculation ).

To create a custom function for calculating, for example, value added tax (VAT), open the VBA editor, add a new module through the menu Insert – Module and enter the text of our function there:

Note that unlike macros, functions have a header Function instead Sub and a non-empty list of arguments (in our case, this is PRICE). After entering the code, our function becomes available in the usual window of the Function Wizard (Insert – Function) in the category User Defined (User Defined):

After selecting the function, select the cells with arguments (with the amount for which VAT must be calculated) as in the case of a regular function: