With VBA macros, you can do many things which aren’t possible using the build in Excel functions. You can save a lot of time by using macros, especially with repeating tasks. Let’s have a look at the necessary steps in order to record a VBA macro.
Before we start to record VBA macros, you might have to do some preparations first.
Add the Developer ribbon
Before you start, you need to add the Developer ribbon. If you can see a Developer ribbon already, you can skip this step.
- Right click on any ribbon.
- Then click on “Customize the Ribbon…”.
- On the right hand side, set the tick at “Developer”.
That’s it, now you should see the Developer ribbon. Usually it’s located next to “View”.
How to insert a new VBA module manually
If you don’t want to record a VBA macro, but start right away with programming it (or pasting the code from the internet…), you can insert a module easily by yourself:
- Open the VBA editor by clicking on the “Visual Basic” button on the left side of the Developer ribbon.
- Right click on “VBAProject(…your workbook name…)”.
- Click on Insert and then on “Module”.
Now you’ve created a new Excel module. You can just paste your code here or start typing.
How to record VBA macros
Let’s face is: creating VBA macros is real programming. Fortunately Excel provides an easy starting point: Record what you are doing and Excel can repeat it. In the background, the source code is automatically created. You start recording, do your steps and stop recording. The following numbers are corresponding to the picture on the right hand side:
- Press on “Record Macro” on the Developer ribbon. If you can’t see the Developer ribbon, right click on any button on any ribbon and click on ‘Customize the Ribbon’. Make sure that ‘Developer’ got a tick mark on the right hand side.
- Give a name to the macro and confirm with “OK”.
- Do the edits to your file which you want Excel to repeat. Click “Stop” when you are done.
- Modify the macro: Usually you’d want to make the source code work universally, for example, on each sheet. Therefore, you have to edit the recorded macro by opening Visual Basic. And that’s when it gets more difficult. But for now we want to concentrate on how to record a VBA macro.
Please note that you have to save your file as a macro enabled file in order to save your recorded VBA macro as well. Therefore press F12 (‘Save as’) and select ‘Excel Macro-Enabled Workbook’ as the file type.
Example: How to edit a recorded VBA macro
Let’s assume the following situation: You prepared an Excel sheet. Now you want to copy it and change one value. How do you start?
- Press “Record Macro” on the Developer ribbon. Confirm with OK.
- Do the action. In your case: Copy the worksheet.
- Therefore, right click on the worksheet name on the bottom of the screen.
- Click on “Move or Copy”. Set the tick at “Create a copy” and press OK.
- Change the value in cellB5
- Press the square stop icon at the bottom left corner of the screen or alternatively click on “Stop recording” on the Developer ribbon.
- Open the recorded code by clicking on the Visual Basic button on the left hand side of the Developer ribbon.
The recorded code should look similar like this.
Sub Macro1() ' ' Macro1 Macro ' ' Sheets("VBA").Select Sheets("VBA").Copy After:=Sheets(28) ActiveCell.FormulaR1C1 = "1/5/2017" Range("B6").Select End Sub
Let’s first explore the code step by step. Therefore, we’ve added comments to the code below:
Sub Macro1() 'This is the name you entered when the recording started. ' Comments - starting with the abostroph - won't be recarded as code. ' Macro1 Macro ' ' Sheets("VBA").Select 'The worksheet "VBA" will be selected. You can of course change "VBA" to any other worksheet name. Only condition: The worksheet should exist in your Excel workbook. 'This line can also be deleted when you later on edit the code. Sheets("VBA").Copy After:=Sheets(28) 'This line copies the worksheet "VBA" and puts it after the 28th worksheet in the current workbook. ActiveCell.FormulaR1C1 = "1/5/2017" 'The selected cell (in our case it's cell B5) will be overwritten with by the date "1/5/2017". Range("B6").Select 'The cell B6 is selected. This line can also be deleted. End Sub
Now the easiest method is to copy this code and adapt it to your needs. In total, we want to create copies for each date given in our table. (Please note: advanced users would use loops; in order to keep it simple we copy and paste the code above several times).
Sub Macro1() ' ' Macro1 Macro ' ' Sheets("VBA").Select Sheets("VBA").Copy After:=Sheets(28) ActiveCell.FormulaR1C1 = "1/2/2017" Range("B6").Select Sheets("VBA").Select Sheets("VBA").Copy After:=Sheets(28) ActiveCell.FormulaR1C1 = "1/3/2017" Range("B6").Select Sheets("VBA").Select Sheets("VBA").Copy After:=Sheets(28) ActiveCell.FormulaR1C1 = "1/4/2017" Range("B6").Select Sheets("VBA").Select Sheets("VBA").Copy After:=Sheets(28) ActiveCell.FormulaR1C1 = "1/5/2017" Range("B6").Select Sheets("VBA").Select Sheets("VBA").Copy After:=Sheets(28) ActiveCell.FormulaR1C1 = "1/6/2017" Range("B6").Select Sheets("VBA").Select Sheets("VBA").Copy After:=Sheets(28) ActiveCell.FormulaR1C1 = "1/7/2017" Range("B6").Select End Sub
How to run your VBA macro
When you’re done modifying your macro, you can probably want to run it. Advice: Save your Excel file first. In case something goes wrong you can easily restore it.
There are two ways of running your VBA macro:
- In the Visual Basic editor: Place the cursor within the code you want to run. Click the small play button on the top.
- A more user friendly way would be adding a button for starting the macro: For adding a button, click on “Insert” on the Developer ribbon and choose the button on the top left corner under Form Controls. Excel then asks you which macro to assign to the button. Select your recorded VBA macro and confirm with OK.
VBA macros are very powerful. Almost everything can be done with them. Also our Excel add-in “Professor Excel Tools” is programmed with VBA macros. If you want to practice or learn a little bit more, we recommend our other articles:
- Merge Excel Files: How to Combine Workbooks into One File
- Hyperlink: 3 Ways of How to Extract the Link from an Excel Cell
- How to Return the Indentation of a Cell in Excel
- MINIF & MAXIF: 5 Ways to Get a Conditional Minimum Value
- Table of Contents in Excel: 3 Ways to Create a Directory
- How to Unhide All Hidden and Very Hidden Worksheets in Excel at Once
- Print Excel Sheets: No More Trouble When Printing
- How to Get the Name of an Excel Worksheet