• VBA Excel: sample programs. Macros in Excel. Creating macros in Microsoft Excel

    Excel tables are one of the most popular ways to store and organize information. If you introduce macros into Excel, the program's capabilities can be almost doubled. For example, macros help automate routine processes and speed up monotonous work. They are often used by specialists who translate reports from 1C into Excel. With the help of embedded code, they can easily be reduced to a single standard template.

    What are Excel macros?

    Macros are used not only in tables, but in everything Microsoft Office. They are code created using Visual Basic for Applications. If you are partially familiar with web programming, then you can draw an analogy with Javascript. Excel macros do roughly the same thing that Javascript does with HTML data.

    A small part of what a macro can do in office applications:

    Align the document according to style and format it;
    . carry out operations with numerical and text data;
    . seek help external files(text, databases and others);
    . create new documents from scratch;
    . combine several actions and perform them in automatic mode.

    To work with the table, you can create it yourself or download a ready-made version from the Internet. Macros should only be downloaded from trusted sites, otherwise you can introduce a virus into Excel.

    How can macros in Excel be dangerous?

    Damage files on your PC;
    . steal confidential data.

    The virus is introduced during the operation of one of the office applications- Word or Excel. After you stop working, they will start autonomous operation virus and infecting the entire system.

    Another way a malicious macro works is as an intermediary to introduce threats into the system. In this case, it is a kind of gateway for the introduction and generation of Trojan software. It will no longer be controlled by a modified macro, but by the operating system, and therefore the threat becomes larger. It is not always possible to cure such viruses; sometimes you have to completely change the Windows OS or hard drive PC (depending on the tasks that the hacker set when writing the code).

    You can choose any method of protection against such macros. The most popular option is to increase the security level of Excel, but in this case the program may no longer support your copyright, useful macros. You should not trust files from unknown sources, as they may become infected. Even a macro received from your colleague’s PC can be dangerous. If a friend gave you a file to work with that supports the use of macros, then before allowing the system to use it, clarify how he received this macro.

    The best option is self-study the art of creating macros for your needs. In this case, you can fully vouch for their quality and safety.

    How to Record Macros in Excel

    The easiest way to create a macro is to program directly in Excel. To do this, you will need a table with the entered data and a little knowledge to enable recording of such code in the program.

    Creation begins with the View menu. In it you need to select the “Macros” position and the “Record Macro” command. Next, you need to specify a name without spaces, for example, “Format” (if the code will be responsible for changing the format of text or cells).

    After which all your actions will be automatically recorded and converted into macro code. A button with a small square (“Stop”) will appear at the bottom of the document, by clicking on which you can stop recording. This way you can automatically record macros in Excel, examples of which you can analyze further in the learning process.

    How to enable macros in Excel

    In order to enable a recorded macro in other documents, you must save it after clearing the table. The document format must be set as XLTM (it is the one that supports macros).

    When you later access your document to enable macros in Excel, you must first enable them in the pop-up top line. Then you need to import the data that will be edited by the macro from a regular CSV table. Once the import is complete, you need to go back to the “View” menu, select “Macros” there and find the name of the code you made in the list. Finally, click the “Run” button. After which you will see how the program, according to the algorithm you previously specified, performs calculations and formats the document.

    How can I see the macro code?

    All macros created in Excel are written automatically, without human intervention. The program composes itself program code, which can be opened from the table by calling using the menu.

    To do this, you need to run the commands sequentially:

    Expand the “View” tab;
    . open "Macros";
    . select the name of the required macro;
    . Call up a window with its code using the “Change” button.

    In the new dialog box you can edit and change the macro. You will see there the code that the program recorded automatically, and you can manually add any action. After saving the information, the macro will work differently when importing new data.

    How to start a macro by clicking on a cell

    Let's say you want a macro to run when cell A1 is selected. To select in Excel cell running the macro, you need to add the program code to the first module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then: Call module1
    End Sub

    The best way to learn macros is in Excel tutorial. There's more than one similar example, and for those who are just getting acquainted with all the possibilities of the table, this best way learn to fully utilize its functionality.

    Where can you learn to work with macros?

    There are several ways to learn how to create more complex macros. They must be distinguished by logic and a clearly defined sequence of actions. Such macros can only be created by writing VBA code manually. To create complex macros, you will need to spend some time learning the intricacies of the Visual Basic programming language.

    Nowadays, to learn all the possibilities of Excel, many special courses have been created that last for several weeks, but to attend them you will need to go to a training center or attend webinars (which is not always convenient due to the time difference). But a more effective and simpler way to learn how to create macros in Excel is a tutorial with examples. You can study it at any time convenient for you, and step-by-step textbooks will help you develop different options: from the most simple codes to the most complex. Microsoft Excel is a universal spreadsheet editor that can be used to solve many complex problems.

    Examples

    There are codes that allow you to automate various tasks. So, as an example, let's look at the row insertion macro. It looks like this:

    Sub Macro1()

    Good afternoon

    I want to devote this article to such a huge section of MS Excel as macros, or rather, let’s start from the beginning and look at how to create a macro in Excel, what it is for and how to use it in your work.

    As you know from own experience, when working with a macro there is a lot of “routine”, that is, the same operations and actions are performed that are necessary to obtain the result, this can be filling out the same type of tables or forms, data processing, similar weekly, monthly reports, and etc. But using macros will allow you to perform these actions automatically, using the capabilities of Excel to the fullest, throwing these routine and monotonous operations onto the powerful shoulders of Excel. Also, the reason for using macros may be adding the necessary opportunities, which are not yet implemented in standard Excel functions(for example, output, collecting data on one sheet, etc.).

    If you've never heard of a macro, the most accurate definition of it would be: these are actions that are programmed for a specific sequence and written in the programming environment in Visual Basic for Applications (VBA). Running a macro can be done multiple times and this will force Excel to perform any sequence of actions we need that we simply don’t like or don’t want to do manually. Despite the great variety of programming languages ​​for the entire Microsoft Office complex, VBA is the standard and it works in any application in the office suite.

    So, there are 2 ways to create a macro in Excel:

    Create a macro in Excelusing a macro recorder

    First, let's clarify what a macro recorder is and what a macro has to do with it.

    Macro recorder is a small program built into Excel that interprets any user action in language codes VBA programming and writes into the software module the commands that were obtained during the work process. That is, if we create the daily report we need with the macro recorder turned on, the macro recorder will record everything in its commands step by step and, as a result, create a macro that will create a daily report automatically.

    This method is very useful for those who do not have the skills and knowledge of working in the VBA language environment. But such ease of execution and recording of a macro has its disadvantages, as well as its advantages:

    • A macro recorder can only record what it can touch, which means it can record actions only when buttons, icons, menu commands and everything like that are used; such options are not available to it;
    • If an error was made during the recording period, it will also be recorded. But you can use the cancel button last action, erase the last command that you wrote incorrectly in VBA;
    • Recording in the macro recorder is carried out only within the boundaries of the MS Excel window, and if you close the program or turn on another one, the recording will be stopped and will no longer be performed.

    To enable the macro recorder for recording, you must perform the following steps:


    The next step in working with the macro recorder will be to configure its parameters for further recording of the macro; this can be done in the window "Record a Macro", Where:

    • Macro name field- you can write a name that you understand in any language, but it must begin with a letter and not contain punctuation marks or spaces;
    • Keyboard shortcut field- will be used by you in the future for quick start your macro. In case you need to register a new one, this option will be available in the menu “Tools” - “Macro” - “Macros” - “Run” or on the tab "Developer" pressing a button "Macros";
    • "Save to..." field— you can specify the location where the macro text will be saved (but not sent), and there are 3 options:
      • "This book"— the macro will be written to the module of the current workbook and can only be executed if the given Excel workbook will be open;
      • "New Book"— the macro will be saved in the template on the basis of which an empty template is created in Excel new book, which means that the macro will become available in all workbooks that will be created on this computer from now on;
      • "Personal Macro Book"- is a special book Excel macros, which is called "Personal.xls" and is used as a special storage library of macros. At startup, macros from the “Personal.xls” workbook are loaded into memory and can be launched in any workbook at any time.
    • "Description" field- here you can describe what the macro should do and how, why it was created and what functions it has, this is a purely informative field, which is called memory.

    Once you have run and recorded your macro, completing all necessary actions, recording can be stopped with the command "Stop recording" and your macro will be created using the macro recorder.

    Create a macro in Excelin the Visual Basic editor.

    In this method, we will look at how to create a macro in Excel through the VBA program editor which, as I said above, is built into any version of Excel.

    Launching the VBA program editor occurs differently, depending on the version of your Excel program:

    • in versions of Excel 2003 and later, we need in the menu "Service", select item "Macro" and press "Visual Basic Editor";
    • in versions of Excel 2007 and later, we need on the tab "Developer" press the button "Visual Basic Editor". If you do not find this tab, you need to activate it by selecting the menu item “File” - “Options” - “Customize Ribbon” and in the dialog box, use the checkbox to activate the tab "Developer".

    In the window that opens, you can see the interface of the VBA editor, everything here is written in English and there are no localizations, you don’t have to look for it, but just accept it and work, especially since it’s only incomprehensible at first, and then everything will be familiar.

    So, how to work in the VBA editor, where is what is stored and how to create a macro in Excel. Questions of this nature arise immediately as soon as you see the editor, and now we will consider all of them.

    All our macros will be stored in so-called software modules. In any of the books Excel versions we can create any software modules in any quantity and place all the macros we create in them. One module can contain any number of macros you need or create. Modules are available in the window "Project Explorer" and are located in the upper left corner of the macro editor (you can also call it with the key combination CTRL+R).

    Program modules in the VBA editor exist in several types and are used to different options and situations:


    Actually, the macro itself, when working in a standard module, looks like this:

    Let's look at an example of a working macro:

    • All macros in mandatory will begin with the operator Sub, followed by the name of your macro and a list of arguments in parentheses. In cases where there are no arguments, the parentheses should be left blank;
    • All macros must end with the operator End Sub;
    • Data that is between operators Sub And End Sub, is the macro body that will run when the macro is run. In the example, the macro checks and, when entering data, finds it in the database list and displays the value specified by the criteria.

    As you can see, the second method is more difficult to use and understand if you have no experience in programming in general or in VBA in particular. It is very difficult to understand and figure out what commands are entered and how, what arguments it uses in order for the macro to start doing its work automatically. But the one who walks will master the road, as the ancient sages said, and therefore you should not give up, but follow the commandments of grandfather Lenin...

    Creating a button to run macros in the toolbar

    As I said earlier you can call a macro procedure hot combination keys, but it's very tedious to remember which combination is assigned to whom, so your best bet would be to create a button to run the macro. Buttons can be created of several types, namely:


    This method is available for any version of MS Excel and consists in the fact that we will place the button directly on our worksheet as a graphic object. To do this you need:

    • In MS Excel 2003 and older go to menu "View", choose "Toolbar" and press the button "Forms".
    • In MS Excel 2007 and later you need on the tab "Developer" open dropdown menu "Insert" and select an object "Button".

    After all this, you must draw a button on your sheet while holding down the left mouse button. After finishing the drawing process, a window will automatically open where you will need to select the macro that is required to be executed when you click on your button.

    How to Create Custom Functions in VBA

    In principle, creating so-called user-defined functions is not very different from creating a regular macro in a standard program module. The difference between these concepts is that a macro will perform the actions contained in it with book or sheet objects (these are formulas, cells, etc.), but a user-defined function only works with the values ​​that it receives from us and these are arguments initial data for calculations.

    For example, to create a custom value added tax, aka VAT function, we need to open our VBA editor and add new module, select from the menu "Insert" paragraph "Module" and enter the text for our function there: It should be noted that the main difference between a function and a macro is the header Function replaced Sub and there is a completed list of arguments, in our example this is Summa. Once our code has been entered, it will become available in standard window Function Wizard, which is located in the menu "Formulas", paragraph "Insert Function".
    And select a category "User Defined" in which our written function will be displayed "NDS".
    After selecting our function, you can place the cursor on the cell with the argument, which will contain the amount for which we calculate VAT, everything happens as with a regular function.
    And that's all for me! I really hope that article on how to create a macro in Excel was clear and useful to you. I would be very grateful for your comments, as this is an indicator of readability and inspires me to write new articles! Share what you read with your friends and like it!

    If you are not strong in programming, but at the same time, you need to implement someFor a task that goes beyond the standard functions and commands of MS Excel, you can search for a solution on the Internet. The solution will most likely come in the form of VBA code that you need to copy and paste into your workbook, then somehow make this code work for you, in this article I will tell you how to do this.

    Let's look at two examples:

    1. Find and use the command

    For example, we need a command that would insert the number “1” into the selected cells. Launch the search engine, type search phrase, we get the results, start browsing, and find code something like this:

    Sub Insertion1() Dim q As Object On Error Resume Next Set q = Cells For Each q In Selection q = 1 Next q End Sub

    We highlight this code(without line numbering, starting with the word Sub) and press Ctrl+C. Go to your MS Excel workbook and press the keyboard shortcut Alt+F11, a VBA editor window will open:

    In the left window "Project - VBA Project" we select (click with the mouse) our workbook into which we need to insert the macro, for example, "VBAProject (Book2)":

    In the "Insert" menu item, select "Module":

    In the left window "Project - VBA Project" you should see new folder"Modules" and in it new object"Module1":

    Move the cursor to the right “Large” input field and click Ctrl+V, the copied macro is pasted into the module:

    Macros in MS Excel can be inserted in the following places:

    • In the Module, they usually insert the code of macros, which will be launched when the user presses a button (as, for example, in our case) or the code of functions (formulas);
    • In the Worksheet, the code of macros is usually inserted, the launch of which should occur automatically depending on user actions or changes in data in the sheet (the data has changed, the macro is executed);
    • Macro code is usually inserted into the Workbook, which should be launched automatically depending on the actions performed on the workbook (file). For example, a macro that runs when a workbook is opened or closed, or when it is saved;
    • Macros can also be part of a user form.

    Typically, the person who posts the code specifies where it should be inserted, into a module, worksheet, or workbook.

    To insert code into a Worksheet, select the appropriate sheet in the left window of the VBA editor, double-click on it with the left mouse button, move the cursor to the right input field and paste the code.

    To insert a code into a Book, select "ThisBook":


    Let's practice. Paste the code posted below into "Sheet1".

    Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo a If Target = 2 Then VBA.MsgBox("Cell " & Target.Address & " = 2") End If a: Exit Sub End Sub

    This macro outputs information message if you enter "2" in any cell of the sheet.

    Return to the workbook, go to “Sheet1” and enter the number “2” in cell “A1” and click Enter, after which you should see the following message:

    If you see this message, then you did everything right. If not, then you inserted the code somewhere in the wrong place, try again.

    When pasting code, you need to be careful where you paste it. This can be done by looking at what is written in the title of the VBA editor window:

    2. Find and use the function

    We find on the Internet the VBA code of a function that counts, for example, the number of words in a cell:

    Public Function Number Of WordsIn Cell(Cell As Range) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Cell.Value), " ") Number Of WordsIn Cell = UBound(q) + 1 End Function

    Copy the code and press the key combination Alt+F11, the VBA editor will open:


    Add a new module to your workbook and paste the copied code into this module:


    Close the VBA editor and go to your workbook. Create a new sheet (optional) in a cell A1 enter the text “mom washed the frame.” We go to the cell in which we want to get the result (number of words), in the “Formulas” menu, click the “Insert function” button:

    In the "Insert Function" window that opens, in the "Category" field, select "User Defined"

    In the list of available functions, select “Number of Words in Cell”, click “OK”:

    Enter the required arguments and click "OK":

    We get the result:

    Important:

    If you do not save the workbook in which you inserted the macro as a “Macro-enabled workbook,” all modules with macros will be deleted and you will have to do all this work again.

    If, when inserting a macro into a module, you see red signs instead of some text " ???????? "

    This means that you have an encoding problem, which is observed with copying Cyrillic text from some browsers. To overcome this problem, try pasting the copied code into blank sheet MS Excel as "Unicode text". To do this, go to the MS Excel workbook, select or create a blank sheet, go to cell "A1" and press the keyboard shortcuts Ctrl+Alt+V. The Paste Special menu should appear, select Unicode Text and click OK.

    The code should be pasted into the worksheet without the question marks:

    After that, copy the code pasted into the sheet again and paste it into the module.

    If you do not see the "Project - VBA Project" window in the VBA editor, go to the "View" menu tab and select "Project Explorer" from the list or press the keyboard shortcut Ctrl+R:

    Objects such as macros in Excel will help you properly organize the work process in Excel.

    Let's take a closer look at all the features of working with these objects in the MS Office software package.

    Thanks to the use of macros, every cell of your document can be automated. This happens by allowing the user to record all actions as they are created.

    What are macros and why are they needed?

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

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

    Their creation and use will help to significantly save time and maximize automation of the robot in the program.

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

    Its body is essentially made up of instructions that tell the program what to do if users choose one or the other.

    You can also encounter the concept of a macro in Word, but in Excel it has several advantages:

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

    We note two more articles that mayyou will need:

    Creating your own macros in Excel 2010, 2007, 2013

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

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

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

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

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

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

    • Now start performing the actions that will be recorded in the macro. Each cell can be filled with certain data.
      You can also work with only one cell; after recording and enabling the macro, the same cell will be recorded according to the specified algorithm;
    • Don't forget to press the stop data recording button. After completing all the above steps, it will be recorded and saved in the program.

    How to enable and work with macros in Excel

    To do this, follow the instructions below:

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

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

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

    The macro is most convenient to use when a certain cell needs to be copied many times.

    Working with books

    Macro 1. Creating a new workbook from scratch
    Macro 2. Saving a workbook when a specific cell/range is changed
    Macro 3. Saving the workbook before closing
    Macro 4. Protecting a worksheet in a workbook before closing
    Macro 5. Unprotecting a sheet when opening an Excel file
    Macro 6. Open a workbook on the desired sheet
    Macro 7. Opening a specific user-defined workbook
    Macro 8. Determine whether the book is open
    Macro 9. Determine whether a book exists in a folder
    Macro 10. Update all connections in open workbooks
    Macro 11. Close all books at once
    Macro 12. Open all workbooks in a folder
    Macro 13. Print all books in a folder
    Macro 14. Do not allow the book to be closed until the cell is filled
    Macro 15. Create backup copy current book with today's date

    Working with sheets

    Macro 16. Add a new worksheet and assign a name
    Macro 17. Delete all sheets except the active one
    Macro 18. Hide everything except the active worksheet
    Macro 19. Display all sheets of the workbook
    Macro 20. Moving Worksheets
    Macro 21. Sorting sheets by name
    Macro 22. Group sheets by Label Color
    Macro 23. Copy a sheet to a new workbook
    Macro 24. Create a new workbook for each sheet
    Macro 25. Printing sheets
    Macro 26. Protect all sheets
    Macro 27. Unprotect all sheets
    Macro 28. Creating a table of contents
    Macro 29: Double-click to zoom a worksheet
    Macro 30. Select column of active row

    Selecting and changing ranges

    Macro 31. Selecting and formatting a range
    Macro 32. Creating and selecting named ranges
    Macro 33. Enumeration using a number of cells
    Macro 34. Selecting and formatting ranges
    Macro 35. Inserting empty rows in a range
    Macro 36. Show all hidden lines and columns
    Macro 37. Removing blank lines
    Macro 38. Removing empty columns
    Macro 39. Selecting and formatting all formulas in the workbook
    Macro 40: Find and select the first empty row or column
    Macro 41. Apply additional fill color
    Macro 42. Sort ranges by double click
    Macro 43. Limiting the scroll range in a particular area
    Macro 44. Automatically set the print area of ​​the sheet

    Working with data

    Macro 45. Copy and paste a range
    Macro 46: Convert all formulas in a range to values
    Macro 47. Transform text values to numeric
    Macro 48. Converting a dash to a minus
    Macro 49. Delete extra spaces from all cells in the range
    Macro 50. Cut off 5 characters on the left in each cell of the range
    Macro 51. Add missing zeros to the cell
    Macro 52. Replace empty cells with zero
    Macro 53. Adding text to the beginning or end of a cell
    Macro 54. Creating a data conversion macro
    Macro 55. Clear data (non-printing characters)
    Macro 56. Select duplicates in the data range
    Macro 57. Hiding repeating lines
    Macro 58. Selectively hide the autofilter arrows
    Macro 59. Copy the filtered rows to a new workbook
    Macro 60. Creating a new sheet for each element in the AutoFilter
    Macro 61. Show filtered columns in status bar

    Working with Pivot Tables

    Macro 62: Creating Backwards Compatibility pivot table
    Macro 63. Updating all pivot tables in a book
    Macro 64. Creating an “inventory” of all the pivot tables of the book
    Macro 65. Create all pivot tables using the same data cache
    Macro 66. Hide all subtotals in the pivot table
    Macro 67. Change the data names of all summary fields
    Macro 68. Forced summation for all summary data
    Macro 69: Apply number format to all data items
    Macro 70. Sorting summary fields in alphabetical order
    Macro 71. Apply custom sorting to data items
    Macro 72: Putting protection on the pivot table
    Macro 73: Apply Pivot Field Constraints
    Macro 74. Automatic removal sheets with summary details
    Macro 75: Print a pivot table for each filter element
    Macro 76. Create a new file for each filter element
    Macro 77. Preparing a data range for a pivot table

    Working with charts and graphs

    Macro 78. Resizing charts on a worksheet
    Macro 79. Linking the chart to a specific range
    Macro 80: Creating a set of disjointed diagrams
    Macro 81: Print all charts on a worksheet
    Macro 82. Marking the best and worst values ​​on
    Macro 83. Same colors for values ​​on different charts
    Macro 84. Matching the color of charts to the color of ranges

    Sending emails from Excel

    Macro 85. Sending an active book by mail (attachment)
    Macro 86: Sending a range of values ​​as an attachment
    Macro 87. Sending one sheet as an attachment
    Macro 88. Send an email with a link to our files
    Macro 89: Sending emails adding addresses to our contact list
    Macro 90. Saving all attachments in a separate folder
    Macro 91. Saving specific attachments to a folder

    Interoperability with other Office applications

    Macro 92. Running an access request from Excel



    Macro 96. Compressing an Access database from Excel
    Macro 97. Sending Excel data to a Word document
    Macro 98. Doing a Merge with a Word Document
    Macro 99: Sending Excel Data to a PowerPoint Presentation
    Macro 100. Sending Excel charts in PowerPoint presentations
    Macro 101: Converting a Workbook to a PowerPoint Presentation

    Interoperability with other Office applications
    learn to work with Word, Access and PowerPoint
    Macro 92. Running an access request from Excel
    Macro 93. Running an Access macro from Excel
    Macro 94. Opening Access report from Excel
    Macro 95. Opening an access form from Excel

    Read more: