In this video we're going to talk a little bit about Multidimensional arrays which would allow us to
put multiple columns and multiple rows all into one big table array that we can reference in our code.
Right now we've created a one dimensional list just a list of names.
Nothing special about it.
So go ahead and take out this so we can keep our code functioning the way it was before.
And we see we have this two dimensional one dimensional list just a list of names.
I go back to my Excel file we see these are all in the same column.
This is a one dimensional list a one dimensional array that we've created but we could actually create
a two dimensional array if we wanted to to match up with this data say that we wanted the great value
to be in there as well.
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
VBA code and we will create a third public subroutine and we'll call this two dimensional array like
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
dynamic and it is a string meaning text and then we'll use read em to go ahead and size this array and
this time instead of only putting in this one particular size zero to four only we're gonna put in a
second.
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
file we know the zero to five corresponds to this direction.
All of these are going to be zero one two three four five.
It represents the row number of these particular items.
That second sizing that we just put in that zero to one right here in this case means the columns 0
and 1.
So all of these are zero.
All of these are one that when we refer to items in these or miss two dimensional array instead of just
saying item number zero and knowing that we're going to get John out of it it's now a two dimensional
array.
So item number zero could correspond to either these or zero could correspond to all of these.
So in essence this becomes a coordinate system.
This is item 0 0.
This is item 0 1.
This is item 1 0.
This is item 1 1.
And we'll build the array like this that we can easily see.
OK my name column is the zeros column and my grade column is the first column so I know anytime I see
a comma zero I'm referring to the name.
Anytime I see a comma one I'm referring to the grade.
And so we can just tweak our existing code a little bit to get this to behave the way we want to.
So I'll go ahead and enter down here.
So I get some room and I'll go ahead and create all of these variables assign them values so my 0 0
is going to be John.
And that is again because I am in my zero throw zeros column which if we go back to the Excel file we
can see my 0 0 0 column is John.
Now I'll go ahead and go into my first row zero column which is James my second row zeros column is
Hannah my third row zeros column and again anytime we see that zeros column we know that we're referring
to the name column and my last name
is a
and I'll come back you're here NATO type on this one so correct it and they should be all of my names.
Now we're going to do the same thing but we're going to do it for our grade value.
So in this case we'll again start in our zero throw.
But instead of the zeros column we'll be in the first column and we'll set that equal to 90 and now
when I come back to my Excel file we can see my 0 throw my first column is 90 and that makes sense.
So we'll come back to here and we'll go ahead and set up all the rest of them as well.
First row first column ninety five
second row first column ninety nine third row first column ninety seven
the fourth row first column is a ninety four and the fifth row first column is a ninety two so now I
have my two dimensional array set up to appear like it does in this excel file and we can go ahead and
get an input and an output from this so we'll create an integer this time we'll call it y and we'll
say why is the input box and we'll use the same input box that we've been using which student number
do you want to view and we'll call this input box student I.D. close parentheses and we'll go ahead
and create our output box our message box and this time we'll say you selected
and to combine our tech strings D which is the array name and in this case we can't just put the number
you need but the number of the row and the number of the column.
So we'll start with the row that the user just selected and the zeros column which is going to be that
person's name.
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
list that person's name which is the zeros column that the and sign will say has a and sign this time
we'll call our first column which is the grade corresponding to that row and sign in this class and
we'll go ahead and title this box.
Student name and grade.
So if we break this down for a second in case you haven't seen the string video or aren't familiar with
these Ampersand and the BBC are RL F here.
VBA treats all string as text.
So all of these string variables that we've created they're all text.
And when we feed that text into a variable for example we surround that text with quotes.
So in this case the text is just John and the quotes is what tells the system.
This is actually going to be text it's going to be string so we've done the same thing with our message
box.
This is all the text that is going to appear appear as the actual message if we wanted to change the
buttons we would do that here but we're going to leave it as just the OK button.
And this text is what shows up as the title of that box.
So if we just look at this message and what's appearing in this message we've given it the text you
selected and we put a space at the end of this and this text is its own text surrounded in quotes and
the and sign means we're about to combine this text with more text but this time it's gonna be text
that's in a variable form so we're not just typing exactly the word that we want there.
We're going to let the system pull the value of this variable in this case the name that the user has
selected we're going to again use the enzyme which means we're not quite done building our message we're
not quite done building all this text yet the VCR left just means we're going to create a new line it's
like hitting enter on your keyboard and that's just the code that VBA uses to designate a new line again
we'll use the and sign to mean hey we're not we're still not done yet we're still going.
This is again a variable again the name and sine again to mean we're still going we've put a space here
and then says has a.
So this is going to be its own text that this the system is going to continue tagging onto the end of
our prompt as we go then we'll put another and sign this time we're reading the grade another and sine
space here in this class.
So when we run this we should see you selected the person's name and then on a new line we'll see the
person's name has a grade in this class and we'll see this when we run the code.
So if we go ahead and shrink our VBA window click on the macros button this time select two dimensional
array and click Run and we'll say we want to view student 2 and click ok.
Now we see we selected Hannah which makes sense.
Hannah is our second student year and Hannah has a ninety nine in this class.
And again this makes sense because we can see her corresponding grade is a ninety nine.
So this is worked by pulling our zeros column which is the name our first column which is the grade.
And we've got the row number that the user selected to begin with.
And so this is because we've created a two dimensional array and usually used two dimensional arrays
for when your data is somewhat related.
In this case we have the name and we have the grade associated with that particular person's name.
And then we can output all of this data however we want to output this data using our BBC or Olaf which
is the enter using our Ampersand to build all of this string together as one long message and we'll
go ahead and click OK so in this video we talked about how to create arrays and build arrays within
VBA.
And there are some easier ways to do this than just manually typing every single value in there.
And we'll go with those in later videos but arrays are a great tool to store multiple values lists of
values into your VBA code for future use.
They're much faster than trying to assign a single variable a different value every time or creating
hundreds of variables for all of the data you're trying to keep track of.
So we talked about static arrays which have one size and cannot be changed size unless you come back
and you change it in the very initial declaration.
We talked about dynamic arrays which we can resize as we build the code at any point in the code and
we can use the word preserve to make sure we don't lose any of our pre-existing data.
And we've talked about two dimensional arrays which are as we talked about in the Excel file capable
of holding column data and row data so we can really build some complex large lists and tables into
arrays and use that data later in our code.
Overall arrays are very useful tools for reading and writing data and I would encourage you to take
advantage of them while coding in VBA.