In some cases when you work in Excel, specific needs and problems should be solved using VBA macros. VBA macros automate work in Excel and make it easier more demanding solutions. You do not have to be a programmer in Visual Basic Application for Excel program, in order to a minor problem or request solve using VBA macro. Excel 2013 for you can automatically record a specific action that you want to repeat or to solve a specific task.
To be able to record a VBA macro is necessary to add DEVELOPER toolbar on the ribbon if not active (This toolbar when you install Excel 2013 is not active on the ribbon). In general, for recording a VBA macro is not needed Developer Toolbar but there are additional commands for working with VBA macros.
How to Add DEVELOPER Toolbar on the Ribbon in Excel
To add Developer toolbar needed over File - Info (backstage view window) menu to open the Excel Options dialog box.
In the dialog window Excel Option click you on the left menu Customize Ribbon. In the right part of the right of the window, in the Main Tabs turn on Developer (see image below).
How to create a VBA macro in Excel
Recording VBA macros can run in three modes.
- VIEW toolbar => Macros => Record Macro
- DEVELOPER toolbar => Record Macro
- Status Bar => Record New Macro button/icon (see third picture below)
When you run a VBA macro for recording, you will see a dialog window Record Macro. In this dialog box, specify a name for the VBA macro. Set the optionally shortcut to run VBA macro. Save a VBA macro in the active workbook This Workbook or Personal Macro Workbook or New Workbook. The name must not have spaces between words. e.g. CopyRangeToSheet2. Click on the OK button.
Now, we come to the most interesting part when recording a VBA macro. When you record VBA macro in Excel, there is no need to a hurry. Excel will remember and record just click on the worksheet or workbook.
So, after clicking on the OK button (picture above) Record Macro dialog window closes. Now you need to do step-by-step specific action, which will remember Excel 2013. Let's take an example that I did.
- On the Developer toolbar, I clicked on Record Macro command
- I selected a range of cells A1:B5 on Sheet1
- I clicked the right mouse button and from shortcut menu I chose Copy
- I clicked on the name of Sheet2 for positioning of him
- I clicked on the cell G1 on Sheet2 that I was selected, because it is a destination where I want to copy the data from Sheet1
- I clicked the right mouse button on a cell G1 and from the shortcut menu I chose Paste
- After copying the data I clicked on cell A1 is selected, because I want her to continue to work after copying.
- I clicked on the toolbar developer on Stop Record Macro command to stop recording the macro
With this step I have finished recording a VBA macro.
How to review the VBA macro code in Excel after the recording.
The next step is to look at VBA code that Excel recorded while I clicked on on worksheets, Sheet1 and Sheet2. So, right-click on name of the worksheet and from shortcut menu I choose View Code command. I opened my window working Visual Basic for Application and workbook appeared Modules folder and in it Module1.
In the working window of Visual Basic for Application you notice Module1 in the right part of the VBA code recorded macros.
This VBA macro code that I made, I put here with added commentary to you notice that the order of the command means.
Sub CopyRangeToSheet2() 'name VBA procedures, this name is the same name that I set in the dialog window
'CopyRangeToSheet2 Macro 'name VBA macro that I have entered on dialog window
'Copy the range A1:B5 on Sheet2 in G1 'description of what he was doing macro and this description I have entered on the dialog window
'Keyboard Shortcut: Ctrl+Shift+L 'shortcut that I have set on dialog window
Range ("A1:B5"). Select 'first step - after I start recording VBA macro I selected a range of cells A1:B5
Selection.Copy 'second step - the right mouse button I clicked on the Copy command on the shortcut menu
Sheets ("Sheet2"). Select 'third step - I clicked on name of Sheet2
Range ("G1"). Select 'fourth step - I clicked on cell G1 on Sheet2
ActiveSheet.Paste 'fifth step - I clicked the right mouse button on a cell and chose Paste command
Range ("A1"). Select 'sixth step - I clicked on cell A1 and then on command Stop Record Macro
End Sub 'end VBA procedures
How to Record Excel 2013 file that contains VBA macro (*.xlsm)
And finally, when we want to save a workbook that contains VBA macro, it is in Excel 2013 when recording select the type of file you will archive (Save as type). By default in Excel 2013 file that contains VBA macro code has an extension *. xlsm.
You can record a Workbook with the macros and for earlier versions of Excel (Excel 97-2003), which support *.xls and do not support *.xlsm, by choosing with the the drop-down list in the Save as type - Excel 97-2003 Workbook.
How to Set Permission to Run VBA Macros in Excel
To work with VBA macros in Excel, you need to allow the VBA macros to run in Excel by setting permissions to run VBA macros. If you do not know how to enable the VBA to run in Excel then look at the instructions and the image below. The format or extension of an Excel file that can automatically execute VBA macros is *.xls (Excel 2003 and older) and *.xlsm (Excel 2007 and later).
- Click the File command in the Excel text menu
- Click the "Trust Center Settings ..." button
- In the new open window, highlight with the mouse "Macro Settings"
- Click on the radio button, Enable 'all macros' (not recommended and may pose a security risk. But you need to be careful and know who you can trust, ie where is the original workbook)
- The last option in the picture above is a very high security risk and it is not recommended to activate it!
The SHIFT key prevents the VBA macros from running
If you want to prevent the launch of VBA macros when launching an XLS or XLSM file (opening a Workbook from an unknown source) then press the SHIFT key to hold it and open the Workbook.
Be careful and watch whom you trust! !!!!!
If you want to run the Excel file format *.xls or *.xlsm and you are unsure whether the file contains the malicious VBA code, then press the SHIFT key when opening the Workbook (In this way you will prevent the execution of VBA macros).