In this video we're going to talk about how to build and interact with arrays in Visual Basic within
Excel.
And so the first thing we're going to want to do is go ahead and open up our visual basic window.
So we'll go to the developer tab and click on visual basic and then we'll go ahead and we'll insert
a module.
Now I'm going to go ahead and create a public subroutine by typing Public Sub and we'll call this one
static array.
Remember not to put spaces in between your variables or subroutine names or anything like that within
VBA.
But the open and close parentheses at the end and then we're going to go ahead and declare our first
right now.
Arrays are clear are declared similarly to regular variables.
We type dim to mean declare and then students which is going to be the name of my array.
And if this was a normal variable we would go ahead and type as string or whatever at the end.
And in this case string just means that this variable is text.
This is what a normal variable declaration would look like.
But in the case of an array we're going to go ahead and put open and close parentheses here and we're
going to tell the system to expect values 0 to 4 so by typing this open parentheses close parentheses
and telling the system expect 0 to 4 within these parentheses.
I've turned this normal variable declaration into an array variable declaration and this tells the system
have a variable named students.
It is an array.
It has zero to four items within it which really means it has a 0 1 2 3 and 4 item meaning five total
items since the indexing starts with 0.
And it is all going to be text so we can go ahead and assign values to those now so we'll say students
0.
And in this case this means the zeros.
Item of my students array.
So the very first item and we'll go ahead and use John and we'll say the second item which corresponds
to the index number one is going to be James and we'll have one for Hannah one for Connor and one for
Christina.
Now I've put quotation marks around all of these names to signify to the system that they are text and
we can see I have 0 1 2 3 and 4 which is what I told the system to expect for this array.
So now this is the all of these items are going to be stored as an array as a sort of list within the
system.
And now we'll be able to come back in later and pull out any of these numbers to find any of their corresponding
value.
So let's go ahead and give an example of that.
I'm going to create an integer type variable which just means a number and I'll call this one end and
then I'm going to set an equal to an input box meaning we're going to ask the user what they want to
store in the value end and we'll say which student number do you want to view
and we'll call this the title say a student I.D. And then last but not least after we've pulled the
value n which is the corresponding student number we'll want to show what that name was that we pulled
the number four so we'll say we'll create a message box that says you selected colon space then we use
the.
And sign or Ampersand to make sure that I'm going to combine these two text strings go and say students
and and we'll call this message box Student Name.
Now I put this extra common here because that is usually what the buttons are for your message box.
So I jumped over this which means it's just going to be an okay button and we can see right now the
section in bold is what section I'm working in right now which is the title of the box which I've given
to be student name now up here we assigned values to our array variable by saying the array variable
parentheses the item number close parentheses equals this particular value.
So each of these item numbers corresponds to the value that we assign to it.
So in this case we're going to pull a new value a new item number and which is what the user will input
and then we're going to output that item number.
So students and and this is going to read what is currently stored in the end value of students.
So for example if we enter the value three four and we should expect it to read back to us.
Connor now what I'm going to do is I am actually going to type this list onto our main screen here so
that we know which names correspond to which values
and once we're ready to run our code.
We can go to macros select our static array macro and click Run it's asking us which student number
we want to view so we'll say three and click ok and we can see you selected Connor which is what we
expect to show up for the value 3.
So we know our arrays behaving properly.
The message box is behaving properly everything is working as expected.
But let's say we actually decide to add a fifth student to this list and let's say this is Eddie.
Now we should expect this to read an error when we try to run it.
And that's because we've told the system our array should only have value 0 to 4.
And we've just created a fifth item down here.
So we should expect the system to flag this as an error so I'll come over here.
I'll go ahead and put Eddie onto our list so that we know and I'll go to macros highlight my macro and
click Run.
And I do get a pop up error message.
It says subscript out of range.
If we click debug it will highlight the line that's giving us a problem.
And it has highlighted our fifth line here where we've tried to create and a fifth item Eddie into our
array which is only supposed to hold up to the fourth item.
So go ahead and click the stop button to reset your code if we comin out this line by putting a little
apostrophe.
We can see it turns green and if we try to run this again the code should skip right over this line
and go back to working properly.
So we'll try it one more time.
Go to macros run static array this time.
Let's view whatever is an item to click OK and we can see we selected Hannah.
So now the code is back to working properly.
And again that's because we've told the system ahead of time.
This array is going to have values for item 0 4 and then all the sudden we tried to create a fifth item
which obviously breaks that sizing that we gave to the array earlier.
So this begs the question can we re size our static array.
Well in the case of our static array as we have here all we need to do is come back in and change is
number to be five.
We can go ahead and get rid of the apostrophe so that this is no longer common line.
And now we've told the system originally this red 0 4 meaning we can only hold up to our fourth item.
Now we've changed it to read five.
So now we should be able to hold up to our fifth item here which should read Eddie.
So we can go ahead and try running this again come down here to macros highlight static array and click
Run and we can see now it seems to be working just to double check.
Well we'll put the number five and click ok and it outputs Eddie so we know this works.
We can go ahead and resize our variable by changing the numbers up here but there is actually a better
way to do this and that is with a dynamic array.
In this video we talked about static arrays which are the simplest kind of array that you'll encounter
and excels VBA in our next video.
We'll cover dynamic arrays which take our array knowledge a little further by letting our arrays grow
and shrink dynamically throughout our code.