The Macro Recorder often returns Code that works today...but may not work every day. Although Pete sent in a question about using a Loop, Bill had to comment on Code designed to select an entire Table. With a few quick changes, Bill's new Code shortens 7 lines of the Recorded Code into 1 line - but it will only work if the 'Shape' of the Data is matches the imagine that Bill has in his head. Today, in Episode #1656, Bill presents: 'A Long Story About What Can Go Wrong with Recorded Code'.
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1656.
VBA Selecting the Whole Range.
Hey! Want to thank Pete for setting a question in, today.
He had watched episode 1481, about taking a macro and wrapping it in a forward next loop and Pete was asking some questions about that and you know, so I answered Pete's questions but then something that he wasn't even asking about just kind of got worried me in his code, and so I want to talk about recorded code designed to select all cells in a table.
First just a little bit about Pete's code.
Pete is running a query and he has a list of items on sheet 1.
He wants to run the query for each of those items.
Okay! So, I said, hey! First thing because we're dealing with two worksheets here that's more complicated than 1481 and I'm always worried when I have multiple sheets that I'm going to do something in the macro that's going to switch the active sheet to something I didn't expect.
The sheet dot add, that's going to you know, switch to the other sheet and so let's just be overly cautious and create three object variables one for your sheet 1.
I'll call it WS1, 1 for your data sheet.
I'll call that WSD and then one for the new worksheets that you're creating, I'll call that WSN.
For the two that are hard-coded, let's say we just assigned to sheet 1 and then data.
WSN is defined as we go.
We're doing sheets dot add down here, I add a parenthesis there, and a parentheses there and said set WSN equal to...
Alright, and then when we're referring to things like we're getting the next value from sheet1.
I can say WS1 dot cells, I, comma ,1.
All right! That way I always know, I'm going back to sheet 1 even if the the new sheet happens to be activated because of the paste or because of the sheets dot add.
All right! So, that was my first thing but then I noticed this code, that the macro recorder had done it.
It looks like Pete has a table, all right! Because they're using this table nomenclature here and starting from a column called file or file name, and then it's starting from that cell and doing a selection dot and down.
So, in other words pressing [ ctrl down arrow ] and then it's selecting from that selection, which I imagined to be an entire column doing selection dot end, right arrow.
All right! So, I can picture his getting a whole big block of text there, doing a copy and then you know, adding a sheet and pasting.
I started to think about this, alright!
So, here's I don't know, what Pete's data looks like.
All right! So, that's my first problem, but I can picture it probably looks something like this and so let's just do with a macro did.
We start, we select the heading called file name, [ Ctrl shift down arrow ], [ Ctrl shift right arrow ], and that gets the whole range.
That's great, but depending on this data that's returned by the query.
You know, we don't, we didn't create that data, and you know, you never know.
What kind of...
You know, system it was maybe it allows blanks.
So, here we have a different item and there's some blanks.
I do the exact same code [ control shift down arrow ], [ control shift right arrow ], and I'm not getting the whole range.
All right! So, that makes me really, really nervous and I wanted to say to Pete, I said, "Pete, it would be a lot better if you could come here and it from A5 and do [ control asterisk(*) ]." [ Control asterisk ], selects the current region.
All right! And so, I just wanted to change Pete's code, right.
He's not asking me about that part of the code but I desperately wanted to take Pete's code and put [ control asterisk ] in there, which is called dot current region.
But then I started to have all kinds of misgivings because I can't see what Pete's data looks like, you know maybe Pete has some really nice instructions up here or something like that and maybe although, I would always leave a blank row above my table.
Maybe Pete didn't and so then if Pete comes to the word filename and does [ control asterisk ], his macro is now gonna be copying stuff that he didn't want it to copy.
Or you know, hey! In this table nomenclature, maybe column A isn't file name, maybe column D is file name and Pete wanted to start from D5 and do [ control shift down arrow ], [ control shift right arrow ].
You know, if that's the case, now my [ control asterisk ], is going to be causing problems, all right!
So, Pete! Here we go.
Assuming that you can have a blank row above the data and that file name is the first column of your table.
I would like to change the macro like this.
We're going to just do WSD dot range, A5 and I'm going to hard code that and you're going to have to figure out where your first top-left corner cell starts.
Dot current region alright, and that's going to extend in all directions, so it hits the blank row, blank column.
A single blank cell here and there though, will not cause the problem, alright!
So, just doing that bit right there is very similar to all of those lines of code and then you're getting just the visible cells, so in other words the results of the filter.
Although in Pete's case, he's actually doing a query that's returning just the right bit.
So, he wouldn't have to add this and then we can do a dot copy.
Alright! So, all of that one line of code actually replaces these four lines of code, boy!
And I really wanted to click [ delete ], right there but I'm just going to comment them out, for right now and then Pete adds a sheet using sheets dot add but here I want to use my version instead, that actually assigns it to an object variable.
So, I'm going to copy that line of code, [ control X ] and do the new sheet before I do the copy because you're allowed to do the copy and paste in one line by specifying the destination and so that's going to be WSN and the sheet just created, range A1, alright! So, there's no copy and paste.
Now, that one line of code also replaces these two lines of code.
So, there we go and then also added a section down here that made the new worksheet, have a name equal to this value from sheet 1.
Alright! Although, in case the value from sheet 1 has an illegal character like, an apostrophe or something you can't put in a sheet name.
I just said, hey! if that's gonna throw an error and stop the macro, then just go on to the next one.
That's okay! We'll let that one be called sheet 8,7 instead of you know, stopping the macro.
Alright! So, let's just take a quick look back here.
We have our list of items to process, you know, it might be hundreds of items who knows.
Here's our table, we have two worksheets right now and we'll run the macro.
BAM! Right there, I saw new things get inserted over in the Project Explorer.
All right! And there is our apple with just the apples, banana, cherry, dill and eggplant.
So, you know, kudos to Pete.
This is going to be a great little macro here, wrapping in a loop being able run all that.
It's like when you take your car to the mechanic and say hey!
I have a problem over here and the mechanic says hey! I have also a problem over here.
So, you know, sorry to stick my nose in this but I'm worried that you know, some days this isn't going to work in, we can actually just shorten it up with one line of code Down here and have a nice you know, a quick little macro to solve the problem, all right!
I want to thank Pete for sending that question in.
I wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1656.
VBA Selecting the Whole Range.
Hey! Want to thank Pete for setting a question in, today.
He had watched episode 1481, about taking a macro and wrapping it in a forward next loop and Pete was asking some questions about that and you know, so I answered Pete's questions but then something that he wasn't even asking about just kind of got worried me in his code, and so I want to talk about recorded code designed to select all cells in a table.
First just a little bit about Pete's code.
Pete is running a query and he has a list of items on sheet 1.
He wants to run the query for each of those items.
Okay! So, I said, hey! First thing because we're dealing with two worksheets here that's more complicated than 1481 and I'm always worried when I have multiple sheets that I'm going to do something in the macro that's going to switch the active sheet to something I didn't expect.
The sheet dot add, that's going to you know, switch to the other sheet and so let's just be overly cautious and create three object variables one for your sheet 1.
I'll call it WS1, 1 for your data sheet.
I'll call that WSD and then one for the new worksheets that you're creating, I'll call that WSN.
For the two that are hard-coded, let's say we just assigned to sheet 1 and then data.
WSN is defined as we go.
We're doing sheets dot add down here, I add a parenthesis there, and a parentheses there and said set WSN equal to...
Alright, and then when we're referring to things like we're getting the next value from sheet1.
I can say WS1 dot cells, I, comma ,1.
All right! That way I always know, I'm going back to sheet 1 even if the the new sheet happens to be activated because of the paste or because of the sheets dot add.
All right! So, that was my first thing but then I noticed this code, that the macro recorder had done it.
It looks like Pete has a table, all right! Because they're using this table nomenclature here and starting from a column called file or file name, and then it's starting from that cell and doing a selection dot and down.
So, in other words pressing [ ctrl down arrow ] and then it's selecting from that selection, which I imagined to be an entire column doing selection dot end, right arrow.
All right! So, I can picture his getting a whole big block of text there, doing a copy and then you know, adding a sheet and pasting.
I started to think about this, alright!
So, here's I don't know, what Pete's data looks like.
All right! So, that's my first problem, but I can picture it probably looks something like this and so let's just do with a macro did.
We start, we select the heading called file name, [ Ctrl shift down arrow ], [ Ctrl shift right arrow ], and that gets the whole range.
That's great, but depending on this data that's returned by the query.
You know, we don't, we didn't create that data, and you know, you never know.
What kind of...
You know, system it was maybe it allows blanks.
So, here we have a different item and there's some blanks.
I do the exact same code [ control shift down arrow ], [ control shift right arrow ], and I'm not getting the whole range.
All right! So, that makes me really, really nervous and I wanted to say to Pete, I said, "Pete, it would be a lot better if you could come here and it from A5 and do [ control asterisk(*) ]." [ Control asterisk ], selects the current region.
All right! And so, I just wanted to change Pete's code, right.
He's not asking me about that part of the code but I desperately wanted to take Pete's code and put [ control asterisk ] in there, which is called dot current region.
But then I started to have all kinds of misgivings because I can't see what Pete's data looks like, you know maybe Pete has some really nice instructions up here or something like that and maybe although, I would always leave a blank row above my table.
Maybe Pete didn't and so then if Pete comes to the word filename and does [ control asterisk ], his macro is now gonna be copying stuff that he didn't want it to copy.
Or you know, hey! In this table nomenclature, maybe column A isn't file name, maybe column D is file name and Pete wanted to start from D5 and do [ control shift down arrow ], [ control shift right arrow ].
You know, if that's the case, now my [ control asterisk ], is going to be causing problems, all right!
So, Pete! Here we go.
Assuming that you can have a blank row above the data and that file name is the first column of your table.
I would like to change the macro like this.
We're going to just do WSD dot range, A5 and I'm going to hard code that and you're going to have to figure out where your first top-left corner cell starts.
Dot current region alright, and that's going to extend in all directions, so it hits the blank row, blank column.
A single blank cell here and there though, will not cause the problem, alright!
So, just doing that bit right there is very similar to all of those lines of code and then you're getting just the visible cells, so in other words the results of the filter.
Although in Pete's case, he's actually doing a query that's returning just the right bit.
So, he wouldn't have to add this and then we can do a dot copy.
Alright! So, all of that one line of code actually replaces these four lines of code, boy!
And I really wanted to click [ delete ], right there but I'm just going to comment them out, for right now and then Pete adds a sheet using sheets dot add but here I want to use my version instead, that actually assigns it to an object variable.
So, I'm going to copy that line of code, [ control X ] and do the new sheet before I do the copy because you're allowed to do the copy and paste in one line by specifying the destination and so that's going to be WSN and the sheet just created, range A1, alright! So, there's no copy and paste.
Now, that one line of code also replaces these two lines of code.
So, there we go and then also added a section down here that made the new worksheet, have a name equal to this value from sheet 1.
Alright! Although, in case the value from sheet 1 has an illegal character like, an apostrophe or something you can't put in a sheet name.
I just said, hey! if that's gonna throw an error and stop the macro, then just go on to the next one.
That's okay! We'll let that one be called sheet 8,7 instead of you know, stopping the macro.
Alright! So, let's just take a quick look back here.
We have our list of items to process, you know, it might be hundreds of items who knows.
Here's our table, we have two worksheets right now and we'll run the macro.
BAM! Right there, I saw new things get inserted over in the Project Explorer.
All right! And there is our apple with just the apples, banana, cherry, dill and eggplant.
So, you know, kudos to Pete.
This is going to be a great little macro here, wrapping in a loop being able run all that.
It's like when you take your car to the mechanic and say hey!
I have a problem over here and the mechanic says hey! I have also a problem over here.
So, you know, sorry to stick my nose in this but I'm worried that you know, some days this isn't going to work in, we can actually just shorten it up with one line of code Down here and have a nice you know, a quick little macro to solve the problem, all right!
I want to thank Pete for sending that question in.
I wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
