1 00:00:00,630 --> 00:00:06,060 In this video we'll be covering how to declare variables in VBA as well as the different methods of 2 00:00:06,060 --> 00:00:09,870 declaring variables and the different declaration types. 3 00:00:09,870 --> 00:00:15,510 So the first thing we'll do is go over to our develop a tab and open up the VBA window by clicking on 4 00:00:15,510 --> 00:00:19,910 Visual Basic under the code section. 5 00:00:19,910 --> 00:00:25,100 Next we're going to go ahead and insert a module by clicking the insert dropdown and selecting module 6 00:00:26,650 --> 00:00:28,960 and then we can create a temporary subroutine. 7 00:00:28,990 --> 00:00:40,730 So we'll just say Public Sub example declarations with our open closed parentheses and hit enter the 8 00:00:40,730 --> 00:00:44,070 first thing we're going to talk about is declaration method. 9 00:00:44,240 --> 00:00:49,850 Variables can be implicitly declared or explicitly declared explicit declarations. 10 00:00:49,850 --> 00:00:54,410 Means we're going to tell the code the variable name and the variable type. 11 00:00:54,500 --> 00:00:57,230 And then we can store value into that variable. 12 00:00:57,320 --> 00:01:04,520 For example we'll type dim which means declare followed by the variable name we'll use x bar with the 13 00:01:04,520 --> 00:01:10,940 E and V capitalized and then we tell the system what type of variable this is by typing as. 14 00:01:10,980 --> 00:01:14,980 And in this case we'll say this is an integer variable. 15 00:01:15,470 --> 00:01:19,000 Press enter to go ahead and finish that declaration statement. 16 00:01:19,010 --> 00:01:24,780 So now the system knows we have a variable named x bar that we will be using to store integers. 17 00:01:24,800 --> 00:01:29,720 The system will only accept data in integer form into that particular variable. 18 00:01:29,900 --> 00:01:35,930 If we try to feed that variable any other type of data the system is going to return an error message. 19 00:01:35,930 --> 00:01:42,990 So we're gonna go ahead and set this variable type by typing X far equals 4. 20 00:01:43,060 --> 00:01:48,190 Now one thing to notice is that after we've declared variables the system will automatically update 21 00:01:48,190 --> 00:01:53,380 your letters to upper or lower case to match the variable which is a good way to check yourself and 22 00:01:53,380 --> 00:01:55,300 make sure you don't make any typos. 23 00:01:55,300 --> 00:02:01,960 So in this case we can see I typed x var all lowercase when I press enter the E and V are automatically 24 00:02:01,960 --> 00:02:09,380 updated to uppercase so I know I haven't made any typo if we were to use implicit declaration. 25 00:02:09,370 --> 00:02:12,270 However it might look something like this. 26 00:02:12,430 --> 00:02:17,220 I will type in var equals nine. 27 00:02:17,800 --> 00:02:20,970 In this case we haven't told the system we're declaring anything. 28 00:02:20,980 --> 00:02:22,560 We didn't write them. 29 00:02:22,750 --> 00:02:28,390 We also didn't tell the system what type of variable in var is we let the system determine the variable 30 00:02:28,390 --> 00:02:33,450 type by telling it we want it to store the number nine in the implicit declarations. 31 00:02:33,520 --> 00:02:39,010 The system will default to variant type variables which are sort of chameleon variables that will change 32 00:02:39,010 --> 00:02:41,150 their type to match the data. 33 00:02:41,170 --> 00:02:45,820 While this seems like a good thing at first it can actually be really inefficient and will slow down 34 00:02:45,820 --> 00:02:46,590 your code. 35 00:02:46,630 --> 00:02:53,830 So we typically want to avoid variant type variables unlike the auto case updates with explicit variables. 36 00:02:53,830 --> 00:02:59,050 If you later type the same implicit variable but the letters are different cases the system assumes 37 00:02:59,050 --> 00:03:04,840 you want to update all of that implicit variable throughout your code to be what you most recently typed 38 00:03:05,140 --> 00:03:09,280 so previous variables will change but the one you just typed won't. 39 00:03:09,280 --> 00:03:16,560 For example if we type Inbar all lowercase and change it to be something else we'll say 8 and hit enter. 40 00:03:16,990 --> 00:03:22,200 We can see all of my Inbar variables are now lowercase which is what I just typed. 41 00:03:22,270 --> 00:03:24,070 We'll go ahead and get rid of that line again. 42 00:03:26,570 --> 00:03:33,260 Because we want to avoid variant type variables it's best practice to explicitly declare your variables 43 00:03:33,410 --> 00:03:40,660 and there's actually setting for this that we can get to by going to tools and then options this checkbox 44 00:03:40,660 --> 00:03:47,350 right here require a variable declaration means the system will not allow implicit variable declaration. 45 00:03:47,350 --> 00:03:53,920 It will require you to declare all of your variables which is a good way to force yourself into explicit 46 00:03:53,920 --> 00:03:55,720 variable declaration. 47 00:03:55,750 --> 00:04:01,150 So I recommend you usually check this box but for our examples we'll leave this unchecked and click 48 00:04:01,150 --> 00:04:01,520 ok. 49 00:04:02,600 --> 00:04:07,970 Now to test these variables out we're going to create a little output on our sheet in Excel. 50 00:04:08,030 --> 00:04:27,200 I'm going to type cells two comma one dot value equals x bar and sells two comma two dot value equals 51 00:04:27,300 --> 00:04:27,890 in bar 52 00:04:32,290 --> 00:04:37,540 talking through this briefly since it's covered in more depth in a future video sells tells the system 53 00:04:37,540 --> 00:04:43,540 that we are about to refer to a specific cell in our excel file the first number represents the row 54 00:04:43,630 --> 00:04:46,020 and the second number represents the column. 55 00:04:46,030 --> 00:04:49,630 So here we're looking at second row first column and second row. 56 00:04:49,630 --> 00:04:58,030 Second column which equates to rows to cells a two and B two on our excel file to help us see which 57 00:04:58,030 --> 00:05:06,960 variables which will type explicit and implicit and now we'll know which of these cells a 2 or b 2 applies 58 00:05:06,960 --> 00:05:09,710 to which variable type that we've declared. 59 00:05:09,720 --> 00:05:16,380 Now we're gonna go ahead and test run this code by clicking on macros making sure example declarations 60 00:05:16,380 --> 00:05:19,690 is highlighted and clicking run. 61 00:05:19,740 --> 00:05:25,250 Now we can see our explicit variable came up as for and our implicit variable came up as 9. 62 00:05:25,410 --> 00:05:29,580 If we go back into our code we can double check this and we see this makes sense. 63 00:05:29,580 --> 00:05:32,500 So everything has run correctly. 64 00:05:32,530 --> 00:05:34,500 Now we're gonna try something a little different. 65 00:05:34,660 --> 00:05:37,920 We're gonna change the values that we put into each of these variables. 66 00:05:38,860 --> 00:05:45,250 So instead of storing the number four in my x bar I'm going to try to store text which we do by putting 67 00:05:45,250 --> 00:05:47,970 quote a quote. 68 00:05:47,980 --> 00:05:53,110 So now we're going to try to store the letter A into our X FA which we told the system is supposed to 69 00:05:53,110 --> 00:05:54,250 be an integer. 70 00:05:54,460 --> 00:05:56,380 So if we go ahead and try to run this 71 00:06:00,300 --> 00:06:02,820 we see we get a mismatch error. 72 00:06:02,820 --> 00:06:09,580 If we click debug it will take us to the line that Excel sees as an error and we can see it's brought 73 00:06:09,580 --> 00:06:15,400 us to this x bar equals line which means the system has recognized that this is text that we're trying 74 00:06:15,400 --> 00:06:20,980 to store in this variable but we've told the system to expect integers into this variable. 75 00:06:20,980 --> 00:06:28,740 Go ahead and click the stop button up here to end your code we're going to reset this to before to get 76 00:06:28,740 --> 00:06:34,970 rid of that error message and instead we're going to set a into our m of our variable. 77 00:06:35,460 --> 00:06:42,450 Go ahead and run the code again by clicking on macros highlighting the code and clicking run and we 78 00:06:42,450 --> 00:06:47,730 can see this is updated to show that our implicit variable correctly store the value a. 79 00:06:47,820 --> 00:06:54,030 Again this is because our implicit variable here is stored as a variant type variable which means it 80 00:06:54,030 --> 00:06:58,670 will change its type to fit the data that we try to feed it. 81 00:06:58,680 --> 00:07:03,140 So in summary there is a difference between explicit and implicit variables. 82 00:07:03,150 --> 00:07:09,280 And generally speaking it's best practice to use explicit variables when you declare variables. 83 00:07:09,360 --> 00:07:13,310 The next thing we're going to talk about is the method of declaration. 84 00:07:13,380 --> 00:07:14,540 There's two different types. 85 00:07:14,550 --> 00:07:21,120 There's global and there's local variables these different declarations we have here are both local 86 00:07:21,120 --> 00:07:27,000 declarations meaning these variables only live within this subroutine that we've created. 87 00:07:27,000 --> 00:07:37,140 If we were to create a second subroutine we'll come down here and say public sub second the system won't 88 00:07:37,140 --> 00:07:41,850 recognize X FA or Inbar if we try to put them into this second subroutine. 89 00:07:42,300 --> 00:07:54,750 As an example we'll say cells 3 1 value equals x bar and cells 3 2 dot value equals in 4. 90 00:07:54,810 --> 00:08:01,590 Now we know that we've already declared these variables up in our first subroutine and x bar is explicitly 91 00:08:01,590 --> 00:08:07,830 declared within the subroutine whereas in var is implicitly declared If we go back to our excel file 92 00:08:09,200 --> 00:08:17,690 and use macros select second and try to run this we can see nothing shows up into these cells. 93 00:08:17,690 --> 00:08:25,370 This is because if we go back to our code we see that X foreign in LA are only used in this top subroutine 94 00:08:25,430 --> 00:08:27,390 and not in the second subroutine. 95 00:08:27,470 --> 00:08:32,280 So X foreign Inbar whenever set values in our second subroutine. 96 00:08:32,280 --> 00:08:36,400 Now let's say that we want to pass these values between these subroutines. 97 00:08:36,410 --> 00:08:41,270 So if I set X far up in this first one I want it to be the same in my second one and I don't want to 98 00:08:41,270 --> 00:08:46,210 have to continue to re declare my variables and reset the variable values. 99 00:08:46,220 --> 00:08:49,870 This is a case where you would use something called a global variable. 100 00:08:50,000 --> 00:08:56,270 So we'll go up to the very top give ourselves a couple lines and we're going to declare a third new 101 00:08:56,360 --> 00:08:59,240 global variable in order to do this. 102 00:08:59,240 --> 00:09:03,090 We're going to type public instead of dead. 103 00:09:03,110 --> 00:09:12,060 We'll create a global var and we'll tell it that this global VAR is an integer. 104 00:09:12,260 --> 00:09:17,420 Now you'll note that I actually moved this declaration to be outside of either my subroutines. 105 00:09:17,420 --> 00:09:22,040 This is because I want to be able to use this global variable throughout any subroutines I have. 106 00:09:22,040 --> 00:09:25,450 Not just these two local subroutines. 107 00:09:25,520 --> 00:09:32,210 So now I've declared a global var as an integer using the word public instead of dim if I move down 108 00:09:32,210 --> 00:09:42,140 into this code and I then set a value to my global var and we'll say this equals 7. 109 00:09:42,320 --> 00:09:45,860 I'll add a new line here to show what my global war value is 110 00:09:50,880 --> 00:09:56,670 and then down into the second subroutine I'll leave these two here since they're still local variables 111 00:09:56,670 --> 00:10:02,570 and we still don't have a value for them in our second subroutine but we are going to add a third line 112 00:10:04,060 --> 00:10:08,680 and we'll set this equal to our global var OK. 113 00:10:08,760 --> 00:10:16,270 Now I'm going to go back to my excel file I'll take global into the cell so we can tell which variable 114 00:10:16,270 --> 00:10:17,410 is global. 115 00:10:17,500 --> 00:10:23,170 And now I am first going to run our example declarations again because this is where we told the system 116 00:10:23,170 --> 00:10:25,650 initially what that global variable is going to be. 117 00:10:26,110 --> 00:10:31,670 So we'll click Run and we can see my global variable has showed up here as being seven. 118 00:10:31,690 --> 00:10:39,790 Now we're going to run the second macro we created click Run and we can see again my explicit and implicit 119 00:10:39,790 --> 00:10:46,420 variables belong solely to the first macro because that's where we declared to them and we declared 120 00:10:46,420 --> 00:10:47,420 them locally. 121 00:10:47,620 --> 00:10:53,650 But our global variable which we declared publicly outside of our subroutines can be passed between 122 00:10:53,650 --> 00:10:55,960 all of our subroutines once it's been set. 123 00:10:55,960 --> 00:10:59,620 So it appeared in our second subroutine as its own value. 124 00:10:59,620 --> 00:11:05,350 So now we understand the different types and methods of declaring variables and we can move on with 125 00:11:05,350 --> 00:11:08,320 our Excel VBA coding tutorials.