Menu
homePhoto and video

Collection of macros and excel functions. Automate tasks using Macro Recorder - Excel. Solving a problem using VBA programming

Macro for highlighting cell A1 on each sheet in the active workbook. This also causes the screen to move.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Select Next Sheets(1) .Select Application.ScreenUpdating = True End Sub

Macro for copying the current sheet a specified number of times. Useful for testing some macros - made edits, checked them on a copy of the data. We ran out of copies—run the macro again

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Enter the number of copies of the current sheet") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets.Count) ActiveSheet .Name = "Copy" & j Next j Application.ScreenUpdating = True End Sub

Create sheets with titles from a specified range on a sheet

Sub CreateFromList() Dim cell As Range For Each cell In Selection Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Next cell End Sub

Markros for sending a letter with a delay. Modified macro from John Walkenbach's book Professional VBA Programming

Sub SendLetter() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " [email protected]" .Subject = "Sales report" .Attachments.Add "C:\Test.txt" .Body = "Email text" .DeferredDeliveryTime = Replace(Date, ".", "/") & " 11:00:00 " .send ".Display to generate a letter and open it End With On Error GoTo 0 Set OutMail = Nothing cleanup: Set OutApp = Nothing End Sub

Slightly modified table of contents macro from Nikolai Pavlov.
If a “Table of Contents” sheet already exists in the book, the macro prompts you to delete it. If not, creates a “Table of Contents” sheet and inserts links with sheet names

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False With ActiveWorkbook For Each Worksheet In ActiveWorkbook.Worksheets If Worksheet.Name = "Table of Contents" Then Answer = MsgBox("The workbook has a sheet with the name Table of Contents. Delete it?", vbYesNo) If Answer = vbNo Then Exit Sub If Answer = vbYes Then Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array(1)).Select Sheets.Add Sheets(1).Name = "Table of Contents" With ActiveWorkbook For Each sheet In ActiveWorkbook.Worksheets If sheet.Name<>"Table of Contents" Then Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name End If Next sheet End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub

Sorting Sheets from the VBA Wizards. The macro also sorts hidden sheets. Will not work if the book has a structure protected

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " remember the visibility state of each sheet and do everything visible For Each iSht In ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " sorting visible sheets For i = 1 To .Sheets.Count - 1 For j = i + 1 To .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Then .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With " restore the initial state visibility of each sheet For Each iSht In ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Next Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Import columns "Field1" and "Field2" from sheet "Sheet1" Excel file“C:\Manager.xls” via ADODB connection and inserting content starting from cell A1 of the current sheet

Even if you don't know anything about VBA and macros, you'll be inspired to learn more after reading some of the tricks and tricks in this part. If you are just starting to work with macros, you can find useful information here.

. .

In previous articles, you saw how Excel can manage others Microsoft applications such as Word and Outlook. But at the same time, other applications can also be used to Excel management. For example, you wrote a macro for Word or Access that creates an Excel table, fills it with data, and then saves it. Excel does not necessarily have to [...]

. .

You can use this technology to control Microsoft Outlook and send emails directly from your spreadsheet or copy your notebook entries. Of course, the features of Excel are such that e-mail the table will be sent, but this method only allows you to send part of the table. In order for this code to work, you need to have […]

. .

This method can be very useful, for example if you have standard document with tables filled with data macros from Excel tables. You can run the macro and the data will be transferred to the tables in Word document. Recently I had the task of writing a program to fill out a report on SLA (Service Level Agreement). Report […]

. .

Macros are often used to automate the operation of applications. Any macro is a sequence of actions recorded under a specific name. If when working with Microsoft Excel there is a need to perform the same sequence of operations several times (for example, complex formatting of the current cell or adding new line with filling some of its cells with formulas), then you can write down these actions, and [...]

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 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 active row column

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. Insert empty lines in the 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 PivotTable for Each Filter Item
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. Base compression Access data 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 an Access report from Excel
Macro 95. Opening an access form from Excel

Next simple examples Excel macros illustrate some of the features and techniques described in the Excel VBA tutorial.

Excel Macro: Example 1

Initially this procedure Sub was given as an example of using comments in VBA code. However, here you can also see how variables are declared, how references work Excel cells, using a loop For, conditional operator If and displaying a message window.

"The Sub procedure searches for a cell containing the specified string "in the range of cells A1:A100 of the active sheet Sub Find_String(sFindText As String) Dim i As Integer "An integer of type Integer, used in a For loop Dim iRowNumber As Integer "An integer of type Integer for storing the result iRowNumber = 0 "Looks through cells A1:A100 one by one until the string is found sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then "If a match to the specified string is found " save the number current line and we leave from For loop iRowNumber = i Exit For End If Next i "We inform the user in a pop-up window whether the required row has been found "If given string found, indicate in which cell the match was found If iRowNumber = 0 Then MsgBox "The row " & sFindText & " was not found" Else MsgBox "The row " & sFindText & " was found in cell A" & iRowNumber End If End Sub

Excel Macro: Example 2

Next procedure Sub– example of using a loop Do While. You can also see how variables are declared, working with Excel cell references, and using a conditional statement. If.

"The Sub procedure outputs Fibonacci numbers not exceeding 1000 Sub Fibonacci() Dim i As Integer "A counter to indicate the position of an element in the sequence Dim iFib As Integer "Stores the current value of the sequence Dim iFib_Next As Integer "Stores the next value of the sequence Dim iStep As Integer "Stores size of the next increment "Initialize the variables i and iFib_Next i = 1 iFib_Next = 0 "The Do While loop will be executed until the value of the "current Fibonacci number exceeds 1000 Do While iFib_Next< 1000 If i = 1 Then "A special case for the first element of the sequence iStep = 1 iFib = 0 Else "Save the size of the next increment before overwriting "the current value of the sequence iStep = iFib iFib = iFib_Next End If "Output the current Fibonacci number in column A of the active worksheet "in the row with index i Cells(i, 1).Value = iFib "Calculate the next Fibonacci number and increase the element's position index by 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Excel Macro: Example 3

This procedure Sub scans the cells of a column A active sheet until it encounters an empty cell. The values ​​are written to an array. This simple Excel macro shows how to work with dynamic arrays and also using a loop Do Until. IN in this example we will not perform any actions with the array, although in real programming practice, after the data is written to the array, such actions are usually performed on them.

"The Sub procedure stores the cell values ​​of column A of the active sheet in the array Sub GetCellValues() Dim iRow As Integer "Stores the number of the current row Dim dCellValues() As Double "An array for storing the cell values ​​iRow = 1 ReDim dCellValues(1 To 10) "Do Loop Until iterates sequentially through the cells of column A of the active sheet "and extracts their values ​​into an array until an empty cell is encountered Do Until IsEmpty(Cells(iRow, 1)) "Check that the dCellValues ​​array is of sufficient size "If not, increase the size array by 10 using ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Excel Macro: Example 4

In this example, the procedure Sub reads values ​​from a column A worksheet Sheet2 and does it with them arithmetic operations. The results are entered into the column cells A on the active worksheet. This macro demonstrates the use of Excel objects. In particular, the appeal is carried out by the procedure Sub to the object Columns, and shows how this object is accessed through the object Worksheet. It is also shown that when accessing a cell or range of cells on the active sheet, it is not necessary to specify the name of this sheet when writing the link.

"The Sub procedure, using a loop, reads the values ​​in column A of the worksheet Sheet2, "performs arithmetic operations with each value and writes the result to "column A of the active worksheet (Sheet1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Assign to variable Col column A of worksheet Sheet 2 Set Col = Sheets("Sheet2").Columns("A") i = 1 "Using a loop, we read the values ​​of the cells of column Col until "until an empty cell is encountered Do Until IsEmpty(Col.Cells(i)) "Perform arithmetic operations on the value of the current cell dVal = Col.Cells(i).Value * 3 - 1" Next command writes the result in column A of the active worksheet "There is no need to indicate the sheet name in the link, since this is the active sheet. Cells(i, 1) = dVal i = i + 1 Loop End Sub

Excel Macro: Example 5

This macro shows an example of VBA code that monitors an Excel event. The event to which the macro is attached occurs each time a cell or range of cells is selected on the worksheet. In our case, when selecting a cell B1, a message window appears on the screen.

"This code shows a message window if on the current worksheet "Cell B1 is selected Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Check whether cell B1 is selected If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then "If cell B1 is selected, perform the necessary action MsgBox "You have selected cell B1" End If End Sub

Excel Macro: Example 6

This procedure illustrates the use of operators On Error And Resume for error handling. This code also shows an example of opening and reading data from a file.

"The Sub procedure assigns the arguments Val1 and Val2 the values ​​of cells A1 and B1" from the workbook Data.xlsx located in the folder C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling " Open the workbook with the data Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Assign the variables Val1 and Val2 values ​​from the given workbook Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets("Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "If the file is not found, the user will be prompted to place the file they are looking for" in the desired folder and after that continue executing the macro MsgBox "The file Data.xlsx was not found! " & _ "Please add the workbook to the C:\Documents and Settings folder and click OK" Resume End Sub

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 as twins weekly, monthly reports, and etc. But using macros will allow you to perform these actions in automatic mode using Excel capabilities 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 have not yet been implemented in standard features Excel (for example, output, collecting data on one sheet, etc.).

If you have never heard of a macro, then the most accurate definition of it would be this: these are actions that are programmed for a certain sequence and written in a programming environment in the language 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 complex Microsoft Office VBA is the standard and it works in any office suite application.

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;
    • "A new book"— the macro will be saved in the template on the basis of which an empty template is created in Excel A 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 Excel macro workbook 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 empty;
  • All macros must end with the operator End Sub;
  • Data that is between operators Sub And End Sub, is the body of the macro 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 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 the drawing process is completed, 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 the macro will perform the actions contained in it with book or sheet objects (these are formulas, cells, etc.), but the user function only works with the values ​​​​that it receives from us and these are the arguments initial data for calculations.

For example, to create custom function value added tax, aka VAT, 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!