Often times we're interested in using arrays in VBA to create a subroutine or a user defined
array function.
So in this screencast I am going to show you how to use VBA subroutines and functions involving
arrays.
So the first example the goal is to create a sub that sums cells A1 to A8.
So we have a fixed range and a fixed location.
Basically what we want to do is create a subroutine that will sum those eight numbers, and obviously
this is pretty trivial because you can just use the sum function in Excel, but we want
to create a subroutine that actually outputs the sum in a message box.
Let's start by making a flow chart.
We start and the first thing we want to do is iterate.
If you we're actually trying to do this by yourself, and tally up these eight numbers
you would start with 8 and then add 4 and then add 2 and so on, so that is exactly what
we want to do in our flowchart.
We start with our i, which is going to be our index, so we start with i=1, we then say
i=i+1, and we stop when i is greater than eight.
As long as i is not greater than eight we're going to keep summing.
So we have this sum that's going to be equal to sum+current cell value, and then we're
going to keep iterating, we're going to increment i by i+1.
Once i finally exceeds eight then we're going to display the sum, and we are done.
The code for this sub that I've named "SumVector" is shown here.
You see that we dim n as an integer, and we're
going to use n to count the number of rows in a a moment. We have "Sum" which is the
running tally of the sum. We also have i, we've dimmed i as an integer, and then we
have the statement here "n = Range("A1:A8).rows.count" and what this does is we have an object, the
object here is range A1 to A8. Now that we know the number of rows we can iterate from
i=1 to n. We put in this function here where the new sum is the old sum plus "Range("A1"A8").Cells(i,1)".
So it's the cells i,1 of our object, which is the range A1:A8. So then we keep iterating
until i is greater than n, and n in this case is just going to be 8, and then we message
box "Sum is", and then this produces a sum. So I am going to run this by pressing F5.
We get an output box that is saying "Sum is 37." The next thing I am going to do is show
you how we can transform this into a function. So now what we're trying to do is develop
a VBA function to sum an arbitrary vector, not just 8 rows, not the same location. We
have a different vector here, G2 to G11, it's not 8 values, and it's not in A1 to A8. So
we want to be able to create a function now, not a sub, where we can type in the sum vector
of a selection, and this is going to be a vector selection. When we run this it will
actually spit out the sum of that vector. So here is the sum vector code that we just
worked with, and we want to make this into a function, and we want to make it so it will
work on an arbitrary range. There's a couple of modifications we need to make to this code,
but that is changing a sub to a function, and I don't know if you noticed that, but
when I pushed down it actually changed the "End Sub" automatically to "End Function."
The next thing we have to do is create dummy variable here, so I am going to put in "Data
Range", this is the argument to our function. We're not going to be using Range(A1:A8) as
our object, instead the object is just going to be "Data Range", which is the argument
to our function. We're going to be summing the same, the other difference here is we
no longer have the range as the object. The only other thing is you never want to have
a message box built into a function, so we will put "SumVector" that's the output of
the function is equal to sum. Back over in Excel I can type into a cell, and you see
as I write this the intellisense detects that I have a user defined function, and I am going
to select what I want to put into it, and then I press enter, and it gives us the sum
of 9. The next thing we're going to do is instead of summing a vector we're going to
sum and array. So for example here, we have a 3X3 array, so we have a sub here that's
going to spit out a message box with our sum. So we start out and then we actually iterate
through rows, but in each array we actually are going to iterate through all columns,
so if we a have a 3x5, let's say, we're going to iterate through row 1, and for that row
we're going to iterate through all five columns of that row, and then we're going to bump
up to the second row and we're going to iterate through all five columns of that second row,
and so on, so we have this nested for loops. When we're done iterating we can display the
sum. The code for this, shown here, this is a sub sum array, we dimmed the number of rows,
number of columns as integers. Sum is a single, i and j are also counting numbers that we're
going to be iterating through. You'll see here we have number of rows equals "Selection.Rows.Count",
so we're counting the number of rows in that selection. nCols, we're counting the number
of columns in that selection, and then we're iterating through i, which corresponds to
the number of rows, and j which corresponds to the number of columns, and for each of
those cells in that array that we're on the new sum is the previous sum plus the current
value of that cell, and then we finish this off with a message box, and we end sub. So
this is my selection, I am going to press F8, and if you look down here at the locals
window you can see I just did the number of rows, I picked it up as three, number of columns
as three. You'll notice that at the end of every for loop the index actually comes out
of that four loop as one greater than the maximum, and that's just because in VBA it
actually increments the index at the end of the loop, and we end up with a message box
for the sum. I am now going to quickly transform this into a function, and to do that I change
sub to function, and when I go down you'll notice that the "End Sub' automatically changes
to an "End Function". I need to add an argument, so I'll do "Data Range" just like I did for
the vector. The object is no longer selection, it's my data range. Don't want a message box
in a function so the output is just the name of the function,and that's going to equal
sum. Let's test this in Excel, I can put this range in or I can put a named range if I want,
press enter, and that is the sum of our array. We're going to create a VBA array function,
it takes an arbitrarily selected range of cells and displays on the same spreadsheet
a new array, let's just say B, whose elements represent the natural log of all numbers that
are greater than zero, and adds twenty to all elements in A that are less than or equal
to zero. I've already got this function built in the VBE, I've got an argument data range,
and one thing we have to do here, we're actually going to be creating a new array, and so we're
having a local array, B, and a local array, A, within our sub, and because we have arrays
within our sub we have to declare those arrays, so I have A as a variant, and I have B as
a variant. You'll notice here that I have empty parenthesis, that means we don't know
the size of the array to begin with, and all the dim statements have to go at the beginning
of our procedure. The first thing we do is we count the number of rows in our data range.
We count the number of columns in our data range. Once we know the size we can re-dim
A, and we can re-dim B with the known size now. We're going to do a double iteration
because it's an array, and during each of those iterations we're going to first pick
up a value to place into our local array A, and then we're asking ourselves a question,
if A is positive then B, that's our output array is going to be equal to log of that
number, otherwise we're going to add 20. And then array change, that's the name of the
function, is just equal to B. So let's take a look at this over in Excel, I've already
named this array A, and now what I am going to do is highlight a 3x2, because I know that
that has to be the size of my output, and I'll do Ctrl+Shift+Enter. So we've done conditional,
so you can actually add conditionals into user defined function, and in this case a
user defined array function.