This short step-by-step guide is intended for beginners and explains how to insert VBA (Visual Basic for Applications) code into an Excel workbook, and how to run the inserted macro to perform various tasks on that worksheet.
Most users are not Microsoft Office gurus. They may not know all the intricacies of a particular function, and will not be able to answer the question of how the speed of VBA macro execution differs in Excel 2010 and 2013. Many simply use Excel as a data processing tool.
Suppose you want to change the data in an Excel sheet in a certain way. We googled a lot and found a VBA macro that solves this problem. However, our knowledge of VBA leaves much to be desired. This is where step-by-step instructions come to the rescue, with which we can use the found code.
Paste VBA code into an Excel workbook
In this example, we will use a VBA macro that removes line breaks from cells in the current Excel sheet.
- Open a workbook in Excel.
- By pressing Alt + F11 call the Visual Basic editor window
- On the panel Project-VBAProject right-click on the name of the workbook (in the upper left part of the editor window) and select in the context menu Insertion > Modules.
- Copy the VBA code (from a web page or other source) and paste it into the right area of the VBA editor (Window Module1).Tip: How to speed up macro execution?
At the very beginning of your VBA macro code, the lines should contain:
Application.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualIf there are no such lines, then be sure to add the following lines to your macro so that it runs faster (see the figure above):
- To the very beginning of the code after all lines starting with Dim (if lines starting with Dim no, then insert immediately after the line Sub):
Application.ScreenUpdating = FalseApplication.Calculation = xlCalculationManual - At the very end of the code, before End Sub:
Application.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomatic
These lines, as can be understood from their content, disable screen refresh and recalculation of workbook formulas before executing the macro. After the code is executed, these options are enabled again. This trick speeds up macro execution from 10% to 500% (yes, a macro can run 5 times faster if cell manipulations occur without unnecessary stops).
- To the very beginning of the code after all lines starting with Dim (if lines starting with Dim no, then insert immediately after the line Sub):
- Save the workbook as an Excel workbook with macro support. To do this, click Ctrl + S and in the warning window The following components cannot be saved in a workbook without macro support (The following features cannot be saved in macro-free workbook) нажмите No (No).A dialog box will open Saving a document (Save as). In drop down list File Type (Save as type) select Macro-enabled Excel workbook (Excel macro-enabled workbook) and click the button Save (Save).
Tip: How to speed up macro execution?
A dialog box will open Saving a document (Save as). In drop down list File Type (Save as type) select Macro-enabled Excel workbook (Excel macro-enabled workbook) and click the button Save (Save).
- Click here Alt + Qto close the VBA editor window and return to the Excel workbook.
We run the VBA macro in Excel
To run the macro you just added, click Alt + F8… A dialog box will open Macro (macro). Listed macro name (Macro name) select the desired macro and press the button Run (Run).
