An explanation of how defined constants such as XLRight make life easier in VBA. Episode #1222 shows an example of the power and versatility of VBA in Microsoft Excel. ...This is the podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel VBA, chapter 2.
Define Constants.
All right! So, let's talk about one of the mysteries of Excel VBA.
I'm just going to turn on the macro recorder here.
Go to developer, will record a new macro.
I'll just leave it like this.
We'll put in some stuff and some other stuff.
And then let's do some formatting here.
I'll take that first word and align right and the second word will align center, and maybe do some vertical line top with that one.
Alright! Good enough, stop.
Let's go. Take a look at the code.
[ Alt ] [ F11 ] and working here, our project explorer, find that new model -1 that was inserted.
Alright! Here we go.
Now, let's look at this xlRight.
xlRight, xlBottom.
What the heck is this stuff, these are known as defined constants and believe it or not.
They're there to make your life easier.
In reality what should be happening...
I'm gonna come down here and say "print xlright".
What should be happening, is that you would have to write code, where you said hey! The horizontal alignment is equal to (=) negative (-) 4152.
Alright!
Think about how horrible that code would be.
So, whoever came up with VBA decided that negative -4152 is what xlRight is the...
When you write a line, you're going to use negative 4152 xlBottom.
Let's take a look at that...
that's negative -4107, who would ever remember this stuff.
Okay! So, if you happen to be in an English-speaking country.
You're going to love this because what they said was well rather than put in 4152, we're going to permanently store, in other words it's a defined constant.
The value of negative 4152 and a variable called xlRight.
So, beautiful thing is you don't have to remember 4152 because who would ever remember that you just have to remember xlRight.
Notice, I always type it lowercase and then when I go to a new line, I should see at least one character get capitalized that means that I typed it correctly.
If nothing is capitalized let's do xlwrite, which obviously is a miss-spelling.
That's not a defined constant.
So, that's how you can tell whether you've got it or not.
It's kind of an interesting way to actually make VBA, a lot easier.
Just imagine, how bad it would be if we had a great code like, vertical equals negative 4107, instead of xlBottom.
Well, there you have it.
Thank you for stopping by.
We’ll see you next time for another netcast, from MrExcel.
Excel VBA, chapter 2.
Define Constants.
All right! So, let's talk about one of the mysteries of Excel VBA.
I'm just going to turn on the macro recorder here.
Go to developer, will record a new macro.
I'll just leave it like this.
We'll put in some stuff and some other stuff.
And then let's do some formatting here.
I'll take that first word and align right and the second word will align center, and maybe do some vertical line top with that one.
Alright! Good enough, stop.
Let's go. Take a look at the code.
[ Alt ] [ F11 ] and working here, our project explorer, find that new model -1 that was inserted.
Alright! Here we go.
Now, let's look at this xlRight.
xlRight, xlBottom.
What the heck is this stuff, these are known as defined constants and believe it or not.
They're there to make your life easier.
In reality what should be happening...
I'm gonna come down here and say "print xlright".
What should be happening, is that you would have to write code, where you said hey! The horizontal alignment is equal to (=) negative (-) 4152.
Alright!
Think about how horrible that code would be.
So, whoever came up with VBA decided that negative -4152 is what xlRight is the...
When you write a line, you're going to use negative 4152 xlBottom.
Let's take a look at that...
that's negative -4107, who would ever remember this stuff.
Okay! So, if you happen to be in an English-speaking country.
You're going to love this because what they said was well rather than put in 4152, we're going to permanently store, in other words it's a defined constant.
The value of negative 4152 and a variable called xlRight.
So, beautiful thing is you don't have to remember 4152 because who would ever remember that you just have to remember xlRight.
Notice, I always type it lowercase and then when I go to a new line, I should see at least one character get capitalized that means that I typed it correctly.
If nothing is capitalized let's do xlwrite, which obviously is a miss-spelling.
That's not a defined constant.
So, that's how you can tell whether you've got it or not.
It's kind of an interesting way to actually make VBA, a lot easier.
Just imagine, how bad it would be if we had a great code like, vertical equals negative 4107, instead of xlBottom.
Well, there you have it.
Thank you for stopping by.
We’ll see you next time for another netcast, from MrExcel.
