Supported control of flow structures. This lesson begins with an overview of structures and bullying expressions. You'll then learn how to utilize conditional branching. And the lesson ends with learning about how to use looping constructs. If you're enjoying these videos, please like, and subscribe. If you want to earn certificates and digital badges, please become a member of our Patreon. The link is in our video description. If you have any questions you want answered by one of our instructors, please join our offsite community. The link is in the description as well as always..
If this course has exercise files, you'll find them in the video description. In our fourth lesson, we're gonna dive into expressions variables and intrinsic functions. You've already experienced comments, seen them when we fill out the description in the record macro dialogue box, and we've actually modified comments and added our own comments to code that we created from scratch and comments are known as.
A foundational VBA language element. Well, there are others and we're gonna get into them in this lesson. So we're gonna get started by learning and understanding variables. We'll go over VBA data types and intrinsic functions, which include expressions. We saw the width and with structure in the first macro, we recorded the ad formatting macro. It created that structure with everything that resides in the paragraph group.
On the home tab of the Excel ribbon. And we deleted a lot of extraneous lines just to make our code more concise. You're gonna learn about the offset property using message boxes and input boxes. And object variables. We're gonna be using our regular vehicles, macro enabled file as well as two other Excel files that are in the video description. And you should be putting these files all in the same directory. So we're gonna be using an Excel file named numbers and another one called sales.
While your procedure executes. There are of course naming rules and conventions. When it comes to variables, very similar to the rules and conventions. While the rules for naming procedures, variable names can be up to 255 characters, alpha numeric. The first character must be a letter, cannot use spaces or any of the characters listed on the slide. And then it has another one that is not a procedure rule. VBA is not case sensitive, but it is case preservative..
So you'll learn about declaring variables in a moment. And you'll see this play out when you declare a variable, if you declare it in proper case, right. Using the rules and conventions, and then let's say the next time you reference it, you type it in all lower case, visual, basic editor gonna automatically change it to the case. It was when you declared it. And that's what case preservative is. A convention is used proper case for each word within the name of the variable. And we have a couple of examples..
Today's date then numb, before you can use variables, they need to be declared and just like procedures variables also have a scope. So there are two declaration methods. There are implicit and explicit. Implicit declaration means you declared a variable by just using it. Without writing a declaration statement and this method can lead to code errors..
So for example, with implicit declaration, let's say I name a variable green, for example. And then when I go to use it, I misspell green. It's going to think that I'm implicitly declaring another variable and you can see how that could lead to coding errors. Explicit declaration is the preferred method. You write a declaration statement to officially declare a variable. Now, when it comes to variable scope, you can use either the.
Dim or the private keyword. They mean the same thing. They're used interchangeably. They have the same result. I typically use dim. And you declare a variable at the top of the module before the first procedure. And that area at the top of the module is known as the declaration section. If you want the variable available to all procedures in a module, You would use dim or private and declare it in the declaration section, the public keyword is also used in the declaration section at the top of the module..
Posts Related:
And you use it if you want the variable available to all procedures in all modules.
And then there's a static keyword, static variables retain their value, even when the procedure ends and you declare a static variable at the procedure level, it means it's only available in that procedure. And when you declare variables, you should assign them a data type..So on this slide, it's talking about the data types that are available in VBA, and you can see the name of the data type, the number of bites that are used and the data types range of values. A bite uses the data type known as a bite uses one bite and has a range of values from zero to 255. A bullying data type uses two bites and has either true or false as its range of values. You have integer long single double currency. And then at the bottom you have decimal data type. Those are your numeric data types..
They use varying bys and they have varying ranges of values. You have a date data type. I don't think you will ever be in a situation where you're not covered as concerns a date. I mean, it goes back to the year, 100 and all the way up to the year, 99, 99, you have your string data types, fixed length and variable length, and you have a variant data type. So if you declare variable and you don't assign it a data type,.
It will automatically assign it. The variant data type look how many bites that's using. If it's a value it's gonna use 16 bites. And if it's a string it's gonna use 22 plus bites. So you want to be careful and really try your hardest to assign data types. When you're declaring variables, I've gone ahead and opened the VIN numbers Excel file. That was in the video description..
And I still have my vehicle's macro enabled file open, but our focus right now is gonna be on this file. So it only has one sheet. It has one VIN number and it has several other columns year make model, color country classification. What we're going to do is insert a new module, create a sub procedure and declare six procedure level string variables. We talked about the declaration section at the top of the module window and anything you declare there, depending on the keyword that you're using, determines.
The scope of the variable and the scope of a variable, not only determines where it can be used, but also affects the circumstances in which the variable is removed from memory only procedure level variables that are declared using the dim keyword are removed from memory. When it's code has completed execution. If you use the static keyword in a variable, that's declared at the procedure level, that means it's gonna retain the value of the variable after it executes..
So you'll see what this means and how we're gonna use it. When we switch over to V B E, which we're gonna do right now by doing Al alt F 11. So I wanna point out a few things to you now, since I still, and you do too have the vehicle. File open. There's a VBA project that we've been working in this whole time for that particular file. I'm gonna collapse that project because we have another open workbook. We have another VBA project and it's VIN numbers dot XL.
SX file that we just opened. One thing I wanna point out at the very top of the screen, and I'm gonna just select like sheet one in here to get rid of the code that's on my screen. And at the very top of the screen, we have that option explicit statement. This is when it's time to learn why that's sitting there and what it means. So let's go to the tools menu and go into options like we did earlier in the course. And one of the options under code settings on the editor tab is require variable declaration..
And that way we can't do an implicit declaration. We have to do an explicit declaration. The implicit ones are where you don't write a declaration statement. You just use the variable, but that's the type that can lead to code errors. So to get the system to force you to write declaration statements, you want this setting to be checked in your options require variable declaration..
And that means that it produces and I'm gonna just cancel out of options.
It forces that option, explicit statement to show at the top. So it's gonna force you to do variable declaration. The other thing we talked about just a moment ago is that the scope of a variable determines not only where it can be used, but the circumstances in which the variable is removed from memory, you can always use two techniques to remove all variables from memory. One of them is the reset button on the VB E toolbar..So it looks like a stop button when you hover over it, it says reset. And a lot of times, if you get cold errors, once you resolve the errors, you're gonna wanna reset. Anyway, if you ever get a runtime error, there's a dialogue box that will pop up and it has an end button on it. If you click end, it will remove all variables from memory. So I've closed the immediate window as we won't be using. It has an X in its upper right hand corner. Just to give me some more working space here and what we wanna do, we're gonna take this in little steps..
Like I said, we're gonna set up the framework of a sub procedure and we're gonna declare six string variables. We are going to look up at the top of your screen, right underneath the option. Explicit statement is known as the declaration areas. That's why it says declarations over to the right, in the procedure box. We are going to first insert a new module. So we're gonna use make sure you're on your VBA project, then numbers, right?.
We wanna be on that one. We're gonna go up to insert module. So it gives us a module one, which we are currently in. And again, I always verify in a title bar up at the top, what file or what project I'm in and then what module I'm in. And this is where we're gonna create a sub procedure. We are gonna type sub and we're gonna name it parse. Then since then is all capitalized. I'm gonna leave it that way. Parse. Then I'm gonna press enter twice..
And press the tab key. So it gave me my parentheses and my nub statement. I'm gonna actually, and actually we don't have to press tab. I'm gonna shift tab. I'm gonna declare procedure level variables, but first I wanna type a comment. So I'm gonna do an apostrophe and type this procedure will parse the VIN number into the appropriate columns, and then I'm gonna press enter and enter again. So I have my comment line..
Now we're gonna use the dim keyword. So I'm gonna type di and I wanna show you two examples here. Again, you want your code to be as concise as possible. So you guys, you don't have to type or do anything other than watch my screen. Just wanna show you an example here. So hopefully this example is gonna show you how to make your code more concise. I'm gonna highlight the lines of interest here. These variables are all using the string data type, and I typed a separate line item using the D key for each item. Why would I do that?.
If they're all the same data type, I can type this line, just using the D keyword once separating the variable names by commas and at the very end giving it the as string data type. So that's the line that you're gonna end up typing, and then I'm gonna end up keeping, but I have another example. Let's say you were only declaring two variables and they had different data types. So in that case, you would end up with two lines. Dim dealer cost as single, dim than number as string..
The ones that have the same data type. I grouped them together on one line, easier to type, easier to troubleshoot. So I am going to go ahead and delete these individual rows of variable declarations, and I'm gonna delete the last two and we want to declare six procedure level string variables, and you're gonna type this D statement that I have on my screen. And you'll notice that when you get to the, as keyword and you start typing.
String, a list of data types will appear. So it's not gonna let you type in a data type that you just make up or pull from thin air that is not compatible with VBA. Now that we have our six procedure level string variables declared. And again, they're only going to be available to the procedure within which they're declared. We are gonna look at building intrinsic functions. So by definition, there are functions that are provided by the application,.
Like a function procedure, an intrinsic function performs a specific task or calculation and returns a value. You may be familiar with the three functions we're gonna use in this section because they're essentially Excel functions and there are string functions in Excel. So if you've ever used any of the Excel text functions, These may be familiar to you. We're gonna use left mid and right. So the left function extracts specified text from the left of the text string.
It's syntax is left, and then you have to tell it what string expression, and then you give it a numeric expression. Don't worry about memorizing this. I will walk you through it when we're ready to go back to visual basic editor, the mid function extracts specified text from the middle of a string. It has three arguments. Well, first you use mid, which is the name of the function. And then it has a string expression that it needs to reference a starting position in that string and the number of characters you want extracted..
And then the right function does the opposite of the left. It extracts specified text from the right of a text string. It too has the same arguments as left. So right. And then the string expression that it's referencing and a numeric expression that references how many characters are gonna be extracted. And again, don't worry about this. I will guide you through these functions. And now I wanna show you just a split screen of a slide on the right and the.
VIN numbers, Excel sheet on the left. I mentioned earlier that these are just make believe VIN numbers, but the way we're using them in this course and the way we're gonna use them in this procedure that we're developing is based on the slide. So we're saying the first character in the VIN. So in this case, the number one represents the country of origin. Honestly, I don't know if that's true or not just make believe data here, but this is how we're using it. The second character of the VIN would represent the, make the third and the fourth characters would represent the model..
The 11th character would represent the year 12th character would represent the color and the 17th character of the VIN would represent the classification. Again, I will guide you through this as we're using our left right and mid functions, but you have the slide for future reference. If you wanna kind of run through it again on your own, you'll know how we're using the VIN. Now we're ready to use our intrinsic functions in this parse then procedure. We already created a blank line underneath our variable declaration, dim statement.
In this procedure, and we're gonna press tab on the keyboard to end in, and what we're gonna type is range. And then in parentheses and double quotes, a two close, your double quotes, close the parentheses. We're gonna use our dot notation. And now you'll see the list pop up with methods, properties, bunch of different things. We're gonna start typing select. And when it shows up on the list and it's highlighted, I can stop.
Typing and press my tab key to get it in and I'm gonna press enter. So it's gonna select cell a two, and then we are gonna reference one of our variables, but we're gonna reference it in all lower case. So we're just gonna type then numb and then a space bar equals and we're gonna type active cell dot. And when the list comes up, start typing value. When I see it on the list, I highlight it and tab it in. So we're saying select sell a two. And then that is the active cell. Once it's selected, we want..
The VIN number variable to be populated with whatever is in cell a two. So in Excel, you can see that the only VIN number in this file is in cell a two. So it's going to select that cell and then assign the VE num variable that VIN number, go ahead and press enter at the end of that line and notice that it adjusted the casing on VIN num remember VBA is not case.
Sensitive it's case preservative. So when we declared that variable, we did uppercase V uppercase N and then when we typed it in, we did it in all lower case. As soon as we press enter at the end of that line, it goes back and preserves its original declaration case. That is an example of case preservative. So now we're gonna type year. So we're going in the order of the way we declared the variables. That's easy to keep yourself organized. So we started with select cell two and then..
Populate the VIN num variable with the VIN number that is in cell a two. Now we're going to say what to populate the year variable with. So we type year equal, and then we're gonna type the mid intrinsic function open per so right underneath it, it's telling you the arguments that it's looking for. So you'll see that yellow banner there. Right? Right now it's waiting for the string argument and then you.
Have a numeric argument, right? And the string argument is bold because that's the one that it's waiting for in the open parenty, we're gonna type then numb the name of our variable. And again, doesn't matter how you do the casing, cuz it's case preservative then numb 11 one. So the mid function has three arguments. The string argument, the start as long argument, right? That's the starting position. And then the last argument is the number of characters..
So we're saying the year is going to equal the 11th character of the VIN. We're saying in that then numb variable. Which now holds the entire VIN number. We want you to go to the 11th character and extract one character, which is the 11th character. You're gonna type a closing Peren. So the 11th character of the VIN represents the year and press enter. Our next one that we're gonna do is the make, because we're.
Doing them in order here. So instead of you watching me continue to type, I finished this part of this procedure. So we left off with doing the year. So I'd like you to just copy from my screen and assign values based on the character or characters in the VIN to make model, color and classification. You can pause the video until you get it done. And once you have the other variable assignments done, go ahead.
And compile your VBA projects. So just be anywhere between sub and then sub make sure there's no errors, by the way, if you just watch my screen, I'm gonna take out this classification line. And I just wanna show you something. You don't have to worry about spacing, you know, the variables of case preservative. So I could type that line as classification, no space equal mid open per I'm, typing VIN numb..
In a weird mix of capitalization 17 one, and I'm not typing spaces at all. I'll type the closing parenthesis. And when I press enter, it gives me the appropriate spacing and it case preserved the name of the variable. So you don't have to worry about your spacing. There's a line editor. So when you get to the end of the line and you press enter, it will fix little stuff like that for you. So you don't have to worry about that now for the classification, since we have 17 character VINs, 17 characters, total in the van, we.
Could have used the right function. It would've just been right then numb 17, right? The last character. But you can also use mid there, same result. Now we're gonna go ahead and I'm gonna compile again, since I made a change and it's saying the variable is not defined. So I gotta compile error this time class because I misspelled classification. So it's not defined, I spelled classification and it's like, uh, Nope, sorry..
You have explicit variable declaration turned on and you did not declare that variable. So I'm gonna just do okay. correct. My typo gonna compile again and now I don't get the error. We're gonna test this at this point. So we are gonna switch over to Excel. I'm gonna use the Excel icon on a toolbar to switch over. Since I have multiple files open and we're gonna test this, we're gonna go alt F eight to open up our macros dialogue box. And we'll see the macros that are in the vehicles..
Macro enabled file that we still have open as well as this one. That's not prefaced by a file name, parse fin, because it's in the current file that we're working in. So I'm gonna just click on par. Then I double clicked it and absolutely nothing happened. I wanted to show it to you at this stage. Let's switch back over alt F 11 and switch back over to our parse VIN sub procedure. So all we've done at this point in this procedure is variables and use our intrinsic functions to assign each variable a value, but.
We haven't told it what to do with the values assigned to the variable. So nothing happened. It assigned the values to the variable, but we didn't say how to display those values. In order to tell it where to display the values of the variables on a spreadsheet. We're gonna take a look at the width end width structure and the offset property, and then we're gonna go and complete that parse. Then sub procedure compare these two code blocks. So the top one, these four lines selection dot starts at the end of each line..
It's repetitive. And then you have whatever the thing is. So selection dot horizontal alignment equals Excel center, selection dot vertical alignment equals Excel center, selection dot rap, text equals true selection dot merge cells equals false. So you're repeating the selection and the dot on every line as a compared to the width end with block of code. So you say with selection, And then you just do the dot and the thing that it is and what it equals. And at the end of it, you have an end with statement..