Hello, Welcome if you are new to the How To Excel At Excel Blog. Welcome back if you are a regular visitor as I know many of you are. :-). Today let’s look at how to view and make alterations or edit the VBA code that is automatically generated when we record an Excel macro. This can make the macro more flexible and adaptable to your requirements.
What Happens When I Record An Excel Macro?
Excel is really good at following instructions, and when you record a macro it takes down everything you do in those steps you record. I mean everything. You know how when you hear yourself back in a recording, you think you sound a little different to what you think. It is exactly the same with the macro recorder. So, if you record an action that includes a dialog box then Excel records all of the options in that dialog box, not just ones you choose to change. For example, if you copy a cell that contains a formula, then paste special values into another cell Excel records a lot more than the options you choose. Even if you did not use any of the Operation Values, Skip Blanks or Transpose options that are available.
Watch What Happens When You Record An Excel Macro!.
If you are curious about what is being recorded, it is easy to see exactly what Excel is doing. In fact is it a really good way to get a feel for macro and VBA. Let’s take the example above and create a macro which will copy the contents of cell A1 which contains a formula, and use Paste Special to paste only the values into cell A2 of the same worksheet.
Setting Up Excel To See What Excel Is Recording.
In order to see exactly what Excel is recording, we need to set up both the VBA Editor screen and the Excel worksheet you are recording your macro on. First, we need to open up the VBA Editor by using one of the following methods.
- Hit ALT+F11
- Developer Tab | Code Group | Visual Basic
If you do not see the Developer Tab then you may need to enable it. It is really simple. Just follow the instructions below.
In Excel 2010 onwards
- Click on the File tab of the ribbon to open the file menu
- On Options in the menu to open the Excel Options dialog box.
- Click on the Customize Ribbon option in the left-hand window to view the available options in the right-hand window of the dialog box.
- Under the Main Tabs section of the Options window tick the Developer option.
- Click OK.
- The Developer tab should now be visible in the ribbon in your Excel window
Or you can check out my YouTube Video for instructions alternatively.
You now have the VBA editor open as well as your worksheet you are working on. Split your screen so you can see both windows. A bit like my screenshot below. I have split mine horizontally so I can see the full width of the editor screen.
I have inserted a new module into my Personal Macro Workbook as this is where I want to save this new recording. In this way, the macro will be available to use whenever I open Excel rather than being only available in the one workbook. If you want to read more about the Personal Macro Workbook feel free to check the blog posts that I have published on this subject.
- Create A Shortcut To Your Personal Macro Workbook
- Why Is My Personal Macro Workbook Not Loading Automatically?
- How To Delete Your Personal Macro Workbook
Right, it is time to watch what happens when we record our macro. There are two places that you can start recording:-
- In the Developer Tab | Code Group | Record macro
- Use The Record button on the bottom left-hand side of the Excel Window
- Hit Record.
- You will be prompted to name your macro
- Hit Ok
You can see that much more information is recorded than you think. You did not choose to submit any of the extra information. But Excel is very thorough and takes all of the options of the Paste Special dialog box into account. So now you have seen the code being recorded when you create a macro, you can feel free to edit the code to make it less chunky and get rid of unnecessary lines that you do not need.
In this example, I can delete the following pieces of code without affecting the macro functionality.(highlighted in blue).
The code runs without issue without the extra lines.
Have you tried to view what code is generated when you record macros? Share in the comments below if you find this method helpful in learning VBA.
What Next? Want More Tips?. Then Read On…
If you want more awesome tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
How To Excel At Excel – Macro Mondays Blog Posts.
