Visual basic macro. Excel acros for beginners

Few people know that the first version of the popular Microsoft Excel product appeared in 1985. Since then, it has gone through several modifications and is in demand by millions of users around the world. At the same time, many work with only a small fraction of the capabilities of this spreadsheet processor and do not even know how their life could be made easier by programming in Excel.

What is VBA

Programming in Excel is carried out using the Visual Basic for Application programming language, which is originally built into the most famous spreadsheet processor from Microsoft.

Experts attribute the relative ease of development to its merits. As practice shows, even users who do not have professional programming skills can master the basics of VBA. The peculiarities of VBA include the execution of the script in the environment of office applications.

The disadvantage of the program is the problems associated with the compatibility of different versions. They are caused by the fact that the VBA program code refers to functionality that is present in the new version of the product, but not in the old one. Also, the disadvantages include the excessively high openness of the code for change by an unauthorized person. However, Microsoft Office as well as IBM Lotus Symphony allows the user to encrypt the seed and set a password to view it.

Objects, Collections, Properties and Methods

These are the concepts that need to be understood by those who are going to work in the VBA environment. First of all, you need to understand what an object is. In Excel, this is a sheet, workbook, cell, and range. These objects have a special hierarchy, i.e. obey each other.

The main one is Application, which corresponds to the Excel program itself. This is followed by Workbooks, Worksheets, and Range. For example, to refer to cell A1 on a specific sheet, you must specify a path based on the hierarchy.

As for the concept of "collection", it is a group of objects of the same class, which in the record has the form ChartObjects. Its individual elements are also objects.

The next concept is properties. They are a necessary characteristic of any object. For example, for Range, it is Value or Formula.

Methods are commands that indicate what needs to be done. When writing code in VBA, they must be separated from the object by a dot. For example, as will be shown later, the Cells (1,1) .Select command is very often used when programming in Excel. It means that you need to select a cell with coordinates

Selection.ClearContents is often used with it. Its execution means clearing the contents of the selected cell.

How to start

Then you need to go to the VB application, for which it is enough to use the combination of keys "Alt" and "F11". Further:

• in the menu bar located at the top of the window, click on the icon next to the Excel icon;
• select the Mudule team;
• save by clicking on the icon with the image;
• write, let's say, a sketch of the code.

It looks like this:

Sub program ()

"Our code

Please note that the line "" Our code "will be highlighted in a different color (green). The reason is in the apostrophe at the beginning of the line, which means that a comment follows.

Now you can write any code and create a new tool for yourself in VBA Excel (see examples of programs below). Of course, those who are familiar with the basics of Visual Basic will find it much easier. However, even those who do not have them, if desired, will be able to get used to it quickly enough.

Excel macros

This name hides programs written in the Visual Basic for Application language. Thus, programming in Excel is about creating macros with the right code. Thanks to this feature, the Microsoft spreadsheet processor self-develops, adapting to the requirements of a particular user. Once you've figured out how to create modules for writing macros, you're ready to start looking at specific VBA Excel program examples. It is best to start with the most rudimentary codes.

Example 1

Task: write a program that will copy the value of the contents of one cell and then write to another.

For this:

• open the "View" tab;
• go to the Macros icon;
• click on "Record Macro";
• fill out the opened form.

For simplicity, leave “Macro1” in the “Macro name” field, and insert, for example, hh into the “Keyboard shortcut” field (this means that you can start the program with the blitz command “Ctrl + h”). Press Enter.

Now, when the macro recording has already started, the contents of one cell are copied to another. Returns to the original icon. Click on "Record Macro". This action means the end of the program.

• again go to the line "Macros";
• select "Macro 1" in the list;
• click "Run" (the same action is started by starting the keyboard shortcut "Ctrl + hh").

As a result, the action occurs that was carried out during the recording of the macro.

It makes sense to see what the code looks like. To do this, go back to the line "Macros" and click "Change" or "Enter". As a result, they find themselves in a VBA environment. Actually, the code of the macro itself is located between the lines Sub Macro1 () and End Sub.

If copying was done, for example, from cell A1 to cell C1, then one of the lines of code will look like Range (“C1”). Select. Translated, it looks like "Range (" C1 "). Select", in other words, it switches to VBA Excel, to cell C1.

The ActiveSheet.Paste command ends the active part of the code. It means writing the contents of the selected cell (in this case A1) to the selected cell C1.

Example 2

VBA loops help you create various macros in Excel.

VBA loops help you create a variety of macros. Suppose we have a function y = x + x 2 + 3x 3 - cos (x). It is required to create a macro to get its graph. This can only be done using VBA loops.

For the initial and final value of the function argument, take x1 = 0 and x2 = 10. In addition, you must enter a constant - the value for the step of changing the argument and the initial value for the counter.

All sample VBA Excel macros are created using the same procedure as presented above. In this particular case, the code looks like:

Sub programm ()

shag = 0.1

Do While x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Cells (i, 1) .Value = x1 (the value of x1 is written to the cell with coordinates (i, 1))

Cells (i, 2) .Value = y (the y value is written to the cell with coordinates (i, 2))

i = i + 1 (counter is active);

x1 = x1 + shag (the argument is changed by the step size);

End Sub.

As a result of running this macro in Excel, we get two columns, the first of which contains the values ​​for x, and the second for y.

Then a graph is plotted using them in a standard way for Excel.

Example 3

To implement loops in VBA Excel 2010, as in other versions, along with the already given Do While construction, For is used.

Consider a program that creates a column. Each cell will contain the squares of the number of the corresponding row. Using the For construct will allow you to write it very shortly, without using a counter.

First you need to create a macro as described above. Next, we write down the code itself. We assume that we are interested in values ​​for 10 cells. The code looks like this.

For i = 1 to 10 Next

The command is translated into "human" language as "Repeat from 1 to 10 in increments of one."

If the task is to get a column with squares, for example, all odd numbers from the range from 1 to 11, then we write:

For i = 1 to 10 step 1 Next.

Here step is a step. In this case, it is equal to two. By default, the absence of this word in the loop means that the step is one.

The results obtained must be saved in cells with the number (i, 1). Then, at each start of the cycle, with an increase in i by the step size, the number of the row will also automatically increase. Thus, the code will be optimized.

In general, the code will look like:

Sub program ()

For i = 1 To 10 Step 1 (you can simply write For i = 1 To 10)

Cells (i, 1) .Value = i ^ 2 (i.e. the value of the square i is written into cell (i, 1))

Next (in a sense, it plays the role of a counter and means another start of the loop)

End Sub.

If everything is done correctly, including recording and running a macro (see the instructions above), then each time it is called, a column of the specified size will be obtained (in this case, consisting of 10 cells).

Example 4

In everyday life, quite often there is a need to make this or that decision, depending on some condition. You cannot do without them in VBA Excel. Examples of programs, where the further course of the algorithm execution is chosen, and not predetermined initially, most often use the If… Then (for complex cases) If… Then… END If construct.

Let's consider a specific case. Suppose you need to create a macro for Excel so that the cell with coordinates (1,1) is written:

1 if the argument is positive;

0 if the argument is null;

-1 if the argument is negative.

The creation of such a macro for Excel begins in the standard way, using the Alt and F11 hot keys. Next, the following code is written:

Sub program ()

x = Cells (1, 1) .Value (this command assigns x the value of the cell content at coordinates (1, 1))

If x> 0 Then Cells (1, 1) .Value = 1

If x = 0 Then Cells (1, 1) .Value = 0

If x<0 Then Cells(1, 1).Value = -1

End Sub.

It remains to run the macro and get the desired value for the argument in Excel.

VBA functions

As you may have noticed, programming in Microsoft's most famous spreadsheet processor isn't all that difficult. Especially if you learn how to use VBA functions. In total, this programming language, created specifically for writing applications in Excel and Word, has about 160 functions. They can be divided into several large groups. This:

• Mathematical functions. By applying them to the argument, you get the value of the cosine, natural logarithm, integer part, etc.
• Financial functions. Thanks to their presence and using programming in Excel, you can get effective tools for accounting and financial calculations.
• Array processing functions. These include Array, IsArray; LBound; UBound.
• VBA Excel functions for string. This is a fairly large group. It includes, for example, the Space functions to create a string with a number of spaces equal to an integer argument, or Asc to translate characters to ANSI code. All of them are widely used and allow you to work with rows in Excel, creating applications that greatly facilitate the work with these tables.
• Data type conversion functions. For example, CVar returns the value of the Expression argument by converting it to the Variant data type.
• Functions for working with dates. They significantly extend the standard ones. For example, the WeekdayName function returns the name (full or partial) of the day of the week by its number. Timer is even more useful. It gives you the number of seconds that have elapsed from midnight to a specific point in the day.
• Functions for converting a numeric argument to different number systems. For example, Oct outputs a number in octal representation.
• Formatting functions. The most important of these is Format. It returns a Variant with an expression formatted according to the instructions given in the format description.
• etc.

Studying the properties of these functions and their application will significantly expand the scope of Excel.

Example 5

Let's try to move on to solving more complex problems. For instance:

Given a paper document of the report of the actual level of costs of the enterprise. Required:

• to develop its template part using the Excel spreadsheet processor;
• create a VBA program that will request the initial data to fill it, carry out the necessary calculations and fill them in the corresponding cells of the template.

Let's consider one of the solutions.

Template creation

All actions are carried out on a standard sheet in Excel. Free cells are reserved for entering data on the name of the consumer company, the amount of costs, their level, and turnover. Since the number of companies (companies) for which the report is being drawn up is not fixed, the cells for entering values ​​based on the results and the specialist's full name are not reserved in advance. The worksheet is given a new name. For example, "Օ reports".

Variables

To write a program for automatic filling of a template, you must select the designations. They will be used for variables:

• NN - number of the current table row;
• TP and TF - planned and actual turnover;
• SF and SP - actual and planned costs;
• IP and IF - the planned and actual level of costs.

Let's denote by the same letters, but with the "prefix" Itog, the accumulation of the total for this column. For example, ItogTP - refers to a table column entitled “Projected Turnover”.

Solving a problem using VBA programming

Using the introduced designations, we obtain formulas for deviations. If you want to calculate in%, we have (F - P) / P * 100, and in total - (F - P).

The results of these calculations can best be entered directly into the appropriate cells in the Excel spreadsheet.

For the factual and forecast totals, they are obtained using the formulas ItogP = ItogP + P and ItogF = ItogF + F.

For deviations, use = (ItogF - ItogP) / ItogP * 100, if the calculation is carried out as a percentage, and in the case of a total value - (ItogF - ItogP).

The results, again, are immediately written to the appropriate cells, so there is no need to assign them to variables.

Before starting the created program, you need to save the workbook, for example, under the name "Report1.xls".

The button "Create reporting table" needs to be pressed only 1 time after entering the header information. You should know other rules as well. In particular, the "Add Row" button must be pressed each time after entering the values ​​for each type of activity into the table. After entering all the data, you need to click the "Finish" button and then switch to the "Excel" window.

Now you know how to solve Excel tasks using macros. The ability to use vba excel (see examples of programs above) may also be needed to work in the environment of the currently most popular text editor "Word". In particular, you can create menu buttons by writing, as shown at the very beginning of the article, or by writing code, thanks to which many operations on text can be performed by pressing the buttons on duty or through the View tab and the Macros icon.

It is often difficult for those who have never programmed to read program examples right away, you must first understand the basic principles, learn the words that programmers operate with. This page is specially designed for the very beginners.

So what are macros and how do you write them?

Macros are programs in Excel. Macros can do anything that a user can do manually. They are useful for processing data or for automating common actions.

Macros are written in VBA - Visual Basic for Applications. This abbreviation is worth remembering and using in search queries when finding the information you need. VBA is an object-oriented, hierarchical language. This means that you will have to manage objects that are subordinate to each other. For example, an Excel workbook is an object. There are sheets in it, there are cells on the sheets. Sheets, cells, ranges, and more are objects. Subordination can be roughly equated with nesting - for example, a cell is nested in a sheet, and a sheet is in a book.

You can perform a number of actions with objects, for example, open, activate, select, delete, copy, and much more.

Objects have properties. For example, a sheet can be visible or hidden, active or inactive. A cell has many properties that are also known to everyone: fill, borders, text color and size, alignment. The properties, of course, can be changed.

So, objects are "embedded" into each other and have different properties. Let's get to know them better.

We will not present the entire list here, because it is huge. We will restrict ourselves to what is needed even at the first stage.

Objects:

Workbook is an Excel workbook.

Sheet - sheet.

Range - range.

Cell - cell.

Row is a row.

Column is a column.

Actions with objects

Activate - activate, that is, "put the cursor". You can activate a book, sheet, cell.

Select - select. You can select one or more rows, one or more columns, a range or a cell.

Delete - delete. You can also delete rows and columns, ranges of cells or one cell, sheet.

Copy - copy.

And there is a separate action Paste - paste. If behind all the previous actions is the word "what?" (what to activate, what to copy), then the word "paste" is followed by the question "where?". Therefore, when writing a program, you need to indicate not what to insert, but where to insert it.

In addition to executing programs, Excel can "give information" according to the given commands. Here are some examples of such commands:

Sheets.Count - Gives the number of sheets in the book.

Date - Returns today's date in string format.

Len ("string") - gives the length of the string in the number of characters. In this example, the length is 6.

Now we need to say a few words about data types (variables).

To begin with, it is enough to know one thing: data are numbers, and there are strings, that is, text. With numbers, you can perform some actions (add, etc.), with strings - others (recognize the first character, for example).

All string values ​​in VBA are quoted. All titles / names of books or sheets are text, that is, a string value, so they should always be surrounded by quotes.

Both numbers and strings can be assigned to variables.

For instance:

S = "Hello"

You can override the values ​​of variables, for example, like this:

I = i + 10

S = s & "world!"

After that i = 11 and s = "Hello world!"

Here we are faced with the first operation on string values. The & sign means that the two lines must be concatenated. Order is important: if you write

s = ", world!" & s, we get s = ", world! Hello".

Since the variable s already contains quotes, you do not need to enclose it in quotes when using it. On the contrary, it is the absence of quotes that tells Excel that it should be perceived as a variable, and not as a text. That is, records:

S = "Hello world!"

H = "Hello world!"

will give the same result - assign the value "Hello, world!" to the variable h.

But the record

S = "Hello world!"

H = "s"

will assign the value "s" to the variable h.

I must say that objects in Excel are sometimes written in the singular, sometimes in the plural. How to remember in which case what is used? You can use the following rule: everything that is in Excel "is not a lot, is written in the plural, everything that is in a single copy is in the only one. In Excel" there are many books, many sheets and a lot of cells. They are all the same for Excel "i and can only be distinguished by name or coordinates. Therefore, the program uses the plural. For example:

Workbooks ("Book1"). Activate

Sheets ("Sheet1"). Copy

Rows (1) .Delete

Cells are defined by coordinates: the first is the row number, the second is the column.

For example, the command

Cells (1,1) .Activate

will place the cursor in the top-left cell.

You can "refer" to books and sheets not only by name, but also by number. Most often this is necessary precisely in working with sheets, when you need to sort through everything. When calling by number, the number does not need to be enclosed in quotes

A singular number is used, for example, when referring to an active cell or sheet, because obviously only one cell or one sheet can be active. For example, let's "remember" the row number of the active cell

I = ActiveCell.Row

When referring to an object, you do not always need to specify the full path to it: if no higher-level objects are specified, the macro will be executed in the currently active location.

For example, the command

Cells (1,1) .Copy

will copy the top-left cell on the active sheet.

And the team

Sheets ("Sheet1"). Cells (1,1) .Copy

will copy the top-left cell on sheet "Sheet1", regardless of whether this sheet is currently active or not.

After that, you can already write macros :)

But better read more about loops and conditional operator, and then about what is collections of objects and what they can give us.

What is a Macro? Macro Is a stored sequence of actions or a program written in VBA (Visual Basic for Application). A common question is how to write a macro in Excel? Just.

Those. if we need to perform the same actions several times, we can remember these actions and launch them with one button. I will not force you to learn the VBA language, and I will not even suggest many standard macros in this article. In fact, everyone can really create / write a macro in Excel. For this, there is the most interesting and unusual feature of Excel - Macro recorder(recording your actions as a code). Those. you can record your actions as on video and translate them into code (sequence).

In short, if you perform the same actions every day, it is worth figuring out how to automate this process. Read on how to write a macro in Excel?

1.Allow the use of macros

Menu (round button top left) - Excel Options - Trust Center - Trust Center Options - Macro Options. Check the box next to Include All Macros.

Or do the same on the Developer tab

2. Enable the Developer menu for quick work with macros

Menu (round button top left) - Excel Options - General - Show Developer tab.

In order for the created macro to be saved in the workbook, you must save the file in .xlsm or .xlsb. Click Save As - Excel Macro Workbook or Binary Workbook.

So how to write a macro in Excel?

It's simple. We go into Excel. At the bottom of the page, under the sheet tabs, there is a "Record Macro" button.

Press the button, the "Record Macro" window opens. We name the macro as we like in the Macro name: field. We set a keyboard shortcut with which we will call it later (optional).

Click OK. Macro Recorder recording has started.

Everything. Now we do the actions that we need to remember with a macro. For example, let's say we need to delete one row and color A1 in yellow.

During these manipulations, instead of the "Record Macro" button under the sheets, a square will light up, clicking on which you will stop the execution of the macro.

How to start what happened? Click the button, the Select Macro window appears, select the macro you want, and then click the Run button.

How to see what happened? Click the button. Select the required macro and click edit. The Macro Recorder window (VBA window) will open

The macro code should look like the following.

Sub Example1 () "Example1 Macro Rows (" 2: 2 "). Select Selection.Delete Shift: = xlUp Range (" A1 "). Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic.Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

'Is a comment character, i.e. this line is not part of the code. A macro must have a name (Sub Example1 ()) and an end (End Sub).

You can read how to create a button for a macro.

So, you can record any of your actions so that you can repeat them as many times as you like!

You can read how to save a macro for all books on your computer.

For this, the file Personal.

How to run a macro conditionally can be read

Good luck, I'm sure you'll find it useful!

Share our article on your social networks:

Objects such as macros in Excel will help you to competently organize the process of working in Excel.

Let's consider in more detail all the features of working with these objects in the MS Office program package.

Through the use of macros, every cell in your document can be automated. This is due to the fact that the user records all actions during their creation.

What are macros and why are they needed

You can work with macros in any of the programs of the MS Office package. First of all, they are needed in order to competently organize the user's work in the program.

They are necessary in order not to perform the same type of tasks and actions several dozen times.

Their creation and use will help to significantly save time and automate the robot in the program as much as possible.

Important! Thanks to the created macro, you can simply do the work instantly, while doing it manually would take a lot of time.

Its body, in fact, consists of instructions that tell the program what to do if users choose one or the other.

The concept of a macro can also be encountered in the Word program, but in Excel it has several advantages:

• First, it combines all instructions into one complete execution script, which allows you to optimize the load on the program and start working faster;
• You can call it using a key on the toolbar or using a special key combination. This will allow the user not to tear himself away from the keyboard while working;

Creating your own macros in Excel 2010, 2007, 2013

Let's take a closer look at examples of creation in Excel 2007, 2013:

• Open the document you are working with and for which you want to create a macro. By the way, each cell on which the action is performed must be worked out;
• Display the developer tab in the ribbon. To do this, open the "File" menu item and open the parameters, as shown in the figure;

• Then select the ribbon customization and add the developer window to the list of main windows, as shown in the figure below;

• Now you can go directly to creating the user macro itself. After its creation, each cell will be automated - this means that any cell of the user document will perform the same type of action that the user specifies;
• Find the special key to create in the developer tab. Its location is shown in the figure below;

• Press the key. A new window will appear, in which you must specify a name, a keyboard shortcut with which it will be enabled. You can also add a short description of how the macro works. This should be done if you have too many of them so as not to get confused;

• Then click "OK". The window will close and the recording process will start. To stop recording, press the corresponding key on the control panel;

• Now start performing the actions that will be recorded in the macro. In this case, each cell can be filled with certain data.
  You can also work with only one cell, after recording and turning on the macro, the same cell will be recorded according to the specified algorithm;
• Remember to press the stop data logging button. After completing all the above steps, it will be recorded and saved in the program.

How to enable and work with macros in Excel

To do this, follow the instructions below:

• On the developer tab, find a button called Macros. Click on it;

• Select the macro you need from the list and click the "Run" button;

• You can also run the required macro using the keyboard shortcut that was specified by the user at the initial stage of its creation;
• After pressing the execute button, all actions that were performed during the recording will be repeated.

A macro is best used when a specific cell needs to be copied many times.

Creating and deleting macros

Macros are created using a programming language called Visual Basic (or just the abbreviation VB).

At the same time, the creation process is so automated that even a user who has never encountered programming can create it.

For the first time, the technology of creating macros in the Excel program was improved and became available for use by ordinary users in the 2007 version.

The most convenient way to create them is in the following versions of the Word: 2007, 2010, 2013.

Macro menu

A macro consists of what are called macro operators. Macrooperators are the set of actions that it must perform in the order specified by the user.

There are different types of operators.

Some may even perform those actions that are associated with the execution.

At the same time, almost ninety percent of all macro-operators presented in the program perform the functions of ordinary buttons and icons on the program toolbar.

Thus, each cell will do its job.

The easiest way to start creating your own custom macro is to open the recorder.

The creation process boils down to the fact that the user needs to start the recording tool, then repeat all the steps that should be automated.

The macro will translate them into the programming language and remember all the commands performed by the user.

To delete a macro, follow the instructions:

1. Open the control window using the "Macros" key on the main developer tab;
2. Select the object you need and click on the delete button in the right part of the window;

Topic 2.3. Presentation software and office programming basics

Topic 2.4. Database management systems and expert systems

2.4.11. Training database with the main button form "Training_students" - Download

VBA programming and macros

2.3. Presentation software and office programming basics

2.3.7. Office programming basics

Visual Basic for Applications: Visual Basic for Applications (VBA)

VBA is a subset of the visual programming language Visual Basic (VB) that includes almost all of the tools for creating VB applications.

VBA differs from the VB programming language in that VBA is designed to work directly with Office objects; it cannot create a project independent of Office applications. Thus, in VBA, the programming language is VB, and the programming environment is implemented as a VB editor, which can be activated from any MS Office application.

For example, in order to open the VBA editor from a PowerPoint application, you need to run the Tools / Macro / VBA Editor command. You can return from the editor to the application by choosing the Microsoft PowerPoint command from the View menu or by pressing Alt + F11.

Using a set of controls and a form editor built into the VBA editor, the user can create a user interface for the project under development with a display form. Controls are objects, and a number of possible events are defined for each object (for example, mouse click or double click, key press, object dragging, etc.).

Each event manifests itself in certain actions of the program (responses, reactions). A custom form allows you to create application dialog boxes. The VBA programming language is used to write program code, for example, to create user functions in Excel.

The fact that the VBA programming system is designed to work with Office objects allows it to be effectively used to automate activities associated with the development of various types of documents.

Consider the algorithm for creating user functions in VBA:

1. Call the VBA code editor window by executing the Service / Macro / Visual Basic Editor command or by pressing Alt + F11.

2. Execute the menu item of the editor Insert / Module (Insert / Module).

3. Next, execute Insert / Procedure. In the Add Procedure dialog box that opens, enter the name of the function (for example, SUM5) and set the switches: Type (type) - in the position Function (function); Scope - set to Public and click OK.

Rice. one.

4. In the editor window for VBA programming, a function template will appear: the title is Public Function SUM5 () and the end is End Function, between which you need to place the code of the function body.

Rice. 2.

5. Next, enter the list of function parameters, for example, in parentheses we indicate (x, y, z, i, j), the data type (for accurate calculations) and the type of value returned by the function (we will not enter it in this example). In addition, we introduce the body of the function, for example, SUM5 = x + y + z + i + j. As a result, we get the following program text:

Public Function SUM5 (x, y, z, i, j)
SUM5 = x + y + z + i + j
End Function

6. Return to the Excel application window, in which, for example, we need to add up five values.

7. We execute "Insert / Function" and in the opened window of the wizard of functions select the category "User-defined", and in the window "Select function" select SUM5 and click OK.

Rice. 3.

Rice. 4.

Macros

Creating a macro

Let's create a macro to automatically add two numbers in cells A1, B1 and place the result in cell C1, and fill cell C1 with turquoise.

Algorithm for creating a macro for the task at hand:

1. Select Tools / Macro, Start Recording.

2. In the Macro name box, enter a name for the macro. The first character of a macro name must be a letter. Spaces are not allowed in the macro name; you can use underscores as word separators.

3. To run the macro using a keyboard shortcut, enter a letter in the Keyboard shortcut box. You can use CTRL + letter (for lowercase letters) or CTRL + SHIFT + letter (for uppercase letters), where letter is any letter key on the keyboard. Do not select the default keyboard shortcut, as the selected keyboard shortcut overrides the standard Microsoft Excel keyboard shortcuts at the time of this workbook.

4. In the Save box, select the workbook where you want to save the macro. Save the macro to This Book. To create a short description of the macro, enter the required text in the Description field. The screenshot shows an example of filling the "Record Macro" dialog box

Rice. 5.

5. Click OK.

6. Execute the macros to be recorded.

Rice. 6.

7. Click the Stop Recording button on the Floating OS toolbar (Stop Recording) or Tools / Macro / Stop Macro.

When you finish recording a macro, it appears in the list of macros under its own name.

The VBA editor automatically recorded a sequence of macros or a program according to the performed actions (Fig. 7.).

Rice. 7.

To call a macro, you must execute the Service / Macros / Macros command. After that, in the dialog box with the list of macros, you can find it by name and click the Run button.

Assigning a toolbar button to run a macro

A macro can be launched using a button on the built-in toolbar, for this you need to run:

1. Select the Settings item in the Service menu.
2. In the Options dialog box, select the Commands tab and select the Macros parameter in the Categories list, in the Commands list, select "Custom Button".
3. From the Commands list, drag a custom button onto the toolbar.
4. Right-click this button and choose Assign Macro from the context menu.
5. Enter a name for the macro in the Macro name field.

Assigning a graphic object area to run a macro:

1. Create a graphic object.
2. Apply the context menu to the selected graphic object.
3. Select the Assign Macro command from the context menu.
4. In the "Assign macro to object" dialog box that appears, enter the name of the macro in the Macro name field, then click OK.

Macro editing is carried out by the VBA editor, for this you need to do the following:

1. Select the Service / Macro / Macros command.
2. Select the name of the macro to be changed from the Name list.
3. Pressing the Modify button will open the Visual Basic window, in which you can edit the commands of the selected macro written in the Visual Basic language.

Removing a macro:

1. On the Tools menu, select Macro, and then the Macros command.
2. In the list of macros in the current workbook, select the macro that you want to delete and click the Delete button.

Renaming a macro

To rename a macro, you must enter the macro editing mode and change the title in the program text. The new name will automatically replace the old one in the macro lists, and a macro with a new name will be called using the shortcut keys.