The macro recorder uses a confusing way to name ranges. There is a much simpler way, as podcast 626 shows.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, I'm gonna do a little bit of VBA and this tip is actually sent along by John.
John was at our power analyst boot camp, in Chicago and just sent me a note last week with this tip.
He has gone to Excel 2007 and found something very annoying, and I just want to point that out.
But along the way, I'm going to show you a cool thing in Excel if we were setting up a named range.
So, let's say that we have this cell in Excel and we would want to set up a name range.
I'm gonna turn on the macro recorder.
Tools, macro, record new macro.
And I'll click [ OK ] to create a named range the fastest way is just to go to the name box and type a new name.
So, we'll call this, let's say revenue and press [ Enter ].
Now, I'll stop recording and we'll go take a look at that macro, to see how the macro recorder recorded that code,.
So I'll click Edit and it uses this code actually workbook dot names dot add.
Name is revenue refers to, in R1 C1 style, which I know drives a lot of people crazy.
Row1, column2.
Well that's how the macro recorder does it but it really is a little long way around.
The much faster way to go is just to say, hey!
We're going to take range cell B1 Dot name equals revenue.
I'm so just using the dot name property is a much faster way to create a name.
We can actually get rid of this code here and when we run our macro.
Tell you what let me change it to a different cell, just to prove that it's working.
So, we'll go to C3, When we run this macro, very quickly we've made C3, be the name Revenue.
Now, here's the tip from John.
John likes to use named ranges, and he uses short name ranges for example ROI 2007 or Tax 82.
When you use names like that.
That causes a real problem when you go to Excel 2007 because in Excel 2007 with 16,000 columns, we now have column names that go out from AA to ZZ and then start over at AAA out to XFD.
So, a lot of those names that used to work in Excel 2003 or in 2007, is now a cell address and when you try and assign that name in Excel 2007 VBA, it fails.
So, John had all of this code.
That was working in Excel 2003, but because he used three digit names followed by a number.
He ran into all sorts of problems and all sorts of re-coding.
So, even if you're still sitting here in Excel 2003, think ahead and make sure that you don't use three letter range names because eventually someday you're going to upgrade to the new Excel whether it's Excel 2007, or Excel 14 or 15 or 16 and at that point those three digit range names will no longer work.
So, let's make your code a bit more flexible now, to save you a lot of hassle in the future.
Hey! thanks for stopping by and thanks to John for sending in that tip.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, I'm gonna do a little bit of VBA and this tip is actually sent along by John.
John was at our power analyst boot camp, in Chicago and just sent me a note last week with this tip.
He has gone to Excel 2007 and found something very annoying, and I just want to point that out.
But along the way, I'm going to show you a cool thing in Excel if we were setting up a named range.
So, let's say that we have this cell in Excel and we would want to set up a name range.
I'm gonna turn on the macro recorder.
Tools, macro, record new macro.
And I'll click [ OK ] to create a named range the fastest way is just to go to the name box and type a new name.
So, we'll call this, let's say revenue and press [ Enter ].
Now, I'll stop recording and we'll go take a look at that macro, to see how the macro recorder recorded that code,.
So I'll click Edit and it uses this code actually workbook dot names dot add.
Name is revenue refers to, in R1 C1 style, which I know drives a lot of people crazy.
Row1, column2.
Well that's how the macro recorder does it but it really is a little long way around.
The much faster way to go is just to say, hey!
We're going to take range cell B1 Dot name equals revenue.
I'm so just using the dot name property is a much faster way to create a name.
We can actually get rid of this code here and when we run our macro.
Tell you what let me change it to a different cell, just to prove that it's working.
So, we'll go to C3, When we run this macro, very quickly we've made C3, be the name Revenue.
Now, here's the tip from John.
John likes to use named ranges, and he uses short name ranges for example ROI 2007 or Tax 82.
When you use names like that.
That causes a real problem when you go to Excel 2007 because in Excel 2007 with 16,000 columns, we now have column names that go out from AA to ZZ and then start over at AAA out to XFD.
So, a lot of those names that used to work in Excel 2003 or in 2007, is now a cell address and when you try and assign that name in Excel 2007 VBA, it fails.
So, John had all of this code.
That was working in Excel 2003, but because he used three digit names followed by a number.
He ran into all sorts of problems and all sorts of re-coding.
So, even if you're still sitting here in Excel 2003, think ahead and make sure that you don't use three letter range names because eventually someday you're going to upgrade to the new Excel whether it's Excel 2007, or Excel 14 or 15 or 16 and at that point those three digit range names will no longer work.
So, let's make your code a bit more flexible now, to save you a lot of hassle in the future.
Hey! thanks for stopping by and thanks to John for sending in that tip.
We'll see you next time for another netcast from MrExcel.
