Working with Variables



Working with VariablesSession Number: MME106-1Session Length: 0:16:08Unusual for your macros to perform some sort of calculation. It's a normal part of what they do. These calculations are made using variables, which are the building blocks of the calculating portion of your macros. Just like different parts of a building may require different sizes of bricks. [00:30] Different portions of your macros require different types of variables. Variables are nothing but a storage space for values. If you prefer real-world analogies. I said that a little bit funny. If you prefer real world analogies, you can think of them as containers that hold information. They are called variables because their contents can vary, meaning they can be changed. If it helps think [01:00] of a, as a drinking glass, like this one, you can hold water, but you could also use it to store other items as well. For instance, you might use a glass to hold ice, or if you wanted to, you could use it to hold snack crackers just as you can change what is stored inside of a container like a drinking glass.You can change what is stored within a variable variables come in different types. They see are often called data types [01:30] so that you can properly handle different types of information because VBA provides different types of variables you can use in your programs. You must learn about them so that you can use them effectively. There are 11 data types supported by VBA, and I've put together a quick guide on those data types. You'll find it in the workbook that accompanies this session of the course, there was actually a 12th data type called decimal that is specified in the VBA documentation, [02:00] but it's not currently supported by the language. Quite honestly, it's doubtful that it ever will be supported since VBA has been around for over two decades. Now, without any change in the specifications or any change in the lack of support,I'm a brand new blank workbook open on the screen here. And what I want to do is I want to jump over to the Visual Basic Editor. So I'll just go ahead and press alt plus F 11, and then I'm going to scroll down so that you can see the [02:30] Visual Basic Editor itself. What I want to do is I want to create a brand new little module in here where we can do a little bit of testing within this particular session. So what I'm going to do is I'm going to insert a module. And now we have a code window here where we can do a little bit of work in in fact, I need to, uh, create if I could a little bit of a macro here and we'll call this a VAR examples.[03:00] And now we have the space within this macro to do a little bit of our work in order to make sure that your variable matches the type of data that you plan on storing in it, you should explicitly declare your variables near the very beginning of the procedures in which those variables are used. You do that by using the dim keyword in this manner right here, I would just type dim then space and the [03:30] name of the variable that I want to declare. And then I would use the keyword as, and give the data type right after it. And when I hit enter, you'll notice that I have a fully formed statement here that declares that in this particular procedure, I want to use a variable called I counter and I'm declaring it, that it is going to contain an [04:00] integer data type. So integers can be stored within this particular variable that I've created.The dim keyword is short for dimension, which is a programmers type language for allocating space or memory to store the value note. Again, that the dim keyword is followed by the name you want to use for the variable. Then the, as keyword ASMR, and then the data type that you want used for the variable. [04:30] In this example, uh, I counter is being declared as an integer. There's nothing special about the name that you give to a variable. And I typically choose to put the I in front of it so that I can later remember that the variable contains an integer, but it's not required by, by VBA. That's just my, uh, convention that I use within my macros that I create. Now you can pick whatever name you want [05:00] to use for any given variable. The only real rule is that you cannot use spaces in the variable name, and you should not choose a name. That is the same as one of the keywords that are used by Excel. Also, uppercase and lowercase are not significant in variable names.If you take some time to look at the 11 data types detailed in this sessions, companion workbook, you'll find that they are fairly self-explanatory. Some [05:30] data types are designed for whole numbers, others for regular numbers, and some are specialized such as those for text and true false values. A few of the data types probably require some special attention. So you can understand them fully. Specifically. We need to look at the date object and variant data types.Dates and times are both stored [06:00] in the date data type, using a serial number technique. This approach means that the portion of the number to the left of the decimal point represents the date and the portion of the number to the right of the decimal point represents the time. This approach is very similar to the way that date and time serial numbers work within an Excel spreadsheet, but there is one important difference in VBA. The range of dates that can be stored is much larger than [06:30] it is an Excel in VBA. The whole portion of a date serial number represents the number of days since January 1st, 100. Yes, the year 100, thus zero is the serial number for January 1st. 100. One is the serial number for January 2nd. 102 is for January 3rd, 100. And so on these serial numbers increment all the way through whatever huge number represents December 31st, [07:00] 99 99. That means that you can re represent a much wider range of dates and VBA than you ever could think of representing in an Excel worksheet 9,899 years.I want to look at a couple of ways here that we can declare date variables in my book, perhaps the easiest way to do that is to use a hashtags around the data itself. We'll [07:30] do that here in just a second, but what I want to do is I want to declare the variables that we're going to be using within, uh, this particular, uh, macro example here. So I'm going to create one called D my date and the number one, and it's going to be as date. And let me declare two other ones here as well.[08:00] Okay. I have my variables declared in here, and if I want to store a date within one of these, using the hashtags I talked about just a moment ago, all I would have to do is type in something like D my date one is equal to, and then I put the hashtag and I say, um, oh, I don't know, we'll say August 15th, [08:30] uh, 2020, and then put a closing hashtag at the end. Now that hashtag indicates to VBA that it surrounds a date. And then that date value is assigned to, in this case, the D my date one variable, there are also two functions that you can use in order to, uh, [09:00] create, uh, date values. The first of these is the C date function. And the way that we would do that is we would say, uh, D my date two in this case, because that's the variable we're going to use.And then we say C date. And then within that, uh, we put a string, a text string that contains the date. So I can say seven slash twenties slash [09:30] we'll say, 1969, uh, on this one, and notice that it's within quotes, which tells VBA that it's a string. And then it is surrounded in the parentheses so that it is understood to be a parameter for the see date function. And now that value is stored within the D my date to variable. Now, the second function that you can use [10:00] is, uh, known as the date serial function and the way that you use that is similar to the other function that you used just a moment ago. So we're going to say D my date three is equal to date serial.And then again, it's going to expect three parameters. Notice that as soon as I hit that left parentheses, VBA, helpfully tells me what parameters it expects. [10:30] It expects first to have the year. So we can say 1969 and then a comma the month that we want, and then another comma, and finally the day of the month. And then we put the closing parentheses in there, and now we have all three of these variables assigned to contain dates. In fact, you can see these dates. If you go down to the immediate window at the bottom of the visual basic [11:00] editor here, and I'm going to type print, uh, D my date one. And when I do, um, it shows me absolutely nothing in here, cause we're not actually running the macro, but if we were running the macro, let's say, we say message box, something like this.And we start stepping through this macro [11:30] by pressing , then what we can do, uh, each time this moves on pressing eight. This is a technique that's talked about later in the course in the section on debugging, but I'm going to pause right here, uh, and not press the FAA to continue, because what I want to do now is down into the immediate window. And this is where I want to display what is within [12:00] the variables, because the values have now been assigned to the variables. You can see what my date, one contains. I can also say, uh, print the, my date two. I can also print, uh, D my date three, and all of them contain the dates that we assigned to them. And, uh, we can see that those are three separate ways that you can [12:30] create a date, values, date, serial numbers, and store them within a variable that has been dimensioned as containing dates.Now, note that both the functions that are indicated here, the C date and the date serial function, except their parameters in different ways. And, uh, in both using the hashtags and the see date function, you have to put in dates in the [13:00] month, day, year format. Even if you're using a system where your regional dates are shown in a different format, such as those in Europe or many other areas of the world, but the date serial function requires that the parameters be in the order of year, month, and day, you'll want to get those correct when you're using these functions. And if you do get them correct, you'll be able to have the, uh, date serial [13:30] numbers just as you want to have them.The object data type is another one that may cause a bit of question objects are part and parcel of an object oriented programming language, such as visual basic for applications. They are used to access all of the pieces and parts that Excel is capable of working with in a workbook. The most likely time that you'll utilize objects is when you [14:00] want to work with an object with an Excel's object model hierarchy, you'll discover more about objects and the Excel object model in a different session of the courseFinal data type I wanted to spend some time with right now is the variant data type. This one gets its name from its ability to vary its format taking on the form of the data that it contains. It's the chameleon [14:30] of the data type world. If you store a string and a variable declared as a variant data type, the variant appears as a string data type and can be manipulated by the string operators. If you store a number to a variant, it acts like a numeric variable, and you can use the arithmetic operators in its manipulation while variants are convenient in the sense that you don't have to think too much to use them. I recommend that unless you have a specific need, that can't be solved [15:00] in any reasonable way without their use, you should avoid them. It's always better to have a thorough understanding of what your macro program is doing and why it's doing it. Using variances like painting without first scraping, sanding and priming. If everything under the new paint is okay, you can get away without any extra work. If it's not, then the new paint is soon a mess, and you are doing the job over in this session. You've discovered what [15:30] variables are, what data types are and how you declare variables in your macros. In the next session, we'll go one step further, and I'll introduce you to a way that you can always make sure that you declare the variables used in your macros. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download