Record a simple macro in Excel 2007. In Episode 809, we take a look at a simple macro that the macro recorder can reliably record. The trick is that you never move the cell pointer during the recording of the macro.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, I want to talk about the macro recorder, and today we're going to go look at a macro recording example that works perfectly, and tomorrow we'll take a look at why most recorded macros don't work.
So, let's say that I, you know, need to format cells, and, so, what I'm going to do is I'm going to turn on the macro recorder.
So, we'll go to RECORD MACRO and I have to give the macro a name.
I'm going to call this SPECIAL FORMAT.
SHORTCUT KEY.
Well, most of the shortcut keys are already taken up.
The only ones that are left are J and K, although you might use the shifted shortcut keys, so maybe CONTROL+SHIFT+F for format, and then where are we going to store the macro?
If we store it in this workbook, the macro is only available to this workbook or when this workbook is open.
If this is a macro that we have to use across several workbooks, then that's something we want to store in the PERSONAL MACRO WORKBOOK, and you might add a little DESCRIPTION here.
THIS MACRO DOES THE AUDIT-FLAG FORMAT ON THE SELECTION.
Click OK.
Now, it's really important that while we're recording this macro, today's macro, we're not going to change the location of the cell pointer at all.
When you're not changing the location of the cell pointer, the macro recorder tends to get it right.
So, I'm going to go to the HOME tab and I'm going to apply the usual formatting.
Maybe it needs to be BOLD, ITALICS.
We change the background a little bit, change the font color, and maybe even some, you know, fancy things with the borders.
I'll go into MORE BORDERS and choose a certain border style, a border color, apply that to the top and bottom.
Click OK.
Alright, so, I've applied some formatting there.
Fairly tedious stuff, you know, if I had to do all that from scratch every time, and now we want to stop recording.
Well, there's a couple of places to go.
If you're an Excel 2003, you're going to look for the stop recording toolbar.
It's floating somewhere above your screen, or, in Excel 2007, down here in the lower left-hand corner, there is always a button to stop recording, or you can come back to the DEVELOPER tab and click STOP RECORDING there.
So, several different places.
In Excel 2003, if the toolbar gets closed, you have to go to TOOLS, MACRO, STOP RECORDING.
So, we'll stop recording and now we'll do a little test.
We'll just select another cell and press CONTROL+SHIFT+F and, sure enough, it does all of that formatting.
So, it looks like the macro recorder has a lot of promise.
Now, stop back tomorrow and we'll take a look at a slightly more complicated example that doesn't work but I'll show you how to work around it.
Well, hey.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, I want to talk about the macro recorder, and today we're going to go look at a macro recording example that works perfectly, and tomorrow we'll take a look at why most recorded macros don't work.
So, let's say that I, you know, need to format cells, and, so, what I'm going to do is I'm going to turn on the macro recorder.
So, we'll go to RECORD MACRO and I have to give the macro a name.
I'm going to call this SPECIAL FORMAT.
SHORTCUT KEY.
Well, most of the shortcut keys are already taken up.
The only ones that are left are J and K, although you might use the shifted shortcut keys, so maybe CONTROL+SHIFT+F for format, and then where are we going to store the macro?
If we store it in this workbook, the macro is only available to this workbook or when this workbook is open.
If this is a macro that we have to use across several workbooks, then that's something we want to store in the PERSONAL MACRO WORKBOOK, and you might add a little DESCRIPTION here.
THIS MACRO DOES THE AUDIT-FLAG FORMAT ON THE SELECTION.
Click OK.
Now, it's really important that while we're recording this macro, today's macro, we're not going to change the location of the cell pointer at all.
When you're not changing the location of the cell pointer, the macro recorder tends to get it right.
So, I'm going to go to the HOME tab and I'm going to apply the usual formatting.
Maybe it needs to be BOLD, ITALICS.
We change the background a little bit, change the font color, and maybe even some, you know, fancy things with the borders.
I'll go into MORE BORDERS and choose a certain border style, a border color, apply that to the top and bottom.
Click OK.
Alright, so, I've applied some formatting there.
Fairly tedious stuff, you know, if I had to do all that from scratch every time, and now we want to stop recording.
Well, there's a couple of places to go.
If you're an Excel 2003, you're going to look for the stop recording toolbar.
It's floating somewhere above your screen, or, in Excel 2007, down here in the lower left-hand corner, there is always a button to stop recording, or you can come back to the DEVELOPER tab and click STOP RECORDING there.
So, several different places.
In Excel 2003, if the toolbar gets closed, you have to go to TOOLS, MACRO, STOP RECORDING.
So, we'll stop recording and now we'll do a little test.
We'll just select another cell and press CONTROL+SHIFT+F and, sure enough, it does all of that formatting.
So, it looks like the macro recorder has a lot of promise.
Now, stop back tomorrow and we'll take a look at a slightly more complicated example that doesn't work but I'll show you how to work around it.
Well, hey.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
