An explanation of how defined constants such as XLRight make life easier in VBA
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”!
Excel VBA chapter 2 - Defined Constants!
Alright 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 on some stuff, some other stuff, and then let's do some formatting here.
Take that first word and align right, and the second word we’ll 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 we come over here to Project Explorer, find that new Module1 that was inserted, alright, here we go.
Now let's look at this, 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 going to 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 =-4152”.
Alright, think about how horrible that code would be.
So whoever came up with VBA decided that -4152 is what xlRight is- when you write a line, you're going to use -4152.
xlBottom, let's take a look at that, that's -4107, who would ever remember this stuff?
OK, 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 -4152 in 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 misspelling, 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 me VBA a lot easier.
Just imagine how bad it would be, if we had to write code like vertical alignment =-4107 instead of xlBottom.
Well, there you have it, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
Excel VBA chapter 2 - Defined Constants!
Alright 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 on some stuff, some other stuff, and then let's do some formatting here.
Take that first word and align right, and the second word we’ll 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 we come over here to Project Explorer, find that new Module1 that was inserted, alright, here we go.
Now let's look at this, 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 going to 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 =-4152”.
Alright, think about how horrible that code would be.
So whoever came up with VBA decided that -4152 is what xlRight is- when you write a line, you're going to use -4152.
xlBottom, let's take a look at that, that's -4107, who would ever remember this stuff?
OK, 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 -4152 in 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 misspelling, 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 me VBA a lot easier.
Just imagine how bad it would be, if we had to write code like vertical alignment =-4107 instead of xlBottom.
Well, there you have it, thanks for stopping by, we’ll see you next time for another netcast from MrExcel!
