Macros

Macros

Conditional formatting

What is "advent of conditional formatting" when and why does he occur? How to deal with him manually and with a special macro?

How to open a new Excel in a separate window

5 ways to launch a new independent instance of Microsoft Excel so as not to wait until the presence in the previous formula, the execution of a long macro, etc.

Duplicates inside the cell

How to deal with repetitions in the text inside the cell: detect them, highlight them or delete. With the help of formulas, macros or Power Query queries.

Launch of the time macro

How to run macros you need at a certain time, with a certain frequency or on schedule? For example, automatically update a heavy and slow report every morning at 5:00?

The last word

How to extract from the text the last word or fragment according to the specified separator symbol. Disaster of several ways (formulas, macros, Power Query).

How to use the personal book of macros

Personal Macros (Personal Macro Workbook) - What it is, how to create it and use it as a personal library for storing and launching the most necessary macros and functions in the daily work of macros.

Generation of duplicate string

How to propagate some rows in the table by creating a given number of duplicates for them. The analysis of two ways: "in the forehead" macro and beautifully - via Power Query.

Utility for VBA programmer

Overview of useful programs, utilities and add-ons for a programmer on Visual Basic. If you at least sometimes create macros on VBA in Excel, then something from this list is accurately useful.

Creating macros and user functions on VBA

What is macros. How to create macros to automate repetitive actions and expand the Microsoft Excel functionality. How to add your user function (UDF) to the standard set of Excel functions. Macros and security.

Coordinate allocation

The automatic coordinate illumination of the current line and the column "cross" is extremely convenient when viewing large tables!

Creating backup copies of valuable files

How to restore lost work if you come out of Excel without saving changes? Or need to return to the state that was 2 hours ago? How to teach Excel to automatically save a backup copy of a valuable file to a specified folder and save lost data?

Translite

A small macro to quickly transform Russian symbols to English, i.e. Cyrillic in translite (Pupkin in pupkin, etc.)

What is macros in Excel

Task Automation With Macro Recording Tool - Excel

Note: We try to provide you with relevant reference materials in your language as soon as possible. This page is automatically translated, so its text may contain inaccuracies and grammatical errors. It is important for us that this article be useful to you. We ask you to pay a couple of seconds and report whether it helps you, using the buttons at the bottom of the page. For convenience, also give a link to the original (in English).

To automate recurring tasks in Microsoft Excel, you can quickly write a macro. Suppose you have dates in a different format and you want to apply one format to all of them. This can be done using a macro. You can write a macro that applies the desired format, and then run it if necessary.

When macro recording, all the steps described in Visual Basic are recorded for applications (VBA) code. These actions may include entering text or numbers, pressing cells or commands on a tape or in the menu, formatting cells, rows or columns, as well as data imports from an external source, such as Microsoft Access. Visual Basic (VBA) application is a subset of the powerful programming language of Visual Basic, which is included in most Office applications. Despite the fact that VBA provides the ability to automate the processes between Office applications, you do not need to know the VBA code or software programming, if necessary.

It is important to know that during the recording of the macro is recorded almost all your actions. Therefore, if you make an error, for example, press the wrong button, the macro recording tool will register this action. In this case, you can again record the entire sequence or change the VBA code. Therefore, before recording the process, it is necessary to work well. More precisely, you will write down the sequence, the more effectively the macro will work.

Macros and VBA tools are located on the tab. Developer which is hidden by default, so you first need to turn it on. For more information, see the article Displays the Developer tab.

Macro record

Before recording macros it is useful to know the following:

Macro recorded to work with an Excel range will be performed only for cells of this range. Therefore, if you add a new string to the range, the macro will not apply to it.

If you need to write a long sequence of tasks, we advise you to use some smaller macros instead.

Macro may contain both exception tasks. The macro process can cover other Office applications and other programs that Support Visual Basic for applications (VBA). For example, you can record the macro, which first updates the table in Excel, and then opens Outlook to send it by email.

To record a macro, follow the instructions below.

On the tab Developer in a group The code Press the button Macro record .

In field Macro name Enter the name of the macro. Make a name clear so that you can quickly find the desired macro.

Note: The first character name of the macro should be the letter. Subsequent characters can be letters, numbers or underscore signs. The macro name should not be contained; As dividers of words should use underscores. If you are using a macro name, an error message may appear, indicating an invalid macro name.

To assign a key combination to start macro, in the field Keyboard shortcut Enter any line or capital letter. It is recommended to use keyboard shortcuts Ctrl +Shift. Since they will replace themselves the standard shortcuts of the keys in Excel, while the book containing the macro is open. For example, if you assign a key combination Ctrl + Z. (Cancel), you will not be able to use it for the "Cancel" function in this Excel instance.

In the list Save B. Select the book in which you want to save the macro.

As a rule, the macro is preserved in the specified location. books But if you want the macro to be available when using Excel, select Personal Book of Macros. If you choose a command Personal book Macros , Excel will create a hidden personal macro book (personal. XLSB), if it does not yet exist, and will retain the macro in this book.

In field Description If necessary, enter a brief description of the actions of the macro.

Although the "Description" field is optional, it is recommended to fill it. In addition, it is desirable to introduce a clear description, which will be useful to you and everyone who launches the macro. If you have many macros, the descriptions will help you quickly determine what they need.

To start recording a macro, click OK .

Perform the steps to write.

On the tab Developer in a group the code Press the button Stop recording .

Working with macros recorded in Excel

On the tab Developer Click Macros To view the macros associated with the book. In addition, you can press the keys Alt + F8. . This opens the dialog box. Macro .

Attention: Macros can not be canceled. Before you first launch a recorded macro, save the book or create a copy of it to prevent undesirable changes. If you are not satisfied with the results of the macro, you can close the book without saving it.

Below are more information about working with macros to Excel.

Information about macros security and their value.

Macros can be launched in various ways, for example, using a keyboard combination, a graphical object, a quick access panel, buttons, or even when opening a book.

Using the Visual Basic editor, you can change the macros attached to the book.

If the book contains a VBA macro to be used anywhere else, this module can be copied to another book using the Microsoft Visual Basic Editor.

Macro Appointment Object, Figure or Graphic Element

On the sheet, right-click the object, drawing, shape or item to which you want to assign an existing macro, and then select the command Assign Macro .

In field Assign Macro Select the macro you want to assign.

You can assign a macro icon and add it to the quick access panel or tape.

You can assign macros forms and elements of ActiveX on a sheet.

Learn how to enable and disable macros in Office files.

Opening of the Visual Basic Editor

Press keys Alt + F11 .

Learn how to find a certificate for the elements of Visual Basic.

Working with the recorded code in the Visual Basic Editor (VBE)

Using the Visual Basic Editor (VBE), you can add your own variables to the recorded code, control structures and other elements that do not support macro recording tool. Since macro recording tool fixes almost every step performed during recording, it may also be necessary to remove unnecessary code. View recorded code - a great way to learn to program on VBA or hone your skills.

An example of changing the recorded code can be found in the article Getting Started with VBA in Excel.

Macro record

Before recording macros it is useful to know the following:

Macro recorded to work with an Excel range will be performed only for cells of this range. Therefore, if you add a new string to the range, the macro will not apply to it.

If you need to write a long sequence of tasks, we advise you to use some smaller macros instead.

Macro may contain both exception tasks. The macro process can cover other Office applications and other programs that Support Visual Basic for applications (VBA). For example, you can record the macro, which first updates the table in Excel, and then opens Outlook to send it by email.

Macros and VBA tools are located on the tab. Developer which is hidden by default, so you first need to turn it on.

Go to Settings _GT_ Excel . Toolbar _GT_ tape _amp_ .

In category Set up tape in the list Main tabs Check the box Developer and then click Save .

To record a macro, follow the instructions below.

On the tab Developer Press the button Macro record .

In field Macro name Enter the name of the macro. Make a name clear so that you can quickly find the desired macro.

Note: The first character name of the macro should be the letter. Subsequent characters can be letters, numbers or underscore signs. The macro name should not be contained; As dividers of words should use underscores. If you are using a macro name, an error message may appear, indicating an invalid macro name.

In the list Save B. Select the book in which you want to save the macro.

As a rule, the macro is preserved in the specified location. books But if you want the macro to be available when using Excel, select Personal Book of Macros. If you choose a command Personal book Macros In Excel will be created a hidden personal book of macros (personal. XLSB), if it does not exist, and preserving the macro in this book. Books in this folder are opened automatically when you start Excel, and the code stored in the Personal Book of Macros will be specified in the Macro dialog box, which is described in the next section.

To assign a key combination to start macro, in the field Keyboard shortcut Enter any line or capital letter. It is recommended to use keyboard shortcuts that are not yet assigned to other commands, as they will override the standard keyboard shortcuts in Excel, while the book containing the macro is open.

In field Description If necessary, enter a brief description of the actions of the macro.

Although the "Description" field is optional, it is recommended to fill it. It is useful to enter a clear description with any useful information that can be useful to you or other users who will run the macro. If you have many macros, the descriptions will help you quickly determine what they need.

To start recording a macro, click OK .

Perform the steps to write.

On the tab Developer Click Stop recording .

Working with macros recorded in Excel

On the tab Developer Click Macros To view the macros associated with the book. This opens the dialog box. Macro .

Note: Macros can not be canceled. Before you first launch a recorded macro, save the book or create a copy of it to prevent undesirable changes. If you are not satisfied with the results of the macro, you can close the book without saving it.

Below are more information about working with macros to Excel.

What is a macro in Excel and what is it intended for?

Good time, friends. In today's release, I will talk about the most interesting function in the most chassis office package of Microsoft Office programs. Those who constantly work with Excel tables know how many routine operations have to sometimes do with content. If there are many documents - you have to repeat the same actions repeated. From the allocation of mouse and copy to more complex - such as calculations, filling, cleaning individual graph, creating tables, layouts, and so on.

In order not to repeat the same actions every time you can automate the process and save time. You record your actions (allocating document cells, copying, formatting, formulas) in a macro. This command is placed in the form of a button on the toolbar. When you press the button, all your previously recorded actions are performed automatically. This is the "macro".

For me personally, acquaintance with macros in one time has become a study of programming. The fact is that Microsoft Office is also a light and understandable environment for this. It has its own built-in language - Visual Basic for Application or abbreviated VBA. . And when you write down macros, you like becoming a programmer. At the same time, it is not necessary to know the language in the first stages. The program code can be seen already later, in the editor and study. How to look - about it just tell.

VBA. Allows you to create powerful tools when working with tabular documents for solving many tasks in the form of individual windows, buttons, lists, flags - all visually. And experienced programmers know that macros can be used to not dial commands with their hands, but to get ready-made code from just recorded macro. But first things first. Those who, after reading the article, will be interested in and wants to study the language VBA alone To help the book. And now we will understand in detail what kind of "macros", how to include them.

What does macros mean and how to work with them?

So, the macro is a kind of program code that describes the sequence of your actions in the document. Even the movement of your cell cursors is recorded, pressing the keys. First you write down the macro. When you need, you in Excel launch this code for execution and all recorded steps are performed automatically. For example, you have the same table, the layout of which you create a new week, change the design, or clean the graphs. You press the button, and now what you spent 5-10 minutes have taken just one second.

First you need to configure Excel to work with macros. It's simple. On the example of Office 2007 I will show you how. In later editors, everything is done in almost similar. First go to "Parameters":

First we will configure the "tape"; You need to enable the "Developer Mode":

If you are going to work seriously with macros - you need to turn on another setting. Here we go to the Security Management Center and configure the "Security Management Center" parameters:

You need to disable all restrictions on the launch of macros. This will allow you not only to work with your macros, but also to run already ready for someone created.

You also need to trust access to the object model of VBA projects. After all, on the Internet or at colleagues, you can find a lot of ready-made and interesting projects. VBA. written for Excel, Word and use.

What is the macro in Excel 2007?

We still learn to write down simple macros. Make it very simple. In "Ribe", we now have the corresponding icons "Developer".

In this mode, the other functions will be available to us - the "constructor mode". You can independently create windows, buttons. And in the Visual Basic editor, you can edit the created macros. So, I will show how it all works. When you click the "Macro Record" button, you will first need to fill it with its name (without spaces), hot key keys (optional) and place where it will be saved:

Depending on the task, you can save macros in a separate document or in the "Personal Book". In the first case, it is recorded within the document and will only be executed in the document. In the second case, it can be called before creating a document. It is better to demonstrate clearly for what makes macros. Task: I need to create: a document Excel, in the document Create a table of a specific format and computing inside. Watch the video what happened:

You yourself saw that the process of creating one simple demo - table occupies from five or more minutes. And we created a table with a macros in 2 seconds. And if we are talking about complex projects? The answer is obvious by creating a procedure once, you will save time and increase the performance of your work.

After the post is completed when the Excel is closed, the program will definitely ask to keep changes in the "personal book" of macros. Do not forget to agree; Otherwise, the recorded macro will disappear and everyone will have to do again.

Important! The "Personal Book" of macros is stored in the user folder on the path C: Users% computer user% AppDataRoamingMicrosoftExcelXLStart. This book can be divided with others or delete if something failed.

Our macro is now available on the Macros button, you can run it from there.

What does the macro mean in Excel 2016 and why is it designed?

The process of recording macros does not differ from early versions; In another simple example, I will tell you about another way to call Macrocomand. The creation of macros will allow you to get acquainted with programming skills and who knows, maybe this is an interesting thing in hobbies.

Imagine that we have a rather cumbersome table, which each time it has to be cleaned of the data of the previous report. Task: Make a button in the document, when you click on which the desired cells are automatically cleaned. Let's start.

First, do not forget to make MSOFFICE 2016 settings. We also go to "Parameters", but then go to "Set up a tape"

After the "developer" will be visible on the "tape". Go there. Our personal book of macros is still on the spot, open it:

Let's start our previous macro, we admire how the program itself creates and fills the table. In our example, there are two columns "Number" and "price", the contents of which have to be cleaned. We automate the process.

How to enable macros in Excel 2016 video

You can call macros for execution, as we have already done in the last example. This is not clear and sometimes not very convenient. Therefore, we will do it anyhow - create a button in the document, with which we will clean the contents.

Programming begins, insert the button to any place of our document from the Developer menu. The cursor change to the crossroads, then draw the button in the right place:

Our button will automatically assign a macro. The name of the macro, the key combination, put free, we save in the "book". This means that the button will only work in this document.

Next, the program will offer a method for creating a macro. If you are already an advanced VBA programist, you can write the code with your hands by clicking "Create". But today we will use the already familiar method - the recording of our actions:

Recording started! We are going to clear the selected graph graphs, so simultaneously select the desired ranges in the "Quantity" and "price" graphs. To do this, first select the Count "Quantity", then clamp the Ctrl key and do not release it allocate the necessary cells in the price column, after letting go.

Using the CTRL key has been selected, it helps to select columns and areas of different cell ranges simultaneously. Do not neglect this in the case of large tables.

Now that the desired range of cells is selected, pressed the DEL key on the keyboard and delete the contents of the cells. Stop recording Macro:

Like any object with the button, you can make various actions - copy, delete, format, assign macros to them. We have yet change the name. To get to the properties of the button, you need to right-click it:

I changed the text to "Clear". Color, text size The font in the buttons can also be changed to taste. Close to this in the "object format":

Everything is about the same way as when the cell formatting. And change the size of the button itself and its location can be stretched by the edges or dragging it:

Well, lastly let's look into the VBA editor and see what our macro program code written it looks like. To do this, we go to our "macros", choose the desired and click "Log in" or "Change":

Another way - you can right-click on the button and select "Assign Macro"; The result will be similar. An VBA editor opened in which you can do a lot of interesting and useful. We are still looking at that we have programmed there:

Simple and understandable sample code; After comments (highlighted green), the code "Select the range of cells is first one, then another, then delete the contents." You can close the editor as the usual window by clicking on the cross. I hope that such macros - you figured out. At the same time they learned how to become a programmer. Good luck in mastering the program!

School programming

Error message

Why do you need macros in Excel?

Many of you probably heard the term "macro". Sometimes when you start some Word documents, and more often Excel, you have populated a warning: "This document contains macros that can harm your computer. For the purpose of security, macros were disabled ", etc. And many this warning scares, although a large proportion of truth in this warning is still present. But, this refers to the documents that have fallen to you from unknown sources, namely from the network, by mail, etc. What is so dangerous macro and why are they needed then if their use is unsafe? Consider further, but as long as we give the definition of a macro.

What is "macro"?

Macro is a program that contains a set of instructions that perform any actions, both automatically and at the request of the user, i.e. Perform clearly and implicitly. Macro from abbreviated " Macrocomanda " This concept was widespread thanks to Microsoft Corporation, which has implemented the possibility of writing macros in their products, namely, everyone's favorite and well-known Office (Word, Excel, Access, etc.). Used language in macros - Visual Basic, as well as Microsoft product, but with some limitations.

How do macros work?

Thanks to the introduction of the Visual Basic language in the MS Office, Microsoft managed to significantly expand the functionality of its products. Macrocomands recorded by users or written by programmers are able to automate most of the processes and significantly reduce the time to process data. Thereby optimizing employee working hours. To fulfill the macros, you need your software environment in which these commands are able to work. Such a medium are, for example Excel, Word, Access. When writing complex macros, their launch and execution, as a rule, is limited to the workstation (PC) on which it was written. Complicated, meaning, connecting additional components and objects that simply may be absent on another PC. This happens because the program code is not translated into the executable machine code and in addition to which the necessary modules and libraries are applied, and is performed through the interpreter. But, there are advantages in this approach, namely, the ability to debug immediately, "on site", when any errors occur, view the code and add it new instructions, expanding the macro functionality. As a rule, macros written for Excel do not work in Word or Access only if the basic set of procedures and functions are not used that do not affect the application objects.

How do macros help in work?

When working with large amounts of data, which is necessary, or otherwise handle, as well as when performing monotonous information processing actions in tables (for example, the formation of consolidated tables of a specific format), macros are indispensable. Example: You daily need to receive data from the database, to produce some calculations with these data, then from the results to reduce the consolidated table. All you have, let's say, 2 hours. Having developed and implementing such a macro that all these actions will be done independently, you have time to cut to a pair of minutes, or rather seconds. The rest will go to the opening and receiving data. And if more to the calculations "manually" add to the mechanical errors that are necessarily coming, and which at best you will find and search, then it will add the time to that two hours. The macro during competent development and well tested, avoids such errors.

This is one of many applications. Another example, on personal experience. When working with daily storage data, I had to fill in the "counterpart" field (ka) with the corresponding name available in the database. The name of the KA must be identical. Creating a list is very uncomfortable. The list consists of not one hundred ka. I wrote a macro that runs on execution, when you press in the appropriate column, right-click. The form opens, with the list of ka in which there is also a "hot search" in the list ie It is enough to start typing the combination of characters contained in the title and the list immediately filters and leaves suitable, then it remains to choose from two three boosters. All about everything, no more than 5 seconds. Here is the option. And examples can be given a bunch, depending on the specifics of the work.

What is the danger of macros?

As I said above, macros can be both useful and dangerous. It all depends on what purpose and who he was written. What is the danger? Since the Visual Basic language is used in writing macros, then having all its functionality, the attacker is able to write a virus in this language - Macrowurus. Macrovirus is able to damage all the data, it is capable of working as a spy program, etc. The lifetime of such a virus - while the application is running (Excel, Word, etc.). After the introduction of such a virus, in the future, its launch will be carried out in a hidden mode. Macros are also capable of acting as an intermediary to introduce more serious threats to the entire system. For example, by activating the macro, a copy or generation of a Trojan program will be copied, which runs running the operating system, followed by its launch. And this is the threat of work through the entire system.

Protection options from such macros:

  • Increase the security level of Excel (the method is not convenient because and beneficial macros will not work);
  • Do not open files from unknown sources and do not ignore security warnings when opening such files;
  • If Windows 7 operating system is installed, preventing the launch of applications with the signature "Unknown Source".

At this, I complete the introductory article on macros. In the future, I will tell and show ways to write macros in Excel. I will try to pick up interesting and useful examples, as well as consider what the potential has excel, if you look at the eyes of a programmer.

P. S. Using the mixture and fantasy on the VBA, you can write the simplest and interesting games. If you are interested in this application, you can familiarize yourself with the games written for Excel ( Games in Excel ).

What is a macro and where to look for it?

Tips_macro_basic_video.xls. (63.5 KIB, 3,070 downloads)

Probably, many have heard this word " macro "But not everyone has an exact idea that this is, if they looked at this page. Macro is a macro (so it sounds in the correct translation from the English language) containing the sequence of actions recorded by the user. The recording is made using the programming language Microsoft Office built into the Microsoft Office package - Visual Basic for Application ( VBA. ).

Why do you need macros? They need them in order to save you from the routine execution of the same actions. For example, you have to delete a few columns from daily reports every day and add new lines for the header, etc. Boring and tiring. You simply can write all these actions by the macrorecore, and in the future just call the recorded macro and it will do everything for you. What is important, to use the macrorecore and macro record, you should not have any programming skills. In this article:

Preparing for macro recording Before starting working with macros, it is necessary to resolve their execution, because By default, they are disabled. To do this, do the following:

  • Excel 2003: Service-safety-level of macros "Low"
  • Excel 2007: Office button Excel Settings (Excel Options) Security Management Center (Trust Centr) Security Management Center Parameters (Trust CENTR SETTINGS) Macro Settings (Macro Settings) Allow all macros (Enable All Macros)
  • Excel 2010: File (File) Options (options) Security Management Center (Trust Centr) Security Management Center Parameters (Trust CENTR SETTINGS) Macro Settings (Macro Settings) Allow all macros (Enable All Macros)

After changing the macro security settings, you must restart the Excel application: close it completely and open again. Only after this change will take effect.

Before you start recording a macro It is necessary to remember the following :

  • Macrorecore holds absolutely all your actions - errors, scrolling the screen, switch between sheets, selection of cells, etc. Therefore, before recording of your actions, you must first thoroughly think over all your actions so that in the future it is only what you really need to record and reproduce. This will reduce both the code itself and the time of its execution.
  • Macrorecore holds actions performed only within Microsoft Excel. If you switch to another application - the actions in this program will not be recorded. If you close Excel - recording will stop
  • Macrorecorder can record only the actions that can be done manually (those available for execution from the menu and panels)
  • If during the code you made an erroneous action and pressed the cancel button (Ctrl + Z) - the canceled action will not be written to the macro, as if you simply did not do it

Recording macro for users Excel 2003. Macro recording is possible through the menu: Service Macro Start recording

For users Excel 2007-2010 And older:

  • With tabs Developer (Developer) : Group Code (Code) Macro Record (Record Macro) Display tab Developer (Developer) (if not yet displayed) Excel 2007: Excel Settings (Excel Options) Basic (Popular) - Tick on the opposite Show the Developer tab on the ribbon (Show Developer Tab On Ribbon) Excel 2010: File (File) Options (options) Ribbon Setup (Customize Ribbon) - Tick opposite the developer (Developer)
  • From the status bar: Press the "Macro Record" button to record a macro from the status string you need to make sure that the status string settings are a tick in front of the macro recording (Macro Recording) See Settings Status Rows You can right-click on the lower border of the application: If this check mark is enabled, then the status bar will be the icon displaying the current recording status. No post Watch is recorded

Macro recording options Before recording, you can set the name of the macro recorded and assign a key combination, when you click this macro will be started. After pressing the button to start recording the macro, a window will appear: The name of the macro - should not contain spaces, commas, colosts, etc. Symbols of Cyrillic, Latin, Figures and Lower Underlining (I recommend giving macros names containing only Latin letters) . Macro name should always start with the letter and should not match the embedded name Excel or the name of another object in the book (for example, should not wear the name Workbook, Cells or Stajniga) . It is better to give a macro at once a clear name, reflecting the approximate essence of what he does. Keyboard shortcut - You can assign a macro to a key combination. Available assigning any number or letters in combination with Ctrl . Save in - choose the location of the recorded macro. Options are available:

  • This book (This Workbook) - The macro will be recorded in the same book from which the entry was launched. The recorded macro will be available from the application only if the book is open
  • New Book (New Workbook) - A new book will be created in which the macro is recorded. The recorded macro will be available from the application only if the book is open
  • Personal Macro Book (Personal Macro Workbook) - The most interesting option. If you choose it, the macro will be saved in a separate book Personal.xls (for Excel 2007 and above - Personal.xlSB) . Macros recorded in this book are available from all open Excel books and this book connects automatically when running Excel itself. Those. One day, writing out somewhere macro in this book - it will now be available from any book regardless of whether the book has been opened from which the macro was recorded or not. Initially, this book is missing and is created at the time of the first appeal to it. (i.e. as soon as you first chose to save on the personal book of macros)

Playing the macro after recording the macro to run it in different ways:

  1. Using a key combination: Alt + F8 This method is universal and can be used from any version of Excel. Just press the Alt + F8 key combination and select the desired macro from the list:
  2. Call from the menu: Excel 2007-2010 and older: tab Developer (Developer) Macros (Macros) Excel 2003: Service Macro Macros
  3. By pressing your own button on the sheet: you can place on the sheet your own button, by clicking which the recorded macro will start. I described this process in detail in the article: how to create a button to call a macro on a sheet
  4. Using the hot keys assigned to the macro: if you assign a key combination before recording, you can also call this macro and pressing this combination. Hot keys Macro can be assigned after recording: Alt. +F8. → Select the desired macro → Options.
  5. At the occurrence of a specific event in a sheet or book:

Before trying to use this method, it is recommended to learn the article What is a module? What are the modules? To do this, first need to insert the macro code into the body of the event procedure. (more about Event Procedures) . For example, if you select the procedure of the Change sheet, an empty procedure will automatically be inserted into the sheet:

Private Sub Worksheet_Change (Byval Target AS Range) End Sub

Channel in Telegram

Why do you need macros in Excel?

Many of you probably heard the term "macro". Sometimes when you start some Word documents, and more often Excel, you have populated a warning: "This document contains macros that can harm your computer. For the purpose of security, macros were disabled ", etc. And many this warning scares, although a large proportion of truth in this warning is still present. But, this refers to the documents that have fallen to you from unknown sources, namely from the network, by mail, etc. What is so dangerous macro and why are they needed then if their use is unsafe? Consider further, but as long as we give the definition of a macro.

What is "macro"?

Macro is a program that contains a set of instructions that perform any actions, both automatically and at the request of the user, i.e. Perform clearly and implicitly. Macro from abbreviated " Macrocomanda " This concept was widespread thanks to Microsoft Corporation, which has implemented the possibility of writing macros in their products, namely, everyone's favorite and well-known Office (Word, Excel, Access, etc.). Used language in macros - Visual Basic, as well as Microsoft product, but with some limitations.

How do macros work?

Thanks to the introduction of the Visual Basic language in the MS Office, Microsoft managed to significantly expand the functionality of its products. Macrocomands recorded by users or written by programmers are able to automate most of the processes and significantly reduce the time to process data. Thereby optimizing employee working hours. To fulfill the macros, you need your software environment in which these commands are able to work. Such a medium are, for example Excel, Word, Access. When writing complex macros, their launch and execution, as a rule, is limited to the workstation (PC) on which it was written. Complicated, meaning, connecting additional components and objects that simply may be absent on another PC. This happens because the program code is not translated into the executable machine code and in addition to which the necessary modules and libraries are applied, and is performed through the interpreter. But, there are advantages in this approach, namely, the ability to debug immediately, "on site", when any errors occur, view the code and add it new instructions, expanding the macro functionality. As a rule, macros written for Excel do not work in Word or Access only if the basic set of procedures and functions are not used that do not affect the application objects.

How do macros help in work?

When working with large amounts of data, which is necessary, or otherwise handle, as well as when performing monotonous information processing actions in tables (for example, the formation of consolidated tables of a specific format), macros are indispensable. Example: You daily need to receive data from the database, to produce some calculations with these data, then from the results to reduce the consolidated table. All you have, let's say, 2 hours. Having developed and implementing such a macro that all these actions will be done independently, you have time to cut to a pair of minutes, or rather seconds. The rest will go to the opening and receiving data. And if more to the calculations "manually" add to the mechanical errors that are necessarily coming, and which at best you will find and search, then it will add the time to that two hours. The macro during competent development and well tested, avoids such errors.

This is one of many applications. Another example, on personal experience. When working with daily storage data, I had to fill in the "counterpart" field (ka) with the corresponding name available in the database. The name of the KA must be identical. Creating a list is very uncomfortable. The list consists of not one hundred ka. I wrote a macro that runs on execution, when you press in the appropriate column, right-click. The form opens, with the list of ka in which there is also a "hot search" in the list ie It is enough to start typing the combination of characters contained in the title and the list immediately filters and leaves suitable, then it remains to choose from two three boosters. All about everything, no more than 5 seconds. Here is the option. And examples can be given a bunch, depending on the specifics of the work.

What is the danger of macros?

As I said above, macros can be both useful and dangerous. It all depends on what purpose and who he was written. What is the danger? Since the Visual Basic language is used in writing macros, then having all its functionality, the attacker is able to write a virus in this language - Macrowurus. Macrovirus is able to damage all the data, it is capable of working as a spy program, etc. The lifetime of such a virus - while the application is running (Excel, Word, etc.). After the introduction of such a virus, in the future, its launch will be carried out in a hidden mode. Macros are also capable of acting as an intermediary to introduce more serious threats to the entire system. For example, by activating the macro, a copy or generation of a Trojan program will be copied, which runs running the operating system, followed by its launch. And this is the threat of work through the entire system.

Protection options from such macros:

  • Increase the security level of Excel (the method is not convenient because and beneficial macros will not work);
  • Do not open files from unknown sources and do not ignore security warnings when opening such files;
  • If Windows 7 operating system is installed, preventing the launch of applications with the signature "Unknown Source".

At this, I complete the introductory article on macros. In the future, I will tell and show ways to write macros in Excel. I will try to pick up interesting and useful examples, as well as consider what the potential has excel, if you look at the eyes of a programmer.

P. S. Using the mixture and fantasy on the VBA, you can write the simplest and interesting games. If you are interested in this application, you can familiarize yourself with the games written for Excel ( Games in Excel ).

To start a little about terminology.

Macro - This is a code written in EXCEL Language Built-in VBA. Visual Basic for Application). Macros can be created both manually and recorded automatically using the so-called macrorecodera.

Macrorecoder - This is a tool in Excel, which step by step writes all that you perform in Excel and converts it into the code in the VBA language. Macrorecoder creates a very detailed code (as we will see later), which you can edit if necessary.

The recorded macro can be launched an unlimited number of times and Excel will repeat all recorded steps. This means that even if you do not know anything about VBA, you can automate some tasks, just writing your steps and then reuse them later.

Now let's plunge and see how to write a macro in Excel.

Display the Developer tab in the menu ribbon

Before recording the macro, add the "Developer" tab to the tape. To do this, follow these steps:

  1. Right-click on any of the existing tabs on the ribbon and click "Configure the Tape". It will open the Excel Settings dialog box. ENTER
  2. In the Excel Settings dialog box, you will have the "Configure Tape" options. On the right in the "Main tabs" panel, select the Developer check box. To get the result. Then stop the macro recording by clicking on the icon
  3. Click OK.

As a result, the Developer tab will appear on the menu ribbon

"Stop"

Macro record in Excel

Now let's write a very simple macro that selects the cell and enters into it text, for example "Excel".

Here are the steps to record such a macro:

  1. Click the Developer tab.
  2. In the "Code" group, click the Macro Record button. The dialog box opens. (appeared instead of a button
  3. In the Macro Record dialog box, enter the name for your macro, for example, "input". There are several naming conditions that must be observed when appointing a macro. For example, you cannot use gaps between them. I usually prefer to keep macros names as one word, with different parts with the title first alphabet. You can also use an underscore to separate two words - for example, "enter_text". ) in the instrument group
  4. If you want, you can set the key combination. In this case, we will use the Ctrl + Shift + N label. Remember that the combination you specify will cancel any existing hotkeys in your book. For example, if you assigned a Ctrl + S combination, you will not be able to use it to save the working book (instead, every time you use it, it performs a macro). Application.screenUpdating = True.
  5. In the "Save to" field, make sure that the "This Book" option is selected. This step guarantees that the macro is part of the working book. He will be there when you save it and open it again, or even if you share the file with someone. Method 2: Creating a manually macro
  6. Enter the description if necessary. Usually I do not do this, but if you have many macros, it is better to specify that in the future do not forget what a macro does.
  7. Click "OK". As soon as you click OK, Excel will start recording your actions. You can see the "Stop Record" button on the Developer tab, which indicates that the macro is executed.
  8. Select the A2 cell.
  9. Enter the text "Excel" (or you can use your name).
  10. Press the ENTER key. You will fall on the A3 cell.
  11. Click the "Stop Record" button on the Developer tab. Experienced users Excel in some cases prefer to create macros from beginning to end completely manually. The action plan is as follows:

Congratulations! You just recorded your first macro in Excel. Although the macro does nothing useful, but it will help us understand how Macrorecorder in Excel works.

Now let's look at the code that recorded the macrorekoder. Follow these steps to open the code editor:

  1. Remove the text in the A2 cell. It is necessary to check whether the macro will insert text into the cell A2 or not.
  2. Choose any cell - except A2. It is necessary to check whether the macro chooses a cell A2 or not.
  3. Click the Developer tab.
  4. In the Code group, click Macros.
  5. In the Macro dialog box, click the "Entercting" macro.
  6. Click the "Run" button. In the tab

You will see that as soon as you click the "Run" button, the text "Excel" will be inserted into the A2 cell and the A3 cell is selected. This happens for milliseconds. But in fact, the macro has consistently executed recorded actions.

Note. You can also start the macro using the CTRL + SHIFT + N key combination (hold the Ctrl and Shift keys, and then press the N key). This is the same shortcut that we assigned a macro when writing.

What does the macro write?

We now turn to the editor of the code and see what happened.

Here are the steps to open the VB editor in Excel:

  1. Click the Developer tab.
  2. In the "Code" group, click the "Visual Basic" button.

You can also use the ALT + F11 key combination and go to the VBA code editor.

Consider the code editor itself. Next, briefly describe the editor interface.

Click on the icon

  • Menu panel : Contains commands that can be used while working with the editor VB.
  • Toolbar - Looks like a quick access panel in Excel. You can add additional tools to it that you often use.
  • Project Window (Project Explorer) - Here Excel lists all the books and all objects in each book. For example, if we have a book with 3 worksheets, it will appear in Project Explorer. There are several additional objects such as modules, user forms and classes modules.
  • Code window - Actually the VBA code itself is located in this window. For each object specified in the project conductor, there is a code window, such as working sheets, books, modules, etc. In this lesson, we will see that the recorded macro is located in the module code window.
  • Property window - You can see the properties of each object in this window. I often use this window to designate objects or change their properties.
  • Immediate Window. (Preview window) - At the initial stage, it will not be useful for you. It is useful when you want to test steps or during debugging. It is not displayed by default, and you can display it by clicking the "VIEW" tab and selecting the "Immediate Window" option.

When we recorded a multi-tech macro, the following things happened in the VB editor:

  • A new module has been added.
  • The macro was recorded with the name we indicated - "input"
  • The new procedure has been added to the code window.

Therefore, if you double-click the module (in our case, the module 1), the code window appears, as shown below.

"Visual Basic"

Here is the code that is recorded by a macrorekoder:

SUB input () 

'

'Shade Macro

' ' Range ("A2"). SELECT ActiveCell.formular1c1 = "Excel" Range ("A3"). SELECT End Sub. .

  • In VBA, any string that follows' (apostrophe) is not performed. This is a comment that is intended only for information purposes. If you delete the first five lines of this code, the macro will continue to work.
  • Now let's run on each line of the code and describe what and why.
  • Code begins S.

Sub.

Behind the name of the macro and empty round brackets. Sub - Reduction for the subroutine. Each subprogramme (also called the procedure) in VBA begins with

Sub.

And ends

End Sub.

Range ("A2"). SELECT - This string selects the A2 cell.

  1. ActiveCell.formular1c1 = "Excel" - This line enters the text "Excel" in the active cell. Since we chose the A2 cell as the first step, it becomes our active cell.
  2. Click the Developer tab.
  3. Range ("A3"). SELECT - Selecting a3 cell. This happens when we press the ENTER key after entering the text, the result of which is the selection of the A3 cell. (group of tools
  4. I hope that you have some basic understanding of how to record a macro in Excel. As a result, the familiar window will appear on the screen, which we considered above.
  5. Please note that the code recorded through the macrorecore is usually not an efficient and optimized code. Macrorecorder often adds additional unnecessary actions. But this does not mean that you do not need to use the macrorekoder. For those who only study VBA, the macrorecore can be excellent way to analyze and understand how everything works in VBA. A macro is created here and its code is written - completely manually.
  6. Absolute and relative macro recording
  7. Do you already know about absolute and relative links to Excel? If you use an absolute link to record a macro, the VBA code will always refer to the same cells that you used. For example, if you select the A2 cell and enter the "Excel" text, then every time - regardless of where you are on the sheet and no matter which cell is selected, your code will enter the text "Excel" in the A2 cell.
  8. Select the A2 cell.
  9. If you use a relative reference setting for macro recording, VBA will not be attached to a specific cell address. In this case, the program will "move" relative to the active cell. For example, suppose you have already chosen the A1 cell, and you start recording the macro in the relative link mode. Now you choose the A2 cell, enter the Excel text and press the ENTER key. Now, if you run this macro, it will not return to the A2 cell, instead it will move relative to the active cell. For example, if a B3 cell is selected, it will move to B4, the text "Excel" will record and then go to the K5 cell.
  10. Now let's write a macro in the relative link mode:
  11. Click the "Stop Record" button on the Developer tab.

Select the A1 cell.

In the "Code" group, click "Relent Links". It will be highlighted, indicating that it is included.

  1. Click the Macro Record button.
  2. Click the Developer tab.
  3. In the Code group, click Macros.
  4. In the Macro Record dialog box, enter a name for your macro. For example, the name "Relatives".
  5. Click the "Run" button.

In the "Save to" option, select "This Book".

Click "OK".

Conclusion

Enter the text "Excel" (or other as you like). 

Press the ENTER key. The cursor move to the A3 cell.

Macro in relative link mode will be saved.

Now do the following.

Select any cell (except A1).

  • In the "Macro" dialog box, click on the saved macro "Relatives".
  • As you notice, the macro recorded the text "Excel" is not in cells A2. It happened because you recorded a macro in the relative link mode. Thus, the cursor moves relative to the active cell. For example, if you do it when the B3 cell is selected, it will enter the Excel text - the B4 cell and ultimately select the B5 cell.
  • Here is the code that recorded Macrorecoder:
  • Sub relatives ()

'

'Relatives Macros

'

Thus, the use of macros in the Table Editor Excel allows you to increase efficiency and speed. You can create them as in automatic mode, simply writing the actions performed and writing the code from scratch. Also, if necessary, you can make changes to the previously created macro and customize the individual aspects of its work.

'

ActiveCell.offset (1, 0) .range ("A1"). SELECT

ActiveCell.formular1c1 = "Excel"

ActiveCell.offset (1, 0) .range ("A1"). SELECT

End Sub.

Please note that there are no references to cells B3 or B4. The macro uses ActiveCell to refer to the current cell and the offset relative to this cell.

Do not pay attention to the part of the Range code ("A1"). This is one of those cases where the macrorekoder adds an unnecessary code that has no goal and can be deleted. Without it, the code will work perfectly.

What can not be done with a macrorekoder?

The macro recorder is great for you in Excel and records your accurate steps, but maybe you do not come when you need to do something more. You cannot execute the code without selecting an object. For example, if you want the macro to move to the next work sheet and allocated all the filled cells in column A, without leaving the current working table, the macrorekoder will not be able to do this. In such cases, you need to manually edit the code.

You cannot create a user function using a macrorecard. Using VBA, you can create user functions that can be used on a working sheet as conventional functions.

  • You can not create cycles using a macrorecard. But you can write one action, and the cycle is manually in the code editor. You can not analyze the conditions: You can check the conditions in the code using the macro chorder. If you write the VBA code manually, you can use IF then ELSE statements to analyze the condition and launch of the code if True (or other code, if false). Excel file extension that contain macros
  • When you record a macro or manually record the VBA code in Excel, you need to save a file with a file enhancement file with support for macros (.xlsm).
  • Until Excel 2007, one file format was sufficient - .xls. But since 2007.xlsx was represented as a standard file extension. Files stored as .xlsx cannot contain a macro. Therefore, if you have a file with the .xlsx extension, and you write / write down the macro and save it, it will warn you about saving it in format with macros support and show you the following dialog box:

If you select "No", Excel Save the file in format with macros support. But if you click "Yes", Excel will automatically delete all the code from your book and save the file as a book in .xlsx format. Therefore, if your book has a macro, you need to save it in .xlsm format to save this macro.

  • Methods of recording macros in Excel Macro is written in two ways: automatically and manually. Using the first option, you simply write down certain actions in Microsoft Excel, which are currently available. Then you can play this entry. This method is very light and does not require knowledge of the code, but its use is quite limited. Manual recording, on the contrary, requires knowledge of programming, as the code is dialing manually from the keyboard. However, the code competently written in this way can significantly speed up the execution of processes. Creating macros In Excel, create macros can be manually or automatically. The last option involves the recording of the actions that we perform in the program for their further repeat. This is a fairly simple way, the user should not have some kind of coding skills, etc. However, in connection with this, it is not always possible to apply it. To create macros manually, you need to be able to program. But it is this method that is sometimes the only one or one of the few options to effectively solve the task.
  • Create a macro in Excel with a macrorecore To begin with, clarify what the macrorecorer represents and what is the macro. Macrorecorder - This is a small program in Excel, which interprets any user action in the VBA programming language codes and writes to the command program module that turned out in the process. That is, if we are included with the macrorekorder, we will create the daily report we need, then the macrorecore holds everything in their teams step by step and as the result will create a macro that will create a daily report automatically. This method is very useful to those who do not own skills and knowledge of work in the VBA language environment. But such ease of execution and record of Macros has its own mines, as well as the advantages: Write the macrorecorder can only what can feed, which means to record actions it can only when the buttons, icons, menu commands, and everything in this spirit, such options are used Sort by color .

It is unavailable for him; To create macros manually, you need to be able to program. But it is this method that is sometimes the only one or one of the few options to effectively solve the task. In the case when an error was allowed during the recording period, it will also be recorded. But you can select the last action button, erase the last command that you incorrectly recorded on VBA;

  • The record in the macrorecore is carried out only within the borders of the MS Excel window and in the case when you close the program or turn on the other, the recording will be stopped and stopped performed. To turn on the macrorecordera, it is necessary to make the following actions:
  • In Excel version of 2007 and more new You need to tab "Developer" Press the button "Macro record" in Excel versions from 2003 and older Creating macros (they are still very often used) you need in the menu "Service"
  • Select item "Macro"
    • And click the button "Start recording"
    • The next step in working with the macrorekorder will be setting up its parameters for further macro recording, it can be made in the window Where:
    • The "Macro name" field - You can register your understandable name in any language, but should begin with the letter and do not contain a sign of punctuation and spaces; The "key combination" field - You will be used, in the future, for the rapid start of your macro. In the case when you need to register a new
  • Combination of hot keys then this feature will be available in the menu

"Service" - "Macro" - "Macros" - "Perform" or on the tab Pressing the button

"Macros"

Field "Save in ..."

- You can ask the place where the text of the macro will be saved (but not sent), and this is 3 options:

"This book"

  1. - The macro will be recorded in the current book module and can only be performed in the case when this book Excel is open;
  2. "A new book"
  3. - The macro will be saved in that template, on the basis of which an empty new book is created in Excel, which means that the macro will be available in all books that will be created on this computer from now on;
  4. ' "Personal book of macros" - is a special book of Excel macros, which is called "Personal.xls" And used as a special storage library of macros. When starting macros from the book "Personal.xls" are loaded into memory and can be launched in any book at any time. - is a special book of Excel macros, which is called Field "Description" - Here you can describe what the macro should make, for which it is created and what functions is carrying, it is a purely informative field, which is called memory. After you started and recorded your macro by performing all the necessary actions, the recording can be terminated by the command "Stop recording" And your macro with a macrorecore will be created. End Sub.

  5. Writing macros to Excel

The Excel macro code is written in Visual Basic for Application (VBA), and it performs the application tool to which it is attached. Most of these tools are not available at the Excel window level. How to write a macro.

Now we will demonstrate on the example information on how to write, edit and execute the macro code.

To write a macro:

Open the Excel workingbook in which you want to use the macro: "Developer" - "code" - "Visual Basic". Or press the Alt + F11 hot key combination.

Before starting work in the editor, make a simple setting. Select a tool in the Visual Basic Editor: "Tools" - "Options". And on the "Editor" tab, activate the "Require Variable Declaration" option. This will allow you to automatically fill out the Options Explicit instructions at the beginning of each new code created. And in the input field "TAB WIDTH:" Specify the value 2 instead of 4. This will reduce the width of the code. This editor setting applies to all sheets, but within the borders of one working book.

Select the Tool: "Insert" - "Module" to create a new standard module for macros. In the module window that appears under the text Explicit text, enter the following macro code: MyMakros ()

Dim

  1. Polzovatel Creating macros AS String To create macros manually, you need to be able to program. But it is this method that is sometimes the only one or one of the few options to effectively solve the task. Data_Segodnya. AS DATE. .
  2. polzovatel = application.usernamedata_segodnya = nowmsgbox "Macro launched User:" .
  3. & Polzovatel & Vbnewline & Data_Segodnya Ctrl Click on the button in the "Run Macro" editor or the F5 key on the keyboard. In the "Macros" window that appears, click on the "RUN" button to view the result of the macro. М.
  4. Note. If the "Developer" tab is missing in the main menu, then it must be activated in the settings: "File" - "Parameters" - "Set up the tape". In the right list, "Basic tabs:" Activate the "Developer" option and click OK.
  5. Setting up permissions for macros to Excel Excel provides built-in virus protection, which can penetrate into a computer through macros. If you want to start an Excel macro in the book, make sure that the security settings are set correctly. .
  6. Option 1: Automatic recording of macros
  7. Before starting the automatic recording of macros, you need to enable them in the Microsoft Excel program. To do this, use our separate material.
  8. Read more: or on the tab Enabling and disable macros in Microsoft Excel To create macros manually, you need to be able to program. But it is this method that is sometimes the only one or one of the few options to effectively solve the task. When everything is ready, proceed to the record.

Click the tab

. Click on the button

  1. which is located on the ribbon in the tool block AS DATE. "The code" "Service" A macro recording setup window opens. Here you can specify any name for it if the default is not satisfied with you. The main thing is that the name it starts with the letter, and not with the numbers, as well as in the title there should be no gaps. We left the default name - "Macro1" .
  2. Immediately, if you wish, you can set the key combination, when you click on which the macro will start. The first key must be And the second user installs independently. We installed a key as an example. .
  3. Next, you should determine where the macro will be stored. By default, it is located in the same book (file), but if you wish, you can set storage in a new book or in a separate book of macros. We will leave the default value. In the lowest field, you can leave any suitable macro description, but it is not necessary to do this. When all settings are made, click on the button Ok
  4. After that, all your actions in this book (file) Excel will be recorded in the macro until you yourself stop recording.

For example, we write the simplest arithmetic effect: the addition of the contents of the three cells (= C4 + C5 + C6).

When the algorithm was executed, click on the button

  1. . This button transformed from the button. "Service" After turning on the record. Launch macro .
  2. To verify how the recorded macro works, perform a few simple actions. Click in the same tool block by button
  3. or click the keyboard key ' Alt + F8. End Sub. After that, a window opens with a list of recorded macros. We are looking for a macro that we recorded, allocate it and click on the button ' "Run" You can do it even easier and not to call the macros selection window, since at the first stage we set the key combination to quickly call the macro. In our case, it Ctrl + M. . Click this combination on the keyboard, after which it starts. As you can see, he fulfilled all the actions that were recorded earlier. Editing macro Naturally, if you wish, you can adjust the created macro to always maintain it up-to-date and correct some inaccuracies made during the recording process. Click on the button again . In the window that opens, select the desired and click on the button
  4. "Change"

    Opens

  5. "Microsoft Visual Basic" (VBE) - Wednesday where their editing occurs. Recording each macro starts with the team , and ends with a team .
  6. . Right after
  7. Macro is indicated. Operator Range ("..."). SELECT Specifies the selection of the cell. For example, when team "Range (" C4 "). SELECT" .
  8. Selected cell "C4" . Operator ActiveCell.formular1c1. Used to record action in formulas and other calculations.

Let's try to change a little macro by adding the expression:

Range ("C3"). SelectActiveCell.formular1c1 = "11"

  1. Expression ActiveCell.formular1c1 = "= R [-3] C + R [-2] C + R [-1] C" Macrorecorder Replace on ActiveCell.formular1c1 = "= R [-4] C + R [-3] C + R [-2] C + R [-1] C" Close the editor and launch the macro. As you can see, as a result of our changes, an additional cell has been added to the data. It was also included in the calculation of the total amount. If the macro is too big, its execution may take a considerable time, but by making a manual change to the code we can speed up the process. Add command Application.screenupdating = false . It will save computing power, and therefore speed up the work. This is achieved by refusing to update the screen during computational actions. To resume update after macro execution, we write a team at its end Application.screenUpdating = True. Add also a command Application.calculation = XLCalculationManual
  2. to the top of the code, and in its end add Application.calculation = XLCalculationAutomatic. . By this, we first turn off the automatic recalculation of the result after each change of cells, and at the end of the macro - turn on. Thus, Excel calculates the result only once, and it will not constantly recount it than the time saves. Creating a button to start macros in the toolbar As I said earlier, you can call the macro procedure with a hot key combination, but it is very tedious to remember which combination to someone is assigned, so it will be best to create a button to start macro. Buttons create, perhaps, several types, namely: Button in the toolbar in MS Excel 2003 and older "Service" . You need in the menu in point "Settings"

Go to the available tab

"Teams"

  • And in the window "Categories" Select a command "Customizable button" marked with a yellow bolk or emoticon, who is as clearer or more convenient. Pull out this button to your taskbar and by pressing the right mouse button on the button, call its context menu in which you can edit the button to your tasks by specifying a new icon, name and assigning the desired macro to it. Button in your quick access panel in MS Excel 2007 and more newer . You need to put the right mouse button on .
  • Fast access panels Macro is written in two ways: automatically and manually. Using the first option, you simply write down certain actions in Microsoft Excel, which are currently available. Then you can play this entry. This method is very light and does not require knowledge of the code, but its use is quite limited. Manual recording, on the contrary, requires knowledge of programming, as the code is dialing manually from the keyboard. However, the code competently written in this way can significantly speed up the execution of processes. Creating macros which is located in the upper left corner of the MS Excel window and select the item in the opened context menu "Setting the Quick Access Panel" . In the Settings dialog box, you choose a category and using the button .

"Add"

You transfer the macro selected from the list to another half the window to further assign this command on your quick access panel.

  1. Creating a graphic button on the Excel sheet
  2. This method is available for any of the versions of MS Excel and is that we will remove the button directly to our work sheet as a graphic object. For this you need:
  3. In MS Excel 2003 and older
  4. Go to the menu
  5. "View"

, choose

"Toolbar"

  1. And press the button
  2. "Forms"
  3. In MS Excel 2007 and more
  4. Open drop-down menu
  5. "Paste"

and choose the object

"Button"

After all this, you must draw a button on your sheet with the left mouse button. After the process is completed, the window will turn on automatically where you need to choose that macro that is required, performed when you click on your button.

To record a macro, follows:

Log in to the "Developer" tab.

  1. Select a macro record.
  2. Select the name of the macro (in the name you can not use spaces and hyphens);
  3. Click OK.

You can select a key combination, when you click on which the macro will start;

Sub.

Select location:

- when saving in this book, the macro will work only in the current document;

Range ("A2"). SELECT - This string selects the A2 cell.

  1. ActiveCell.formular1c1 = "Excel" - This line enters the text "Excel" in the active cell. Since we chose the A2 cell as the first step, it becomes our active cell.
  2. - When saving to "Personal Book", the macro will work in all documents on your computer.
  3. You can add a description of the macro, it will help you remember what actions make a macro.
  4. Press "OK".
  5. If you do not specify a key combination, the record will start immediately after pressing the "OK" button.
  6. When you record, you must make the required sequence of actions.
  7. When you finish, press the Stop Record button.
  8. Select the A2 cell.
  9. Recorded macros are displayed in the macros.
  10. To see them, click the Macros button. A list of macros appears in the window that appears. Select the desired macro and click "Run".
  11. Macros in the book can be edited. To do this, select the macro and click the "Edit" button. When you click on the "Change" button, the macro editor with a script written in the VBA language opens.

Display the Developer tab in the menu ribbon

In the "Code" group, click "Relent Links". It will be highlighted, indicating that it is included.

  1. Click the Macro Record button.
  2. - When saving to "Personal Book", the macro will work in all documents on your computer.
  3. Before recording the macro, add the "Developer" tab to the tape. To do this, follow these steps:
  4. Right-click on any of the existing tabs on the ribbon and click "Configure the Tape". It will open the Excel Settings dialog box.
  5. In the Excel Settings dialog box, you will have the "Configure Tape" options. On the right in the "Main tabs" panel, select the Developer check box.

As a result, the Developer tab will appear on the menu ribbon

Click "OK".

Do you already know about absolute and relative links to Excel? If you use an absolute link to record a macro, the VBA code will always refer to the same cells that you used. For example, if you select the A2 cell and enter the "Excel" text, then every time - regardless of where you are on the sheet and no matter which cell is selected, your code will enter the text "Excel" in the A2 cell. 

If you use a relative reference setting for macro recording, VBA will not be attached to a specific cell address. In this case, the program will "move" relative to the active cell. For example, suppose you have already chosen the A1 cell, and you start recording the macro in the relative link mode. Now you choose the A2 cell, enter the Excel text and press the ENTER key. Now, if you run this macro, it will not return to the A2 cell, instead it will move relative to the active cell. For example, if a B3 cell is selected, it will move to B4, the text "Excel" will record and then go to the K5 cell.

Click the Developer tab.

In the "Code" group, click "Relent Links". It will be highlighted, indicating that it is included.

'Relatives Macros

Click the Macro Record button.

In the Macro Record dialog box, enter a name for your macro. For example, the name "Relatives".

Now do the following.

In the "Save to" option, select "This Book".

  • Click "OK".
  • Enter the text "Excel" (or other as you like).
  • Press the ENTER key. The cursor move to the A3 cell.
  • Click the "Stop Record" button on the Developer tab.

Macro in relative link mode will be saved.

In the Code group, click Macros. In the "Macro" dialog box, click on the saved macro "Relatives". Click the "Run" button.

As you notice, the macro recorded the text "Excel" is not in cells A2. It happened because you recorded a macro in the relative link mode. Thus, the cursor moves relative to the active cell. For example, if you do it when the B3 cell is selected, it will enter the Excel text - the B4 cell and ultimately select the B5 cell.

Sub Relatives () '' Relative Macro Macro '' ActiveCell.offset (1, 0) .range ("A1"). Select ActiveCell.formular1c1 = "Excel" ActiveCell.offset (1, 0) .range ("A1"). SELECT END Sub.

  1. Please note that there are no references to cells B3 or B4. The macro uses ActiveCell to refer to the current cell and the offset relative to this cell. Do not pay attention to the part of the Range code ("A1"). This is one of those cases where the macrorekoder adds an unnecessary code that has no goal and can be deleted. Without it, the code will work perfectly. Excel file extension that contain macros Until Excel 2007, one file format was sufficient - .xls. But since 2007.xlsx was represented as a standard file extension. Files stored as .xlsx cannot contain a macro. Therefore, if you have a file with the .xlsx extension, and you write / write down the macro and save it, it will warn you about saving it in format with macros support and show you the following dialog box: If you select "No", Excel Save the file in format with macros support. But if you click "Yes", Excel will automatically delete all the code from your book and save the file as a book in .xlsx format. Therefore, if your book has a macro, you need to save it in .xlsm format to save this macro. The macro recorder is great for you in Excel and records your accurate steps, but maybe you do not come when you need to do something more. You cannot execute the code without selecting an object. For example, if you want the macro to move to the next work sheet and allocated all the filled cells in column A, without leaving the current working table, the macrorekoder will not be able to do this. In such cases, you need to manually edit the code. You cannot create a user function using a macrorecard. Using VBA, you can create user functions that can be used on a working sheet as conventional functions. ).
  2. You can not create cycles using a macrorecard. But you can write one action, and the cycle is manually in the code editor. You can not analyze the conditions: You can check the conditions in the code using the macro chorder. If you write the VBA code manually, you can use IF then ELSE statements to analyze the condition and launch of the code if True (or other code, if FALSE). .Note: Editor Visual Basic
  3. Excel has a built-in

Editor Visual Basic

which stores the macro code and interacts with the Excel book. Visual Basic Editor allocates errors in the programming language syntax and provides debugging tools to track operation and detect errors in the code, helping the developer in writing code.

  1. Launch the execution of the macro The macro recorder is great for you in Excel and records your accurate steps, but maybe you do not come when you need to do something more. To check the work of the recorded macro, you need to do the following: In the same tab ( ).
  2. "Developer" ) and group .
  3. "The code" Press the button "Macros"
    • (You can also use hot keys Alt + F8. In the displayed window, choose our macro and click on the team "Run"
    • There is a simpler option to start macro execution - use the key combination that we set when creating a macro. Alt + F8. The result of the verification will be the repetition of previously executed (recorded) actions.
    • Correct macro
    • Created macro can be changed. The most common cause that leads to such a need - made when writing an error. Here is how you can edit the macro: Press the button (or combination Ctrl + F8. In the window that appears, we choose our macro and click "Change" .
    • The Editor window will appear on the screen. "Microsoft Visual Basic" in which we can make edits. The structure of each macro is as follows:
  4. Opens the team "SUB" , closes - 3"END SUB"; after
  5. Displays the name of the macro; The following describes the description (if there is) and the designated key combination; command "Range (" ... "). SELECT .Note: "Returns the cell number. For instance, Click on the button again "Range (" B2 "). SELECT" Selects cell .
  6. B2.
  7. In line "SUB" , closes - "ActiveCell.formular1c1" The value of the cell or action in the formula is indicated.
  8. Let's try to adjust the macro, namely, add a cell to it
    • B4. Range ("..."). SELECT with meaning
    • . To the macro code you need to add the following lines: "Range (" C4 "). SELECT" .
  9. Range ("B4"). SelectActiveCell.formular1c1 = "3" "C4" For a resulting cell ActiveCell.formular1c1. D2.

Accordingly, you also need to change the initial expression on the following:

  • ActiveCell.formular1c1 = "= Rc [-2] * R [1] C [-2] * R [2] C [-2]"
  • Please note that the cell addresses in this line (
  • ) are written in style
  • R1C1
  • When everything is ready, the editor can be closed (just click on the cross in the upper right corner of the window).
  • We launch the execution of the changed macro, after which we can notice that a new filled cell appeared in the table (
  • "3"

), as well as, refined the result, taking into account the modified formula.

If we are dealing with a large macro, on the execution of which it may be necessary to take a lot of time, manual editing changes will help to cope with the task faster.

Adding a command at the end Visual Basic for Application or abbreviated VBA. We can accelerate the work, since during the execution of the macro, the changes on the screen will not be displayed.

VBA. If you need to return the display on the screen again, write the command: VBA alone To help the book. And now we will understand in detail what kind of "macros", how to include them.

What does macros mean and how to work with them?

In order not to load the program recalculation after each change made, at the very beginning we write the team

First you need to configure Excel to work with macros. It's simple. On the example of Office 2007 I will show you how. In later editors, everything is done in almost similar. First go to "Parameters":

, and at the end -

. Now the calculation will be performed only once.

You need to disable all restrictions on the launch of macros. This will allow you not only to work with your macros, but also to run already ready for someone created.

You also need to trust access to the object model of VBA projects. After all, on the Internet or at colleagues, you can find a lot of ready-made and interesting projects. VBA. written for Excel, Word and use.

What is the macro in Excel 2007?

We still learn to write down simple macros. Make it very simple. In "Ribe", we now have the corresponding icons "Developer".

Sources

https://lumpics.ru/how-to-create-a-macro-in-excel/

https://microexcel.ru/sozdanie-makrosov/

http://topexcel.ru/kak-sozdat-makros-v-excel/

https://exceltable.com/vba-macros/kak-napisat-makros.

Our macro is now available on the Macros button, you can run it from there.

https://office-guru.ru/excel/samouchitel-po-rabote-s-makrosami-v-excel-449.html

http://ruexcel.ru/simple-macros/

Imagine that we have a rather cumbersome table, which each time it has to be cleaned of the data of the previous report. Task: Make a button in the document, when you click on which the desired cells are automatically cleaned. Let's start.

https://micro-solution.ru/excel/vba/first-macros.

Good time, friends. In today's release, I will talk about the most interesting function in the most chassis office package of Microsoft Office programs. Those who constantly work with Excel tables know how many routine operations have to sometimes do with content. If there are many documents - you have to repeat the same actions repeated. From the allocation of mouse and copy to more complex - such as calculations, filling, cleaning individual graph, creating tables, layouts, and so on.

In order not to repeat the same actions every time you can automate the process and save time. You record your actions (allocating document cells, copying, formatting, formulas) in a macro. This command is placed in the form of a button on the toolbar. When you press the button, all your previously recorded actions are performed automatically. This is the "macro".

How to enable macros in Excel 2016 video

For me personally, acquaintance with macros in one time has become a study of programming. The fact is that Microsoft Office is also a light and understandable environment for this. It has its own built-in language -

. And when you write down macros, you like becoming a programmer. At the same time, it is not necessary to know the language in the first stages. The program code can be seen already later, in the editor and study. How to look - about it just tell.

Our button will automatically assign a macro. The name of the macro, the key combination, put free, we save in the "book". This means that the button will only work in this document.

Next, the program will offer a method for creating a macro. If you are already an advanced VBA programist, you can write the code with your hands by clicking "Create". But today we will use the already familiar method - the recording of our actions:

Allows you to create powerful tools when working with tabular documents for solving many tasks in the form of individual windows, buttons, lists, flags - all visually. And experienced programmers know that macros can be used to not dial commands with their hands, but to get ready-made code from just recorded macro. But first things first. Those who, after reading the article, will be interested in and wants to study the language

So, the macro is a kind of program code that describes the sequence of your actions in the document. Even the movement of your cell cursors is recorded, pressing the keys. First you write down the macro. When you need, you in Excel launch this code for execution and all recorded steps are performed automatically. For example, you have the same table, the layout of which you create a new week, change the design, or clean the graphs. You press the button, and now what you spent 5-10 minutes have taken just one second.

Now that the desired range of cells is selected, pressed the DEL key on the keyboard and delete the contents of the cells. Stop recording Macro:

Like any object with the button, you can make various actions - copy, delete, format, assign macros to them. We have yet change the name. To get to the properties of the button, you need to right-click it:

First we will configure the "tape"; You need to enable the "Developer Mode":

If you are going to work seriously with macros - you need to turn on another setting. Here we go to the Security Management Center and configure the "Security Management Center" parameters:

In this mode, the other functions will be available to us - the "constructor mode". You can independently create windows, buttons. And in the Visual Basic editor, you can edit the created macros. So, I will show how it all works. When you click the "Macro Record" button, you will first need to fill it with its name (without spaces), hot key keys (optional) and place where it will be saved:

Depending on the task, you can save macros in a separate document or in the "Personal Book". In the first case, it is recorded within the document and will only be executed in the document. In the second case, it can be called before creating a document. It is better to demonstrate clearly for what makes macros. Task: I need to create: a document Excel, in the document Create a table of a specific format and computing inside. Watch the video what happened:

You yourself saw that the process of creating one simple demo - table occupies from five or more minutes. And we created a table with a macros in 2 seconds. And if we are talking about complex projects? The answer is obvious by creating a procedure once, you will save time and increase the performance of your work.

After the post is completed when the Excel is closed, the program will definitely ask to keep changes in the "personal book" of macros. Do not forget to agree; Otherwise, the recorded macro will disappear and everyone will have to do again.

0 Important! The "Personal Book" of macros is stored in the user folder on the path C: \ users \% computer user% \ APPDATA \ Roaming \ Microsoft \ Excel \ XLStart. This book can be divided with others or delete if something failed. What does the macro mean in Excel 2016 and why is it designed? The process of recording macros does not differ from early versions; In another simple example, I will tell you about another way to call Macrocomand. The creation of macros will allow you to get acquainted with programming skills and who knows, maybe this is an interesting thing in hobbies.

First, do not forget to make MSOFFICE 2016 settings. We also go to "Parameters", but then go to "Set up a tape"

ActiveCell.offset (1, 0) .range ("A1"). SELECT

End Sub.

Please note that there are no references to cells B3 or B4. The macro uses ActiveCell to refer to the current cell and the offset relative to this cell.

After the "developer" will be visible on the "tape". Go there. Our personal book of macros is still on the spot, open it:

Let's start our previous macro, we admire how the program itself creates and fills the table. In our example, there are two columns "Number" and "price", the contents of which have to be cleaned. We automate the process.

You can call macros for execution, as we have already done in the last example. This is not clear and sometimes not very convenient. Therefore, we will do it anyhow - create a button in the document, with which we will clean the contents.

Programming begins 😛, from the Developer menu, insert the button to any place of our document. The cursor change to the crossroads, then draw the button in the right place:

  1. Recording started! We are going to clear the selected graph graphs, so simultaneously select the desired ranges in the "Quantity" and "price" graphs. To do this, first select the Count "Quantity", then clamp the Ctrl key and do not release it allocate the necessary cells in the price column, after letting go. Do not pay attention to the part of the Range code ("A1"). This is one of those cases where the macrorekoder adds an unnecessary code that has no goal and can be deleted. Without it, the code will work perfectly. Using the CTRL key has been selected, it helps to select columns and areas of different cell ranges simultaneously. Do not neglect this in the case of large tables. Until Excel 2007, one file format was sufficient - .xls. But since 2007.xlsx was represented as a standard file extension. Files stored as .xlsx cannot contain a macro. Therefore, if you have a file with the .xlsx extension, and you write / write down the macro and save it, it will warn you about saving it in format with macros support and show you the following dialog box: I changed the text to "Clear". Color, text size The font in the buttons can also be changed to taste. Close to this in the "object format": Everything is about the same way as when the cell formatting. And change the size of the button itself and its location can be stretched by the edges or dragging it: .Configure tape in Excel
  2. Well, lastly let's look into the VBA editor and see what our macro program code written it looks like. To do this, we go to our "macros", choose the desired and click "Log in" or "Change":
    • Another way - you can right-click on the button and select "Assign Macro"; The result will be similar. An VBA editor opened in which you can do a lot of interesting and useful. We are still looking at that we have programmed there: Simple and understandable sample code; After comments (highlighted green), the code "Select the range of cells is first one, then another, then delete the contents." You can close the editor as the usual window by clicking on the cross. I hope that such macros - you figured out. At the same time they learned how to become a programmer. Good luck in mastering the program!
    • Publication author Comments: 62. Ctrl Publications: 389. Registration: 04-09-2015 With the help of macros, you can significantly increase the efficiency of processing and working with information in Excel due to the fact that the set of identical actions that we have to repeat, you can decorate in the form of a special code that will take this care for yourself. In this article, we will look at how you can create a new macro, as well as how to change the existing one.
    • Method 1: write a macro in automatic mode To be able to record macros automatically, first you need to activate them in the Excel parameters.
    • You can find out how it can be done, you can in our article - "how to enable and disable macros in Excel".
    • After macros are included, you can go to our main task. Switch to the tab .Write a macro to Excel - Enable the Developer tab
  3. . In the instrument group
  4. Click on the icon The following describes the description (if there is) and the designated key combination; "Write a macro" "Change" и A window will appear on the screen in which we can perform recording settings. It is indicated here: Macro name - Any, but should start with the letter (not from the figure), gaps are not allowed. .Developer Tab in Ribbon
  5. Key combination which will be used to start macro. Mandatory is And we can assign the second in a special field. Suppose, let it be the "R" key. If you need to use a capital letter, you need to hold the key Shift. which will be added to the key combination. Everything is about the same way as when the cell formatting. And change the size of the button itself and its location can be stretched by the edges or dragging it: Macro storage location Until Excel 2007, one file format was sufficient - .xls. But since 2007.xlsx was represented as a standard file extension. Files stored as .xlsx cannot contain a macro. Therefore, if you have a file with the .xlsx extension, and you write / write down the macro and save it, it will warn you about saving it in format with macros support and show you the following dialog box: .Macro Record button on the Developer's tab in the ribbon

As you notice, the macro recorded the text "Excel" is not in cells A2. It happened because you recorded a macro in the relative link mode. Thus, the cursor moves relative to the active cell. For example, if you do it when the B3 cell is selected, it will enter the Excel text - the B4 cell and ultimately select the B5 cell.

Sub Relatives () '' Relative Macro Macro '' ActiveCell.offset (1, 0) .range ("A1"). Select ActiveCell.formular1c1 = "Excel" ActiveCell.offset (1, 0) .range ("A1"). SELECT END Sub.

  1. Please note that there are no references to cells B3 or B4. The macro uses ActiveCell to refer to the current cell and the offset relative to this cell. Do not pay attention to the part of the Range code ("A1"). This is one of those cases where the macrorekoder adds an unnecessary code that has no goal and can be deleted. Without it, the code will work perfectly. Excel file extension that contain macros Until Excel 2007, one file format was sufficient - .xls. But since 2007.xlsx was represented as a standard file extension. Files stored as .xlsx cannot contain a macro. Therefore, if you have a file with the .xlsx extension, and you write / write down the macro and save it, it will warn you about saving it in format with macros support and show you the following dialog box: If you select "No", Excel Save the file in format with macros support. But if you click "Yes", Excel will automatically delete all the code from your book and save the file as a book in .xlsx format. Therefore, if your book has a macro, you need to save it in .xlsm format to save this macro. The macro recorder is great for you in Excel and records your accurate steps, but maybe you do not come when you need to do something more. You cannot execute the code without selecting an object. For example, if you want the macro to move to the next work sheet and allocated all the filled cells in column A, without leaving the current working table, the macrorekoder will not be able to do this. In such cases, you need to manually edit the code. You cannot create a user function using a macrorecard. Using VBA, you can create user functions that can be used on a working sheet as conventional functions. ). Macro name in dialog box
  2. You can not create cycles using a macrorecard. But you can write one action, and the cycle is manually in the code editor. You can not analyze the conditions: You can check the conditions in the code using the macro chorder. If you write the VBA code manually, you can use IF then ELSE statements to analyze the condition and launch of the code if True (or other code, if FALSE). .Macro recording in Excel - Purpose of the shortcut in the dialog boxNote: Editor Visual Basic
  3. Excel has a built-in Write a macro in Excel - save the macro in this book

Editor Visual Basic

which stores the macro code and interacts with the Excel book. Visual Basic Editor allocates errors in the programming language syntax and provides debugging tools to track operation and detect errors in the code, helping the developer in writing code.

  1. Launch the execution of the macro The macro recorder is great for you in Excel and records your accurate steps, but maybe you do not come when you need to do something more. To check the work of the recorded macro, you need to do the following: In the same tab ( ).
  2. "Developer" ) and group .Write a macro in Excel - Stop recording
  3. "The code" Press the button "Macros"
    • (You can also use hot keys Alt + F8. In the displayed window, choose our macro and click on the team "Run"
    • There is a simpler option to start macro execution - use the key combination that we set when creating a macro. Alt + F8. The result of the verification will be the repetition of previously executed (recorded) actions.
    • Correct macro
    • Created macro can be changed. The most common cause that leads to such a need - made when writing an error. Here is how you can edit the macro: Press the button (or combination Ctrl + F8. In the window that appears, we choose our macro and click "Change" .
    • The Editor window will appear on the screen. "Microsoft Visual Basic" in which we can make edits. The structure of each macro is as follows: Write a macro to Excel
  4. Opens the team "SUB" , closes - 3"END SUB"; after Code Editor Window
  5. Displays the name of the macro; The following describes the description (if there is) and the designated key combination; command "Range (" ... "). SELECT .Macro code in the moduleNote: "Returns the cell number. For instance, Click on the button again "Range (" B2 "). SELECT" Selects cell .
  6. B2.
  7. In line "SUB" , closes - "ActiveCell.formular1c1" The value of the cell or action in the formula is indicated. Write down the macro in Excel - using "relative links"
  8. Let's try to adjust the macro, namely, add a cell to it
    • B4. Range ("..."). SELECT with meaning Click the Macro Record button on the Developer tab.
    • . To the macro code you need to add the following lines: . Standard - it will be the current book, but if required, you can choose a new book or personal book of macros. In our case, we leave the standard option. .
  9. Range ("B4"). SelectActiveCell.formular1c1 = "3" "C4" For a resulting cell ActiveCell.formular1c1. D2. Write a macro in Excel - macro name

If you wish, you can add a description to the macro.

By readiness for clicking

  1. OK. Do not pay attention to the part of the Range code ("A1"). This is one of those cases where the macrorekoder adds an unnecessary code that has no goal and can be deleted. Without it, the code will work perfectly. Now all the actions that we will perform in the book will be recorded in the created macro. And so until we stop the record. Let's say let's let's in the cell Perform multiplication of two cells ( Until Excel 2007, one file format was sufficient - .xls. But since 2007.xlsx was represented as a standard file extension. Files stored as .xlsx cannot contain a macro. Therefore, if you have a file with the .xlsx extension, and you write / write down the macro and save it, it will warn you about saving it in format with macros support and show you the following dialog box: ). VBA macro code for relative reference
  2. B3. Save a file with macros support
  3. ):

= B2 * B3

After the expression is ready, press the key

Добавить комментарий