Effective work in MS Office. VBA Excel: sample programs
VBA is considered the standard scripting language for Microsoft applications and is now included in all Office applications and even applications from other companies. Therefore, once you master VBA for Excel, you can immediately move on to creating macros for other software. Microsoft products. Moreover, you will be able to create full-fledged software products, simultaneously using the functions of a variety of applications.
How to enable macros in Excel
By default, the tab responsible for managing and navigating macros in Excel is hidden. To activate this option go to the tab File to the group Options. In the dialog box that appears OptionsExcel go to the tab Ribbon customization, in the right combo box, place a marker opposite the tab Developer. These actions are relevant for Excel versions 2010 and older.
Appear on the tape new inset Developer with Excel automation controls.
Writing macros in Excel
In the tab Developer in Group Code, click the button Record a macro. A dialog box will appear Record a macro, which requests some information about the future code being written. If this is your first time creating a macro, you can simply click the button OK. WITH at this moment Excel will record every user action in a VBA module, be it data entry, formatting, or creating charts. To stop recording a macro, click the button Stop recording which is in the same group Code.
You can also take advantage of an alternative option for recording macros by using the button Record a macro, which is in the lower left corner workbook Excel (to the right of the status Ready).
Now you can view a list of all created macros by clicking on the button Macro, located in the group Code. In the dialog box that appears, you can give more descriptive names to your codes or set keyboard shortcuts that would run a particular macro. Alternative option to launch this window is to press Alt keys+ F8.
Editing Macros
Congratulations! You've written your first macro. It would be logical to check now what code Excel generated for us. The generated code is written in VBA ( Visual Basic for Applications). You have to open it to see it. EditorVB(VBE), which is launched by pressing Alt + F11 or the button VisualBasic on the tab Developer.
To avoid confusion in the editor, you can work with only one tab in a workbook, sheet, or module. This is what the editor looks like in real life.
I offer on at this stage study in more detail various windows and VBA editor menu. This will help you save a lot of time in the future.
To view the code, click on the thread Modules in the projects window and double-click on the branch that appears Module1 . The editor will open a window with the code, as shown in the picture.
Here you can edit the generated code that was written while working in Excel. For example, you need to fill a certain column with values from 1 to 10. You already have the first three steps, which enter the values 1, 2 and 3 in the first three cells of column A. We need to complete the remaining seven steps.
If you look at the code above, you will see that the macro is structured in a certain way. The application first moves the cursor to the cell using the Range("A1").Select command, then edits its contents using ActiveCell.FormulaR1C1 = "1". So for the remaining steps we can repeat these steps, changing the cell address and the value you want to write to that cell. For example, to set cell A4 to 4, you would write:
Range("A4").Select |
And repeat similar steps for the remaining values.
Once you're done editing, save your book. You can run the macro by pressing the F5 button, or by returning to the working Excel workbook, go to tab Developer to the group Code -> Macros and select from the list the macro you are interested in.
Take a few minutes to carefully study the code that Excel generated. If you're a beginner, investing a few minutes in learning the code will yield amazing results in getting to know VBA objects later on. Please note that the example we discussed is just an illustration. There are faster and effective ways achieving similar results, which we will discuss later.
Increase the speed of Excel macro execution
So far so good. Let's look at a couple of tricks that will help speed up macro execution. Let's take the code snippet above as an example. Modern computers will work on the code in question so quickly that you won’t even notice it. But what if you need to perform the operation 50,000 times. This will take some time. If the macro you write is hundreds of lines long, you can speed up code execution by trimming the part of the processes that is not used during macro execution.
Using the Application.ScreenUpdating Command
The first trick is to avoid updating the screen while the macro is running. This will allow Excel to save computing power computer and update the screen with the latest values only after all the code has been executed. To do this, you need to add a command to disable screen refresh at the beginning of the macro and a command to enable screen refresh at the end of the macro.
1 | Sub Macro1() |
The Application.ScreenUpdating command tells Excel to stop displaying the recalculated data on the screen and return the finished values at the end of the code execution.
Using the Application command. Calculation
The second trick is to disable automatic calculations. Let me explain. Every time a user or process updates a cell, Excel tries to recalculate all the cells that depend on it. So let's say if the cell that the macro is trying to update affects 10,000 other cells, Excel will try to recalculate them all before the code finishes executing. Accordingly, if there is whole line influencing cells, recalculation can significantly slow down code execution. To prevent this from happening, you can install the Application command. Calculation at the beginning of the code, which will switch the recalculation of formulas to manual mode and then return the automatic calculation at the end of the macro.
1 | Sub Macro1() |
Be careful, do not forget to switch this option again in auto mode at the end of the macro. Otherwise, you will need to do this in Excel itself by clicking on the tab Formulas to the group Calculation and select Calculation options –> Automatic.
Avoiding selecting cells and ranges
In mode automatic recording macros, you may notice that Excel very often uses the cell selection command, for example, Range("A1").Select. In our example, we used this command repeatedly to select a cell and change its value. You can avoid this by simply specifying the cell address and giving it the required value (The macro recorded the cursor movement from one cell to another, hence inserting these steps. However, they are not necessary). So, a more efficient code would look like this.
1 | Sub Macro1() |
IN in this case we simply referenced the cell and gave it the required value without selecting it at all. This method is faster than the previous one.
Excel Macro Examples
Below are some sample VBA codes that will help you automate the most common tasks.
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. 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 color fills
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 cell 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: Creation backward 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. Hiding everything subtotals in a 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. Celebrating the best and worst value 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 certain investments 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
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 of 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 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 Microsoft management Outlook and send emails directly from your spreadsheet or copy your notebook entries. Of course, Excel features are such that but 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 [...]
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 for creating. 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.
Macros are most convenient to use when specific cell needs repeated copying.