Effective work in MS Office. How to Automate Routine Tasks in Excel Using Macros
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. 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. Base compression Access data from Excel
Macro 97. Sending Excel data to a Word document
Macro 98. Doing a Merger 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
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 Excel cell references work, and the use of 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 searched row was found "If the specified row is found, indicate in which cell the match was found If iRowNumber = 0 Then MsgBox "The row " & sFindText & " was not found " Else MsgBox " Row "&sFindText&" 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 prints 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 next value sequence Dim iStep As Integer "Stores the 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 "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 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 "The following command writes the result to column A of the active worksheet "Indicate the sheet name in the link not necessary since this is the active leaf 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 box if cell B1 is selected on the current worksheet. 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 required 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
Excel tables are one of the most popular ways to store and organize information. If implemented in Excel macros, then the program’s capabilities can be expanded almost twice. 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 created by Visual help Basic for Applications code. 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 automatically.
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 their use 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 there similar example, and for those who are just getting acquainted with all the possibilities of the table, this best way learn to fully use 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 capabilities 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()
If you are not strong in programming, but at the same time, you need to implement somea task that goes beyond standard features and MS Excel commands, 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 new module Paste the copied code into your book and 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 occurs 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. A menu should appear " Special insert", 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:
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 manage Excel. 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 email 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 a 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, you 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 [...]