In this screencast, I am going to talk about the ReDim Preserve Statement.
You have learned about how to use the ReDim statement when
you do not know the size of an array or a vector to begin with,
you somehow get the size and then you have to ReDim an array.
We always have to dim the vector or array in the dim statements at the top,
even if you do not know the size.
So, in this screencast,
I am going to show you how we can preserve
the elements of an array and then ReDim the size.
So, in the example I am going to show you,
we are going to make a shopping list in this first part.
It is going to ask you if you want to add an item to the list,
so we can put in something like cookies,
we could add something else like milk,
we can add, let us do a third item,
maybe some bread and then we are done.
So, would you like to- so we are going to say no and then it will
export your shopping list to the cells of the spreadsheet.
So, to begin with,
it is sort of a variable size this vector.
So, we are creating a vector here of strings in this case, it is a variable size.
We do not know the size to begin with but inside the sub routine,
we are adding items as we go along,
so we have to resize which would mean ReDim,
but we also have to preserve what is remaining in this vector.
So I am going to show you how to do that in this screencast.
The first thing you do are dim our variables.
We are going to need to dim I.
This variable ANS, short for answer and I will
explain in a minute what that is and our vector or list
vector which I am going to refer to as L. I am also
going to use option based one which is typical when we work with arrays.
Now, because we do not know how many times the user is going to want
to press Okay to add an item to the shopping list,
we are going to put this in a do loop and the first thing inside do loop,
we are going to ask the user if they want to add an item to the shopping list.
So, this is something a little bit different from what you guys have seen but I am
saying answer is equal to message box.
Would you like to add an item to the shopping list? VB, Yes or No.
So, that is Visual Basic yes or no.
This puts a Yes or No button on the message box but it is also,
because we have this on the right side of an equality with this variable answer.
It is going to take the yes or no button, whatever they press.
Yes, it is going to give a return value to this answer variable.
So, yes returns an integer value of six and
No provides an integer value or return value of seven.
And if they click No, then we want to just exit the loop.
So, if answer equals seven,
corresponding to the No button,
then we are going to exit the do and we are going to be done.
Otherwise, if we stay inside the loop,
that means the user wants to add an item and
what I am going to do is just increment i by one,
so now we have the first item in the shopping list.
And then we would ReDim, now we know the size of L,
we would ReDim L as a variant,
which is now just going to be a vector of
one component and then we can use the input box,
so we can use an input box to obtain the new items.
The first element will then be placed in the first element of L and then we loop back.
We ask the user again,
would you like to add an element.
If they select Yes,
that is a return value of six,
so the answer will be six which is not seven,
will stay inside the loop,
I will be bumped up to two.
We will ReDim L as size two now,
however this is where we run into the problem.
Whenever you ReDim, you replace all the elements in that vector with zero.
If you do not want to do that,
and in this case we want to preserve the first element that the user input,
after ReDim, I can do ReDim preserve.
So, we are going to preserve the previous elements but we are going to change the size.
So, now we have added I equals I plus one.
Now, the size is two but we are going to preserve the first element.
Then we are going to get the second element because now I is equal to two.
We are going to get L of two in an input box and we are going to keep going.
So, using this ReDim preserve,
we can resize L. In other words,
we can increase the size by one,
during each iteration of this loop but we will
preserve the previous components of this vector.
So, I have added one more line here,
range A_one to A and then I am concatenating that with I,
for example if they wanted to add four items,
then I would be equal to four,
which would mean in range A_one to A_four we are going to transpose
L vectors by default are row vectors in VBA,
but we want to transpose that for a column,
to make a column vector.
So, we would place L into Range A_one to A_four.
So, let us go ahead and see if this works.
We press F eight,
we open up the message box,
would you like to add an item to the shopping list? And we say Yes.
And you notice down here,
the return value from the message box function here is a six.
It would be a seven for No.
So, answer is not equal to six.
So, then we increase the size,
we ReDim preserve, we create L now size one,
is a vector and to get the first element of L,
we get that in a input box.
So I will say something like cookies and then we
loop and it asks them again would you like to add an item,
you say Yes, the return value for Yes is a six.
So we stay inside the loop,
I is bumped up to two.
Now, we have ReDim preserved L,
which means we resized it to now two,
but we have preserved the previous element in position one.
And then we get that in the second input box and we loop back up,
if we want to add another item,
we stay inside the loop.
We bump up to three, we resize L,
keeping the previous two elements in our vector,
we get the third element and we keep going.
And this time I will select No.
You see that the return value for No is a seven.
So, answer equals seven.
We exit two, and then in range A_one to A_three,
we are going to transpose L and we place that onto the spreadsheet.
So, this is how we can use the ReDim preserve to
kind of keep previous elements in the vector or an array.
Just a word of caution,
you can only use ReDim preserve on the last element of an array.
So, if you were making a two by two array and you wanted to resize it to a three by two,
and preserve everything in there, you can't do that.
As an example, I have deemed A here as unknown size but then I ReDim to a two by two,
I define the elements of that two by two array,
and now I want to ReDim and I want to resize the array,
I want to add a column but I want to preserve all the elements.
So, if we run through this,
we see that we create our A array and open up the first row and the second row,
and then I am going to ReDim preserve.
I am going to resize this to be a two by three,
two rows and three columns,
and when you open up A,
now we have the three columns.
And if I wanted to then I could say A_one comma three equals something,
A two comma three equals something,
but that is how you can ReDim preserve a two by two array.
However, if I wanted to add a row instead of adding a column,
I wanted to add a row,
you can't do this because with the ReDim preserve you can
only change the size of the last index in an array.
So, that would be the column index here.
So, you see when we run through this,
it doesn't like that, it does not work.
So, that gives you an idea of how to use the ReDim preserve statement in VBA.
Charlie NuttelmanInstructor
