In our second Where is it Wednesday episode, we take a look at how to unlock all of the macro functionality in Excel 2007. Episode 808 shows you how.
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. Alright. Welcome back to the MrExcel netcast. I'm Bill Jelen.
That was George Wood with the Where Is It Wednesday theme.
We’ll take a look at the macro recorder.
Now, the macro recorder, back in Excel 2003, was under TOOLS, MACRO, and we had a whole series of commands here.
We could change the SECURITY, of course, before you wanted to run macros the first time.
We'd have to go into SECURITY and change it back from HIGH to at least MEDIUM, and then, other things here, we could TOOLS, MACRO, get a list of the MACROS -- that's ALT+F8 -- or record a new macro or even go straight to the VISUAL BASIC EDITOR.
This was all well and good.
We'll talk over the next week about the macro recorder and the shortfalls of the macro recorder, but when we get to Excel 2007, well, gee, it seems like every trace of the macro recorder is gone.
If we go to VIEW, out here on the right-hand side, we can get a list of our macros but, really, it doesn't seem to have the same amount of functionality that we have in Excel 2003.
So, here's the story.
They’ve really improved the whole macro recording experience.
They've just tried to simplify things by removing it from VIEW for most people.
So, what you're going to do is you're come up here to the OFFICE button, click the OFFICE button, and then go down to Excel OPTIONS at the bottom of, basically, the old file menu.
In Excel OPTIONS, it’s very easy to find.
It's just the third checkmark here, SHOW DEVELOPER TAB IN THE RIBBON.
Click OK, and we’ll now have a brand new tab to the right of you called the DEVELOPER tab.
Now, here's a quick tour of that new DEVELOPER tab.
VISUAL BASIC is the fast way to get to the visual basic editor, or ALT+F11.
MACROS is a list of all macros, or ALT+F8, and then the macro recording buttons.
RECORD MACRO.
USE RELATIVE REFERENCES.
It's really nice that the relative reference button now actually has words USE RELATIVE REFERENCES.
As we'll take a look, that button is really the key to making your macros work well, and then MACRO SECURITY.
This is one that's just a tiny bit maddening because they've changed the words around.
What used to be MEDIUM is now called DISABLE ALL MACROS WITH NOTIFICATION.
That means, hey, we're not going to let the macros run but we're going to tell them that there's macros there, kind of like the MEDIUM option used to do.
So, that's the one that we want to use.
We also, it's pretty nice, have now access to the FORM CONTROLS and the ACTIVEX CONTROLS.
Those 2 little toolbars are now right here, and of course once we've added a formula, we can get to PROPERTIES, VIEW CODE, RUN DIALOG, and then, on the right-hand side, a new XML pane.
XML has really started to be supported in 2003 so they've added it here to the DEVELOPER tab.
Now, tomorrow, we’re going to take a look at recording your first macro and it'll work, but then learn when the macro recorder doesn't work and what you can do about it.
So, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
That was George Wood with the Where Is It Wednesday theme.
We’ll take a look at the macro recorder.
Now, the macro recorder, back in Excel 2003, was under TOOLS, MACRO, and we had a whole series of commands here.
We could change the SECURITY, of course, before you wanted to run macros the first time.
We'd have to go into SECURITY and change it back from HIGH to at least MEDIUM, and then, other things here, we could TOOLS, MACRO, get a list of the MACROS -- that's ALT+F8 -- or record a new macro or even go straight to the VISUAL BASIC EDITOR.
This was all well and good.
We'll talk over the next week about the macro recorder and the shortfalls of the macro recorder, but when we get to Excel 2007, well, gee, it seems like every trace of the macro recorder is gone.
If we go to VIEW, out here on the right-hand side, we can get a list of our macros but, really, it doesn't seem to have the same amount of functionality that we have in Excel 2003.
So, here's the story.
They’ve really improved the whole macro recording experience.
They've just tried to simplify things by removing it from VIEW for most people.
So, what you're going to do is you're come up here to the OFFICE button, click the OFFICE button, and then go down to Excel OPTIONS at the bottom of, basically, the old file menu.
In Excel OPTIONS, it’s very easy to find.
It's just the third checkmark here, SHOW DEVELOPER TAB IN THE RIBBON.
Click OK, and we’ll now have a brand new tab to the right of you called the DEVELOPER tab.
Now, here's a quick tour of that new DEVELOPER tab.
VISUAL BASIC is the fast way to get to the visual basic editor, or ALT+F11.
MACROS is a list of all macros, or ALT+F8, and then the macro recording buttons.
RECORD MACRO.
USE RELATIVE REFERENCES.
It's really nice that the relative reference button now actually has words USE RELATIVE REFERENCES.
As we'll take a look, that button is really the key to making your macros work well, and then MACRO SECURITY.
This is one that's just a tiny bit maddening because they've changed the words around.
What used to be MEDIUM is now called DISABLE ALL MACROS WITH NOTIFICATION.
That means, hey, we're not going to let the macros run but we're going to tell them that there's macros there, kind of like the MEDIUM option used to do.
So, that's the one that we want to use.
We also, it's pretty nice, have now access to the FORM CONTROLS and the ACTIVEX CONTROLS.
Those 2 little toolbars are now right here, and of course once we've added a formula, we can get to PROPERTIES, VIEW CODE, RUN DIALOG, and then, on the right-hand side, a new XML pane.
XML has really started to be supported in 2003 so they've added it here to the DEVELOPER tab.
Now, tomorrow, we’re going to take a look at recording your first macro and it'll work, but then learn when the macro recorder doesn't work and what you can do about it.
So, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
