1 00:00:00,630 --> 00:00:06,720 In this video we're going to talk about how to build and interact with arrays in Visual Basic within 2 00:00:06,780 --> 00:00:07,630 Excel. 3 00:00:07,800 --> 00:00:11,900 And so the first thing we're going to want to do is go ahead and open up our visual basic window. 4 00:00:11,940 --> 00:00:18,750 So we'll go to the developer tab and click on visual basic and then we'll go ahead and we'll insert 5 00:00:18,870 --> 00:00:20,160 a module. 6 00:00:20,160 --> 00:00:25,860 Now I'm going to go ahead and create a public subroutine by typing Public Sub and we'll call this one 7 00:00:25,860 --> 00:00:27,590 static array. 8 00:00:27,810 --> 00:00:35,040 Remember not to put spaces in between your variables or subroutine names or anything like that within 9 00:00:35,040 --> 00:00:36,250 VBA. 10 00:00:36,630 --> 00:00:41,390 But the open and close parentheses at the end and then we're going to go ahead and declare our first 11 00:00:41,400 --> 00:00:42,960 right now. 12 00:00:42,960 --> 00:00:46,650 Arrays are clear are declared similarly to regular variables. 13 00:00:46,650 --> 00:00:53,100 We type dim to mean declare and then students which is going to be the name of my array. 14 00:00:53,220 --> 00:00:58,830 And if this was a normal variable we would go ahead and type as string or whatever at the end. 15 00:00:58,830 --> 00:01:02,430 And in this case string just means that this variable is text. 16 00:01:02,460 --> 00:01:05,700 This is what a normal variable declaration would look like. 17 00:01:05,700 --> 00:01:10,950 But in the case of an array we're going to go ahead and put open and close parentheses here and we're 18 00:01:10,950 --> 00:01:19,620 going to tell the system to expect values 0 to 4 so by typing this open parentheses close parentheses 19 00:01:19,650 --> 00:01:24,430 and telling the system expect 0 to 4 within these parentheses. 20 00:01:24,480 --> 00:01:30,360 I've turned this normal variable declaration into an array variable declaration and this tells the system 21 00:01:30,420 --> 00:01:32,560 have a variable named students. 22 00:01:32,790 --> 00:01:34,060 It is an array. 23 00:01:34,170 --> 00:01:42,660 It has zero to four items within it which really means it has a 0 1 2 3 and 4 item meaning five total 24 00:01:42,660 --> 00:01:45,470 items since the indexing starts with 0. 25 00:01:45,630 --> 00:01:53,360 And it is all going to be text so we can go ahead and assign values to those now so we'll say students 26 00:01:53,530 --> 00:01:55,060 0. 27 00:01:55,500 --> 00:01:57,870 And in this case this means the zeros. 28 00:01:57,900 --> 00:02:00,090 Item of my students array. 29 00:02:00,270 --> 00:02:07,820 So the very first item and we'll go ahead and use John and we'll say the second item which corresponds 30 00:02:07,820 --> 00:02:22,400 to the index number one is going to be James and we'll have one for Hannah one for Connor and one for 31 00:02:23,120 --> 00:02:25,400 Christina. 32 00:02:25,440 --> 00:02:31,720 Now I've put quotation marks around all of these names to signify to the system that they are text and 33 00:02:31,720 --> 00:02:38,170 we can see I have 0 1 2 3 and 4 which is what I told the system to expect for this array. 34 00:02:38,560 --> 00:02:43,570 So now this is the all of these items are going to be stored as an array as a sort of list within the 35 00:02:43,570 --> 00:02:44,440 system. 36 00:02:44,560 --> 00:02:49,990 And now we'll be able to come back in later and pull out any of these numbers to find any of their corresponding 37 00:02:49,990 --> 00:02:50,740 value. 38 00:02:50,830 --> 00:02:53,480 So let's go ahead and give an example of that. 39 00:02:53,640 --> 00:03:00,550 I'm going to create an integer type variable which just means a number and I'll call this one end and 40 00:03:00,550 --> 00:03:06,490 then I'm going to set an equal to an input box meaning we're going to ask the user what they want to 41 00:03:06,490 --> 00:03:12,340 store in the value end and we'll say which student number do you want to view 42 00:03:15,090 --> 00:03:22,830 and we'll call this the title say a student I.D. And then last but not least after we've pulled the 43 00:03:22,860 --> 00:03:28,950 value n which is the corresponding student number we'll want to show what that name was that we pulled 44 00:03:29,310 --> 00:03:37,050 the number four so we'll say we'll create a message box that says you selected colon space then we use 45 00:03:37,050 --> 00:03:37,380 the. 46 00:03:37,380 --> 00:03:45,100 And sign or Ampersand to make sure that I'm going to combine these two text strings go and say students 47 00:03:45,710 --> 00:03:49,850 and and we'll call this message box Student Name. 48 00:03:49,960 --> 00:03:55,030 Now I put this extra common here because that is usually what the buttons are for your message box. 49 00:03:55,030 --> 00:03:59,770 So I jumped over this which means it's just going to be an okay button and we can see right now the 50 00:03:59,770 --> 00:04:04,870 section in bold is what section I'm working in right now which is the title of the box which I've given 51 00:04:04,870 --> 00:04:13,490 to be student name now up here we assigned values to our array variable by saying the array variable 52 00:04:13,490 --> 00:04:19,370 parentheses the item number close parentheses equals this particular value. 53 00:04:19,370 --> 00:04:24,160 So each of these item numbers corresponds to the value that we assign to it. 54 00:04:24,170 --> 00:04:30,110 So in this case we're going to pull a new value a new item number and which is what the user will input 55 00:04:30,710 --> 00:04:33,110 and then we're going to output that item number. 56 00:04:33,110 --> 00:04:40,270 So students and and this is going to read what is currently stored in the end value of students. 57 00:04:40,280 --> 00:04:45,670 So for example if we enter the value three four and we should expect it to read back to us. 58 00:04:45,700 --> 00:04:54,150 Connor now what I'm going to do is I am actually going to type this list onto our main screen here so 59 00:04:54,150 --> 00:04:57,090 that we know which names correspond to which values 60 00:05:01,280 --> 00:05:03,320 and once we're ready to run our code. 61 00:05:03,450 --> 00:05:11,140 We can go to macros select our static array macro and click Run it's asking us which student number 62 00:05:11,140 --> 00:05:18,550 we want to view so we'll say three and click ok and we can see you selected Connor which is what we 63 00:05:18,550 --> 00:05:20,870 expect to show up for the value 3. 64 00:05:20,980 --> 00:05:23,550 So we know our arrays behaving properly. 65 00:05:23,650 --> 00:05:27,290 The message box is behaving properly everything is working as expected. 66 00:05:27,790 --> 00:05:35,600 But let's say we actually decide to add a fifth student to this list and let's say this is Eddie. 67 00:05:35,650 --> 00:05:39,280 Now we should expect this to read an error when we try to run it. 68 00:05:39,280 --> 00:05:44,360 And that's because we've told the system our array should only have value 0 to 4. 69 00:05:44,620 --> 00:05:47,500 And we've just created a fifth item down here. 70 00:05:47,710 --> 00:05:52,970 So we should expect the system to flag this as an error so I'll come over here. 71 00:05:52,980 --> 00:05:59,760 I'll go ahead and put Eddie onto our list so that we know and I'll go to macros highlight my macro and 72 00:05:59,760 --> 00:06:01,140 click Run. 73 00:06:01,670 --> 00:06:03,860 And I do get a pop up error message. 74 00:06:03,860 --> 00:06:06,050 It says subscript out of range. 75 00:06:06,180 --> 00:06:10,560 If we click debug it will highlight the line that's giving us a problem. 76 00:06:10,830 --> 00:06:17,220 And it has highlighted our fifth line here where we've tried to create and a fifth item Eddie into our 77 00:06:17,220 --> 00:06:20,250 array which is only supposed to hold up to the fourth item. 78 00:06:20,880 --> 00:06:27,830 So go ahead and click the stop button to reset your code if we comin out this line by putting a little 79 00:06:27,830 --> 00:06:28,820 apostrophe. 80 00:06:29,000 --> 00:06:34,160 We can see it turns green and if we try to run this again the code should skip right over this line 81 00:06:34,190 --> 00:06:36,580 and go back to working properly. 82 00:06:36,680 --> 00:06:38,000 So we'll try it one more time. 83 00:06:38,000 --> 00:06:41,530 Go to macros run static array this time. 84 00:06:41,540 --> 00:06:46,320 Let's view whatever is an item to click OK and we can see we selected Hannah. 85 00:06:46,320 --> 00:06:48,650 So now the code is back to working properly. 86 00:06:48,830 --> 00:06:51,580 And again that's because we've told the system ahead of time. 87 00:06:51,650 --> 00:06:57,530 This array is going to have values for item 0 4 and then all the sudden we tried to create a fifth item 88 00:06:57,560 --> 00:07:03,080 which obviously breaks that sizing that we gave to the array earlier. 89 00:07:03,080 --> 00:07:07,400 So this begs the question can we re size our static array. 90 00:07:07,640 --> 00:07:13,640 Well in the case of our static array as we have here all we need to do is come back in and change is 91 00:07:13,640 --> 00:07:15,700 number to be five. 92 00:07:15,770 --> 00:07:20,780 We can go ahead and get rid of the apostrophe so that this is no longer common line. 93 00:07:20,780 --> 00:07:27,440 And now we've told the system originally this red 0 4 meaning we can only hold up to our fourth item. 94 00:07:27,440 --> 00:07:29,200 Now we've changed it to read five. 95 00:07:29,200 --> 00:07:34,420 So now we should be able to hold up to our fifth item here which should read Eddie. 96 00:07:34,490 --> 00:07:41,370 So we can go ahead and try running this again come down here to macros highlight static array and click 97 00:07:41,370 --> 00:07:45,360 Run and we can see now it seems to be working just to double check. 98 00:07:45,360 --> 00:07:51,540 Well we'll put the number five and click ok and it outputs Eddie so we know this works. 99 00:07:51,540 --> 00:07:56,790 We can go ahead and resize our variable by changing the numbers up here but there is actually a better 100 00:07:56,790 --> 00:07:59,840 way to do this and that is with a dynamic array. 101 00:08:00,090 --> 00:08:05,520 In this video we talked about static arrays which are the simplest kind of array that you'll encounter 102 00:08:05,550 --> 00:08:08,340 and excels VBA in our next video. 103 00:08:08,340 --> 00:08:13,980 We'll cover dynamic arrays which take our array knowledge a little further by letting our arrays grow 104 00:08:13,980 --> 00:08:16,260 and shrink dynamically throughout our code.