1 00:00:00,540 --> 00:00:05,460 In this video we're going to talk a little bit about Multidimensional arrays which would allow us to 2 00:00:05,460 --> 00:00:12,500 put multiple columns and multiple rows all into one big table array that we can reference in our code. 3 00:00:12,510 --> 00:00:16,400 Right now we've created a one dimensional list just a list of names. 4 00:00:16,410 --> 00:00:17,970 Nothing special about it. 5 00:00:18,030 --> 00:00:22,550 So go ahead and take out this so we can keep our code functioning the way it was before. 6 00:00:22,800 --> 00:00:26,850 And we see we have this two dimensional one dimensional list just a list of names. 7 00:00:26,850 --> 00:00:30,810 I go back to my Excel file we see these are all in the same column. 8 00:00:30,810 --> 00:00:36,300 This is a one dimensional list a one dimensional array that we've created but we could actually create 9 00:00:36,330 --> 00:00:41,880 a two dimensional array if we wanted to to match up with this data say that we wanted the great value 10 00:00:41,880 --> 00:00:43,350 to be in there as well. 11 00:00:43,440 --> 00:00:51,460 So I'll go ahead and I'll make up some grades for all of these people and then we will go back to our 12 00:00:51,460 --> 00:01:04,570 VBA code and we will create a third public subroutine and we'll call this two dimensional array like 13 00:01:04,580 --> 00:01:11,390 so we'll go ahead and create a new array we'll just call this 1 2 D and we'll go ahead and let it be 14 00:01:11,390 --> 00:01:21,390 dynamic and it is a string meaning text and then we'll use read em to go ahead and size this array and 15 00:01:21,390 --> 00:01:27,660 this time instead of only putting in this one particular size zero to four only we're gonna put in a 16 00:01:27,660 --> 00:01:28,200 second. 17 00:01:28,260 --> 00:01:40,190 Sighs You're going to say 0 to 5 comma space zero to one and what this means is if I go back to my Excel 18 00:01:40,190 --> 00:01:46,010 file we know the zero to five corresponds to this direction. 19 00:01:46,010 --> 00:01:53,380 All of these are going to be zero one two three four five. 20 00:01:53,390 --> 00:01:57,850 It represents the row number of these particular items. 21 00:01:58,100 --> 00:02:06,110 That second sizing that we just put in that zero to one right here in this case means the columns 0 22 00:02:06,110 --> 00:02:07,490 and 1. 23 00:02:07,490 --> 00:02:09,270 So all of these are zero. 24 00:02:09,290 --> 00:02:16,280 All of these are one that when we refer to items in these or miss two dimensional array instead of just 25 00:02:16,280 --> 00:02:22,310 saying item number zero and knowing that we're going to get John out of it it's now a two dimensional 26 00:02:22,310 --> 00:02:22,700 array. 27 00:02:22,700 --> 00:02:29,590 So item number zero could correspond to either these or zero could correspond to all of these. 28 00:02:29,600 --> 00:02:33,350 So in essence this becomes a coordinate system. 29 00:02:33,380 --> 00:02:35,480 This is item 0 0. 30 00:02:35,630 --> 00:02:38,280 This is item 0 1. 31 00:02:38,540 --> 00:02:41,200 This is item 1 0. 32 00:02:41,240 --> 00:02:43,880 This is item 1 1. 33 00:02:44,240 --> 00:02:47,060 And we'll build the array like this that we can easily see. 34 00:02:47,480 --> 00:02:55,010 OK my name column is the zeros column and my grade column is the first column so I know anytime I see 35 00:02:55,010 --> 00:02:57,830 a comma zero I'm referring to the name. 36 00:02:57,950 --> 00:03:02,420 Anytime I see a comma one I'm referring to the grade. 37 00:03:02,420 --> 00:03:08,660 And so we can just tweak our existing code a little bit to get this to behave the way we want to. 38 00:03:08,660 --> 00:03:10,430 So I'll go ahead and enter down here. 39 00:03:10,430 --> 00:03:20,370 So I get some room and I'll go ahead and create all of these variables assign them values so my 0 0 40 00:03:20,630 --> 00:03:22,060 is going to be John. 41 00:03:22,370 --> 00:03:28,760 And that is again because I am in my zero throw zeros column which if we go back to the Excel file we 42 00:03:28,760 --> 00:03:36,640 can see my 0 0 0 column is John. 43 00:03:36,770 --> 00:03:48,720 Now I'll go ahead and go into my first row zero column which is James my second row zeros column is 44 00:03:48,720 --> 00:03:57,840 Hannah my third row zeros column and again anytime we see that zeros column we know that we're referring 45 00:03:57,840 --> 00:04:03,550 to the name column and my last name 46 00:04:07,600 --> 00:04:08,680 is a 47 00:04:12,040 --> 00:04:19,920 and I'll come back you're here NATO type on this one so correct it and they should be all of my names. 48 00:04:20,090 --> 00:04:25,800 Now we're going to do the same thing but we're going to do it for our grade value. 49 00:04:25,800 --> 00:04:28,920 So in this case we'll again start in our zero throw. 50 00:04:28,920 --> 00:04:36,190 But instead of the zeros column we'll be in the first column and we'll set that equal to 90 and now 51 00:04:36,190 --> 00:04:43,840 when I come back to my Excel file we can see my 0 throw my first column is 90 and that makes sense. 52 00:04:43,930 --> 00:04:48,910 So we'll come back to here and we'll go ahead and set up all the rest of them as well. 53 00:04:48,910 --> 00:04:52,240 First row first column ninety five 54 00:04:55,680 --> 00:05:04,550 second row first column ninety nine third row first column ninety seven 55 00:05:07,670 --> 00:05:17,890 the fourth row first column is a ninety four and the fifth row first column is a ninety two so now I 56 00:05:17,890 --> 00:05:24,880 have my two dimensional array set up to appear like it does in this excel file and we can go ahead and 57 00:05:25,240 --> 00:05:33,910 get an input and an output from this so we'll create an integer this time we'll call it y and we'll 58 00:05:33,910 --> 00:05:42,650 say why is the input box and we'll use the same input box that we've been using which student number 59 00:05:42,650 --> 00:05:55,170 do you want to view and we'll call this input box student I.D. close parentheses and we'll go ahead 60 00:05:55,200 --> 00:06:00,450 and create our output box our message box and this time we'll say you selected 61 00:06:03,600 --> 00:06:12,900 and to combine our tech strings D which is the array name and in this case we can't just put the number 62 00:06:13,470 --> 00:06:17,080 you need but the number of the row and the number of the column. 63 00:06:17,340 --> 00:06:23,610 So we'll start with the row that the user just selected and the zeros column which is going to be that 64 00:06:23,610 --> 00:06:24,510 person's name. 65 00:06:25,500 --> 00:06:38,630 Then we'll use and B B C R L F to create a new line and sign again to continue combining text will again 66 00:06:38,870 --> 00:06:52,850 list that person's name which is the zeros column that the and sign will say has a and sign this time 67 00:06:52,850 --> 00:07:03,880 we'll call our first column which is the grade corresponding to that row and sign in this class and 68 00:07:03,880 --> 00:07:05,800 we'll go ahead and title this box. 69 00:07:06,140 --> 00:07:11,500 Student name and grade. 70 00:07:11,500 --> 00:07:18,010 So if we break this down for a second in case you haven't seen the string video or aren't familiar with 71 00:07:18,010 --> 00:07:22,490 these Ampersand and the BBC are RL F here. 72 00:07:22,510 --> 00:07:25,470 VBA treats all string as text. 73 00:07:25,480 --> 00:07:29,560 So all of these string variables that we've created they're all text. 74 00:07:29,560 --> 00:07:35,320 And when we feed that text into a variable for example we surround that text with quotes. 75 00:07:35,320 --> 00:07:40,440 So in this case the text is just John and the quotes is what tells the system. 76 00:07:40,720 --> 00:07:46,490 This is actually going to be text it's going to be string so we've done the same thing with our message 77 00:07:46,490 --> 00:07:47,320 box. 78 00:07:47,390 --> 00:07:53,150 This is all the text that is going to appear appear as the actual message if we wanted to change the 79 00:07:53,150 --> 00:07:57,030 buttons we would do that here but we're going to leave it as just the OK button. 80 00:07:57,260 --> 00:08:00,830 And this text is what shows up as the title of that box. 81 00:08:00,830 --> 00:08:06,650 So if we just look at this message and what's appearing in this message we've given it the text you 82 00:08:06,650 --> 00:08:14,810 selected and we put a space at the end of this and this text is its own text surrounded in quotes and 83 00:08:14,810 --> 00:08:20,580 the and sign means we're about to combine this text with more text but this time it's gonna be text 84 00:08:20,580 --> 00:08:25,150 that's in a variable form so we're not just typing exactly the word that we want there. 85 00:08:25,310 --> 00:08:31,220 We're going to let the system pull the value of this variable in this case the name that the user has 86 00:08:31,220 --> 00:08:37,470 selected we're going to again use the enzyme which means we're not quite done building our message we're 87 00:08:37,470 --> 00:08:43,920 not quite done building all this text yet the VCR left just means we're going to create a new line it's 88 00:08:43,920 --> 00:08:51,270 like hitting enter on your keyboard and that's just the code that VBA uses to designate a new line again 89 00:08:51,270 --> 00:08:55,370 we'll use the and sign to mean hey we're not we're still not done yet we're still going. 90 00:08:55,590 --> 00:09:03,660 This is again a variable again the name and sine again to mean we're still going we've put a space here 91 00:09:03,720 --> 00:09:05,210 and then says has a. 92 00:09:05,580 --> 00:09:11,280 So this is going to be its own text that this the system is going to continue tagging onto the end of 93 00:09:11,280 --> 00:09:17,940 our prompt as we go then we'll put another and sign this time we're reading the grade another and sine 94 00:09:18,420 --> 00:09:21,150 space here in this class. 95 00:09:21,180 --> 00:09:27,540 So when we run this we should see you selected the person's name and then on a new line we'll see the 96 00:09:27,540 --> 00:09:34,250 person's name has a grade in this class and we'll see this when we run the code. 97 00:09:34,440 --> 00:09:42,090 So if we go ahead and shrink our VBA window click on the macros button this time select two dimensional 98 00:09:42,090 --> 00:09:49,340 array and click Run and we'll say we want to view student 2 and click ok. 99 00:09:49,520 --> 00:09:52,520 Now we see we selected Hannah which makes sense. 100 00:09:52,520 --> 00:09:57,510 Hannah is our second student year and Hannah has a ninety nine in this class. 101 00:09:57,530 --> 00:10:02,040 And again this makes sense because we can see her corresponding grade is a ninety nine. 102 00:10:02,060 --> 00:10:08,640 So this is worked by pulling our zeros column which is the name our first column which is the grade. 103 00:10:08,810 --> 00:10:12,180 And we've got the row number that the user selected to begin with. 104 00:10:12,230 --> 00:10:17,390 And so this is because we've created a two dimensional array and usually used two dimensional arrays 105 00:10:17,390 --> 00:10:20,100 for when your data is somewhat related. 106 00:10:20,150 --> 00:10:25,140 In this case we have the name and we have the grade associated with that particular person's name. 107 00:10:25,400 --> 00:10:31,040 And then we can output all of this data however we want to output this data using our BBC or Olaf which 108 00:10:31,040 --> 00:10:37,550 is the enter using our Ampersand to build all of this string together as one long message and we'll 109 00:10:37,550 --> 00:10:45,150 go ahead and click OK so in this video we talked about how to create arrays and build arrays within 110 00:10:45,150 --> 00:10:46,020 VBA. 111 00:10:46,260 --> 00:10:51,510 And there are some easier ways to do this than just manually typing every single value in there. 112 00:10:51,510 --> 00:10:57,030 And we'll go with those in later videos but arrays are a great tool to store multiple values lists of 113 00:10:57,030 --> 00:11:00,380 values into your VBA code for future use. 114 00:11:00,390 --> 00:11:06,420 They're much faster than trying to assign a single variable a different value every time or creating 115 00:11:06,420 --> 00:11:09,660 hundreds of variables for all of the data you're trying to keep track of. 116 00:11:09,990 --> 00:11:16,860 So we talked about static arrays which have one size and cannot be changed size unless you come back 117 00:11:16,890 --> 00:11:20,080 and you change it in the very initial declaration. 118 00:11:20,220 --> 00:11:26,610 We talked about dynamic arrays which we can resize as we build the code at any point in the code and 119 00:11:26,610 --> 00:11:32,190 we can use the word preserve to make sure we don't lose any of our pre-existing data. 120 00:11:32,190 --> 00:11:40,080 And we've talked about two dimensional arrays which are as we talked about in the Excel file capable 121 00:11:40,080 --> 00:11:48,450 of holding column data and row data so we can really build some complex large lists and tables into 122 00:11:48,450 --> 00:11:51,510 arrays and use that data later in our code. 123 00:11:51,630 --> 00:11:56,670 Overall arrays are very useful tools for reading and writing data and I would encourage you to take 124 00:11:56,670 --> 00:11:58,950 advantage of them while coding in VBA.