And this is how we tell VBA that we're going to be using the next word as a variable. This is essentially a pocket or a bowl or a backpack or something like that. Something that's going to be able to temporarily store a bit of information. And then we need to explain for VBA, what kind of content is going to go in there in this case, I'm saying integers are going to go in there. That's all that's going to go in there. And that's going to make it easy for VBA to keep that data size and appropriate size. In this case, the integer size is rather small..
And so I like to specify for VBA that it's going to be small. In this case, I actually assigned a number of. So I've already told VBA that I'm going to create this pocket. It's called a number, and now I'm going to give it a value. So I'm saying this thing that I made called a number of this little pocket, that's going to hold the value of two for now. And then in this third, little bit here, I've actually assigned a task for this I've said, okay, I have a cell range, a five, and the value of that range..
I want to be specific, stored value. And if I run this macro here, you'll see that over here, the value of that cell has now changed. Now, of course, the formatting here needs to be changed back to something like general for me to actually see the number two, or if I change that to a date format, of course it looks a little silly, but if I wanted it to be January 2nd, 1900, I would have exactly what I wanted. All right, we're going to keep going. The next few examples are mostly for practice for you. In the next example, we're actually storing date values..
This is an example of a date variable. So we're saying this is going to be a variable called a date. And the data type is going to be a date data type. Then we actually assign a value. We say, okay, this thing that we made called a date, we're going to grab the content from . Now Iwan is located right up here, and that happens to have a date in it. And so this is appropriate. I've stored this as a date data type, and now it's going to store dates. What you don't want to do is store variables as the wrong data type. Now, there are lots of different data types for variables..
And then you have the intruder data type, which doesn't allow any decimal placeholders whatsoever. You'll see, as they're allowed to be more and more simple, they take up less and less space into you're only taking up two bytes. There are many different kinds of text data types. The first one I've listed here is called variant. Now variant doesn't actually need to be text variant can hold anything, which is why it's called variant. That's also why it's so big. So you'll want to make sure not to use variant unless you really truly.
Don't know what kind of data is going to need to be housed in their event. The most common text data type is the string data type. The size of that variable does depend on the amount of content that you're storing there. So keep that in mind. It can hold quite a bit of content, but it can also take up a bit of space if it holds a lot of content and then you have the char or care data type that handles mostly smaller sets of information. We also have the bullying and bite datatypes bullying only handles true or false, which makes it relatively small, only two bites. And then we have the smallest of them, all the byte data type, which.
Only takes up one bite and can only hold values from zero to 255. And then we have a group of miscellaneous data types here. We have the date data type that we just saw an example, a moment ago, we have object data types that can hold things like charts. And then we have the range data type, which you may have seen in an example earlier, which actually remembers ranges of cells in your work. Coming back in our document here. You'll see that if you scroll down a little bit, there is a.
Macro called string variable. This is an example where you can practice creating a string variable. I'm going to do it really quick, and then you can do it on your own. I'm going to start by declaring my variable with the dim as statement what's referred to as the dim, as course, D I M stands for declared in memory. So I'm going to name this a product as string, and that's going to let VBA know that the only kind of content that's going to be stored here is string content..
Now I'm actually going to place some data inside of my variable by saying my variable that have named a product that should hold the information that's in cell G three. So I'm going to say range T3 value that's in that range should be stored in my variable called a. And now I'm going to actually use that content. Now that it's remembered that content I'm later going to be able to use it. And let's say later on in cell B five, I'm going to go on to be using that content a product..
Posts Related:
And if I run this macro here that I've created, you'll see that it puts in the
Name of that particular product there. It just so happens that I need to make this column a little bit bigger. There we go. And now I can see the entire name. All right. So the rest of the variables that are in this worksheet are for you to practice on here's a number variable. If you scroll down, you'll see an example where you can get to your variable to increase in value. And then at the bottom, there is a macro that is designed to.Run all of the above variables. There is a little bit of lingo in here called a call station. We are going to be talking about that later on. So if you don't feel completely comfortable with it yet, don't worry about it. We're going to cover it. Go ahead and pause the video. Come on. Back when you're done, there are a couple of other things that we want to know about variables before we go and start using them in our VBA language. And here's what they are. There is a naming convention for variables, and there's certain ways to treat them..
First of all, this dim as statement is not actually necessary. However, if you don't include the dim as statement, then the data type that gets assigned to that variable is the variant data type, which as we learned earlier is pretty byte expensive. So we want to avoid that. And so that's why we tend to put the dim as statement at the beginning of all of our variable names. Now you can force people to put in the DMS statement at the beginning of all.
Of their variables in the way that we do that is with an option explicit statement at the very top of our macro code. So you can go all the way to the top of your macro code, above your sub statement and type in option explicit. And that's going to make sure that people have to declare their variables. They can't just go making any variable that they want whenever they want by saying like, this is a variable and just using it. Okay. So we want to make sure that we do have dim as statements, and we're going to use the option explicit at the top of our code in order to make sure people do that..
The other thing that we want to be aware of is that there is a naming convention for our variables. They have to start with a letter. They can't have any spaces and they need to have 255 characters or fewer. I'll give us an example. So here's an example of an inappropriately named variable. I have a space and the moment I try and execute that variable, it says you can't do that. Okay. So no spaces inside of our variable names..
Also it needs to start with a letter. If I accidentally put a number as the beginning, watch what happens. So it doesn't accept that either. And the third thing we want to be mindful of when we're naming our variables is not to name them after a existing property method, active sheet here. Now watch what happens when I hit enter it actually lets me name it that, and then I can store information inside of this variable type, for instance. And then I can use that later on. I'll say, I want to assign, sell a one as the value of what.
I've put inside that variable. And if I run this right now, It actually inserts that numerical value there. So it worked as a storage space. However, later on, if I decide to use this particular variable and I actually want to use the object that it's named after, maybe I want to rename my sheet here at the end. I want to rename my sheet, this work, and now I want to run my code. It says, oh, I can't do that. So we also don't want to name our variables after any existing.
Objects, properties, or methods. And if we stick to those guidelines, we're going to be safe with our variables. And this last example, we were able to get VBA to fetch information about a particular product and populate it on our list on the left-hand side. However, if we wanted to do that for many different products, it would start to become rather time consuming to do that within VBA and less, we started to incorporate what's called. And that would be able to populate information for multiple different products in order to do that, you're going to want to open up a file called for next..
The for next loop is going to be the file that we're working on next. There are a few different kinds of loops within VBA, but we're going to be focusing on two different kinds of the for-next loop and the do loop. We're starting with the, for next loop. Go ahead and open up this file called for next loop on the left-hand side, and then the VBA window associated with it on the right hand side. You'll see that there's already some code in here that's populated for you and you can use it to follow along with me. I'm going to create mine from scratch. Now loops do require us to create a variable..
For an X loops are the first kind of loop that we're going to be talking about. And what they do is they loop through a certain process of specific number of
Times, meaning you tell it how many times to go through this particular process. If I wanted to, I could say I want it to happen 10 times or happen a hundred times or something like that. I'm going to need to declare a variable here because eventually I'm going to want my loop to happen across all of these different rows in this green area,.On the left-hand side of our document. So I'm going to create a variable called X and I'm going to make it a integer variable because eventually it's going to be representing row numbers. And my row numbers are only ever integers. It basic for next loop starts with the force statement. And then you declare exactly what the value of your variable should be. So. For X equaling one to 10, that means that this loop is going to happen when.
X is equal to one it'll happen when X is equal to two, it'll happen when X is equal to three to four, to five, to six, and every single possibility in between. Now I've already declared that X is an integer, which means that it can only happen when it's equal to one or two or three. It cannot do 1.2, 1.3, 1.4 or anything like that. So in this case, X is only going to equal 1, 2, 3, 4, 5, 6, 7, 8, or nine or 10, meaning this loop is going to happen 10 times altogether. Now, what I want to eventually loop is the process by which I'm going to insert the.
Value of 100 into each one of these cells. And so I'm going to use the code that says cell. And I'm going to start with cell one, comma one, that's sell a one as we know it. And then I'm going to say, I want that to have a value of 100. Now, if I executed this right now, well, it would have a problem because I've started a for-loop without the end of it..
But if I were just to execute this one little statement that says this sells line right here, the value of 100, all I would do is input that value into cell a one, but I want this to repeat every single time. So when X equals one, I want it to be in row one. When X equals two, I want it to be in row two when X equals three, I want it to be in row three. And so instead of actually putting the number of the rows here, what I can do is simply say X, because then when X equals one, the row will be one as well. When X equals two, the role will be two as well..
And I have to end my four next loop with the next statement. This is my ending bookend that basically says to go back to the beginning and start the next loop for the next value of X. And if I execute this, it happens too fast for me to watch, but the value of 100 got inserted into each one of these cells. Go ahead and give that a try on your own and come on back. And the last example, I was very easily able to populate this entire column of information and exact number of times exactly as I wanted. However, if I wanted to populate the next column over, I'm going to have a little bit of difficulty because the way that my macro code is set up.
Is to automatically presume that I'm only going to operate in column one here, where it says column one, watch what happens if I change this to add. If I think maybe I can get the next column over to happen and the next column after that and the next column after that and all the way through the 10 columns. After that, when I hit play, watch, when X is equal to one, it populates a one. When X is equal to two, it only populates B2 when X is equal to three only see three, et cetera. So that's not exactly what I want to be able to do here. I want to be able to populate more columns of information right here..
I don't want to have that value that I'm looking for populated across those rows. If you want to be able to populate multiple columns or multiple rows with the same for next loop, you're going to need to create what's called a double for next loop, which is basically nesting one inside the other. It's not as complicated as it sounds. We're going to start by declaring two variables this time one for each of the four next loops that we're going to be CRE. I'm going to start creating my variables with a very specific name, like I N T C.
O L I N T for integer and C O L as the name of what it's eventually going to fill, which is the column in this case. And I'm going to say, this is an integer variable, and I'll create one more for rose. Now that I've declared my variables, I can go ahead and make my four next statement. So I'll start with my four. And I'll say when int call is equal to one through three. Okay. So I want three columns of information populated here. Okay. When, when it's equal to one through three, well then my cells, I'll.
Just start with the basics here. I want the value to say something like, wow, this word. And then of course, I'll have to end this next statement by saying next into call. However, if I were to try and add that second access onto my for-next loop, I'm going to need to add a secondary for next loop inside of the one that I already have. So I'm going to start by placing my cursor at the end of the previous four. And I'm going to make another one for int RO..
And I'll say, I want this to happen for 10 rows altogether. So one to 10. Now the trick with nesting one for next loop inside of another is that whichever one is on the inside has to be on the inside in both cases during the for statement and the next statement. So I'm going to make sure that my next statement for my row is on the inside as well. And I'll say next int. Now, of course, this is only going to work for cell a one at this point, unless.
I specify for my cells object that when intro equals one or two or three or four or five or six or seven, et cetera, that I want that to get populated. And when the column equals one or two or three, that that gets populated and here we go, let's test it out. That's pretty cool. Give it a shot yourself and come on. And now we're going to take the idea of a double for-next loop and turn it into a triple for next loop..
So the double was able to populate information down a column and across a row and create a, basically a table of information for us. But what if we wanted to do this over multiple different sheets, you could basically add a third dimension to your, for next loop and be able to do that. It's going to require the exact same principles that we learned in the double for next loop. So I'm going to declare three different variables this time, one for columns, one for rose, and one for sheets..
Then I'm going to put in the beginning of my four statements. So I'm going to start with my four statement for the sheet. And I want this to happen on this sheet and the following two sheets. This one happens to be number three in the order of all the sheets in the workbook. So I'm going to say three to five, and then I'm going to start my for next loop for the columns. And I want that to happen over three columns and then my rose,.
And then the actual code that I want to execute. I am going to need to be specific enough to say exactly which worksheet I want this to execute on, but lucky for me, my variable is going to determine that for me. And then I'll do the same code that we had last time, which is the cells using the variables. And then I'll input a value there. Uh, And I'm going to have to finish out my next statements. They are going to have to be in reverse order of the four statements, just like we saw last time..
And assuming I've spelled all my variables correctly, I should be able to press play. And then of course it happens on this sheet on the next sheet and on the sheet after that a for each loop is a for next loop, but it operates on entire collections. So in this case, we have a collection of worksheets in this workbook altogether. They make up the collection called worksheet. And so if I have something that's a collection like worksheets,.
Then I can say, I want to do this particular thing to every single thing in that particular collection, I'm going to go ahead and establish a variable justice we have before. Except I'm going to say that this is actually a worksheet here, and we will be talking about the worksheet object and the worksheet collection later on in this course. But if I wanted to use the, for each loop to do this, it would look like this for each X in the collection called worksheets. And maybe in this case, I just want to get a nice little message box to pop up. And we're going to be covering message boxes later on in this course as well..
But maybe I want it to tell me exactly what the name of that particular worksheet is. And I'll concatenate this with X, which is my variable and the name of that particular worksheet. And then I'll finish out this with my next statement. So it's finding each one of these worksheets and telling me exactly what the name is, because that's what I've asked it to do. So it's going to operate this particular for next statement for every single one of the items, every single one of the worksheets in this case, in.
The collection called worksheets. Go ahead and give that a shot and come on back. And finally, the last of the four next statements we're going to cover is the one that actually requires an exit for statement. The exit four statement is used to exit the, for next loop prior to the actual ending statement. Okay. So if you have a clause in here that would require you to exit the loop early, that's what the exit four statement is used for. Now, in this example, we're actually going to be utilizing an if then.
Statement, which is something we're going to be covering later on in the course. So just go ahead and bear with. While we check out the example of this, I'm going to declare my variable as normal. Then you use X and that's going to be an integer of course. And I'll say for X equals one to 50 for gemein equal sign. There we go. All right. And I'm going to say that I want to select range, be something and the road that's going to be determined here is going to be based on whatever my variable is at the moment..
So when it goes through this the first time I want to be on V1 when it goes through it, the second time I want to be on B2, when it goes through a third time, I want to be on B3. So I am going to use my variable to determine that. And then I'm going to select that cell and I'm going to input a particular value. If the word doesn't stay, stop off. If the word stop is not already populated in that cell. So I'm going to put a little test in here. It's basically going to say, do this, keep doing this forever, unless you actually run into a value that's already in that cell that says stop. And this is what that statement would look like..
Keep in mind, we haven't talked about the, if then statement yet. So don't worry about completely feeling comfortable with this, unless that cell says stop. In which case, exit my four statement. And that's what the exit four looks like now in the case that it doesn't say stop. I do want it to continue going. So I'm going to use an LSF statement. And again, we are going to cover this later, so don't worry about it too much right now. And if it doesn't say the word stop, then I want it to populate the word info over and over and over again..
So we're going to be saying, if it says stop, then exit the forest statement. Otherwise if it's empty, then putting the word info and we'll cover this later, but I do have to put in an end Def and then of course, like we are used to, I do need to put in my next statement to finish out my for-next next loop. And then when I go ahead and play this, the word info gets populated over and over and over again, until it runs into a cell that already has the value of stop. Go ahead and give this a shot for yourself and come on back and then we'll move on to other kinds of..
In the last video, we saw an example of a loop that was going to perform its action over and over and over again and less, some kind of a logical test was performed. And if that test equivocated to true, then the loop would stop itself prematurely. That was the exit four statement that allowed us to do that. But let's say we wanted to be able to create a loop whose sole job wasn't to do that loop a certain number of times like the four loop, but maybe what it was supposed to do is simply perform its task ad infinitum until.
Some particular criteria was met. That's what we call a dual loop. Now there are a few different kinds of do loops that we're going to be exploring. We're going to start with the do wild. This loop performs its action over and over and over again. While a particular criteria is met. Another way of phrasing. This is to say that it performs its loop over and over again, while a certain test equals true. The moment that test equals false is when the loop actually stops itself..
So if you open the file called dual loop on the very first sheet there, which is called the do while loop open up your visual basic for applications interface, and you'll see on the right-hand side, that there are already some macros in here that are designed for you to be able to follow along. I'm going to remove the code within my macro so that we can go ahead and do it together. In this first example, what we're going to be doing is asking visual basic to perform a particular task, a certain number of times. We're going to be asking it to perform this particular task while X is less than 10..
In this case, we're going to be establishing a variable, just like we did with the, for next loop. So I'm going to say dim X as integer, and this is a do while loop. So I'm going to say do while X is less than a value of 10. Now I'm going to be using cells again to refer to each cell reference. And I'm going to be saying while the row is a numerical value of one or two or three or anything less than 10, I want it to select that particular row and stay in column one and put a value in there. Uh, let's say, cool..
Here's the neat thing about the do loop. Now what I'm going to say is the new X. Equals X plus one. And with that, the value of ax is actually increasing. Now I do need to put a beginning value of X. So I'm going to go ahead and do that here at the beginning. I'm going to say X starts as a value of one, and then it goes through this loop. The last thing that it does in the middle of this loop is to add one to its own value, thereby increasing its value by one each time. And now I'm going to close out the loop by putting the word loop at the end and let's run it..
And there we go, go ahead and do that for yourself and come on back and we'll look at the Duwell not empty. And this next example, we're going to be manufacturing our own Dyl, not empty loop. So I'm going to start just as I did before by declaring a variable. And I'm going to start with the value of five, because I want my loop to actually start on row five. I'm going to start by using the do while loop. And I'm going to say, do while the cell, the value of that cell.