Ever wonder how MrExcel spends his free time? He watches the macro recorder write code before his eyes. In Episode 813, learn how to arrange the VBA window and the Excel window to watch which lines of code appear in response to actions in Excel.
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, we've been talking about the macro recorder and I want to show off a cool trick here, a cool trick.
Now, if you have any questions for the netcast, of course, you can call in, leave your question as a voicemail, 866-581-0221, or just drop me a note, bill@mrexcel.com, and we can get to you on a future netcast.
I've arranged my window here so that way the Excel window is very small, and, in back, in the background, I show the VISUAL BASIC window, so that's ALT+F11 to get that.
I'm going to turn on the macro recorder.
I will just start the macro recorder, and, in VBA now, we should see that we have modules, and I can open MODULE1, and we can actually see the tiny bit of macro that's been recorded.
Now, switch back to Excel and, in this particular case, I want to rename these sheets.
So, typically, when we start with a brand new worksheet in Excel, we get SHEET1, SHEET2, SHEET3, and you can see that as I do things on the left-hand side, I just changed the application window, that got recorded as code on the right-hand side.
So, I'm going to come here to SHEET1, I'm going to double click that, and call it REVENUE, and it selected the sheet, it changed the name.
Now, over here on the left hand side, I'm going to go through and I'm going to change this to COSTS, and then, on SHEET3, I’ll call it EXPENSES, and it's pretty wild.
When you set this code up so that way you can see both the Excel window and the VBA window, you can actually watch the macro recorder write the code right before your eyes.
It’s a great way to see which lines of code got written in response to doing certain things within the Excel window.
So, if I choose a few cells here, 1, 2, 3, CONTROL+ENTER, it will do 2 things -- first of all, the select, and then it applies a formula.
Now, it uses the old R1C1 formulas here and that's a topic for another day, but it's pretty cool to see these two macros work side by side.
Well, I want to thank you for stopping by and we’ll see you next time for another netcast from MrExcel.
Well, we've been talking about the macro recorder and I want to show off a cool trick here, a cool trick.
Now, if you have any questions for the netcast, of course, you can call in, leave your question as a voicemail, 866-581-0221, or just drop me a note, bill@mrexcel.com, and we can get to you on a future netcast.
I've arranged my window here so that way the Excel window is very small, and, in back, in the background, I show the VISUAL BASIC window, so that's ALT+F11 to get that.
I'm going to turn on the macro recorder.
I will just start the macro recorder, and, in VBA now, we should see that we have modules, and I can open MODULE1, and we can actually see the tiny bit of macro that's been recorded.
Now, switch back to Excel and, in this particular case, I want to rename these sheets.
So, typically, when we start with a brand new worksheet in Excel, we get SHEET1, SHEET2, SHEET3, and you can see that as I do things on the left-hand side, I just changed the application window, that got recorded as code on the right-hand side.
So, I'm going to come here to SHEET1, I'm going to double click that, and call it REVENUE, and it selected the sheet, it changed the name.
Now, over here on the left hand side, I'm going to go through and I'm going to change this to COSTS, and then, on SHEET3, I’ll call it EXPENSES, and it's pretty wild.
When you set this code up so that way you can see both the Excel window and the VBA window, you can actually watch the macro recorder write the code right before your eyes.
It’s a great way to see which lines of code got written in response to doing certain things within the Excel window.
So, if I choose a few cells here, 1, 2, 3, CONTROL+ENTER, it will do 2 things -- first of all, the select, and then it applies a formula.
Now, it uses the old R1C1 formulas here and that's a topic for another day, but it's pretty cool to see these two macros work side by side.
Well, I want to thank you for stopping by and we’ll see you next time for another netcast from MrExcel.
