In this screencast, I'm going to show you the difference

between subroutines that act on arrays,

and functions that act on arrays.

So, a subroutine can act on one or more cells.

It doesn't have to use cells.

But in the examples in this screencast,

we're going to use cells as we going to use arrays.

But subroutines can also use input and message boxes to interface with the user.

Functions do not do that.

They do not use input or message boxes.

Subroutines can make manipulations to selections, or ranges,

and they can be used to create new arrays that can be

exported to other areas of the spreadsheet.

I showed you an example of this in a previous screencast.

So we can have a selection,

and then we can make permanent changes to that selection using the add five sub.

So we can just add five to all of those.

I also showed you how we can add five,

but then offset the result skipping one row.

So when we do this,

it takes our selection, adds five to each of them,

and then exports the resultant array in a new area of the spreadsheet.

So these are just two examples of how we can use

subroutines to manipulate data on the spreadsheet.

In contrast, we can use functions that act on arrays.

So, these are single formulas in a single cell.

They use cells of the spreadsheet as arguments and the result is a single answer.

Now Excel has a couple of built-in functions of arrays.

Just one example would be the average function.

So, the argument to the average function is an array,

but the output is a single cell.

So we can calculate the average.

There's also the standard deviation function that acts on an array.

There's the max function,

so we can calculate the max.

And there's also a min and there's many more of these.

But, bottom line is these are functions of arrays,

but they result in a single value in a single cell.

So what we're going to do now is we're going to create two examples here.

We're going to create a VBA subroutine that's going to

count the number of elements in an array that are divisible by n,

which is input by the users.

We're going to start with our selection.

And when we run this, it's going to ask the user for

an integer and I'm just going to do three.

And then it's going to go through here and it's going to count the number of

items in our array that are divisible by three and we see that there are three.

So that's an example of a subroutine that uses an array as kind of its object,

and outputs a single result.

We're going to then create a function of an array.

It's going to be called count divisible by N,

similar to the average and standard deviation functions and

min and max functions that are built into Excel.

We're going to have on a range here,

on the spreadsheet as the argument.

And there's also a second argument to this function.

We want to see how many of these items in our range B2 to D4 are divisible by three.

So the user provides that.

And this does the same thing that that subroutine did.

Although, it results in a permanent value on the spreadsheet.

So, this is known as a function of an array.

So let's go ahead and do the subroutine.

We're going to start with a selection.

So the selection is going to kind of be the object that we're working on.

Count divisible by N. We dim i,

j as integers, number of rows and number of columns as integers.

I've also got this variable n,

which is going to be chosen by the user,

and that's going to be the number that they want to see how many are divisible by.

So in my example that was three.

I've also got the second integer c,

which is just going to be a counting index.

So just like we've done in previous examples,

we're going to count the number of rows,

count the number of columns,

then we're going to obtain in an input box from the user the integer, for example three.

And we want to count the number of items that are divisible by that number.

Then we set up our nested for loops to iterate

through the different cells of our selection.

And in inside each cell,

we're going to determine if that cell,

that particular cell is divisible by N. If the remainder is zero,

then that means is perfectly divisible by N,

and in that case then we're just going to add

1 to our c. So c is going to count the number of items.

And finally in a message box we're going to output,

there were n items divisible by. Sorry there were.

We need to get this to be c. There were c items divisible by the number that they input.

So let's go ahead and step through this.

We make sure we have a selection, go through here.

We count the number rows, count the number of columns,

we obtain n in an input box.

So that's going to be three.

We validate this down in the locals window.

Then we go through. So, the first one is four,

it's not divisible by three.

Eight is not divisible by three,

but then we get to the 1, 3 position.

So we add negative nine is divisible by three.

So account gets bumped up by one.

And we keep going and going and going to the second row,

and finally the third row.

And it outputs there were three items divisible by three.

And then we can end this up.

Now what we're gonna do is we're going to change this.

I'm going to show you how we can change a subroutine into a function.

The first thing we need to do is,

we need to change the sub to function.

And you notice that when I click on a different line here with my cursor,

notice what happens to the end sub down here.

It automatically changes to end function.

So the name of our function is going to be count divisible by

N. I'm going to have just kind of a space holder.

This is going to be my range as a range. So I'm dimming.

That's going to be what the user inputs as one of the arguments.

And then n, is going to be an integer.

And that's also one of the arguments.

And then the output to the function will just be an integer.

So we can dim that as an integer.

So we're still going to need to get i, j.

We're going to have to count the number of rows, count the number of columns.

N, is no longer input by the user in an input box.

It's input here as one of the arguments.

I can also then delete this line where we get n,

in an input box.

Instead of counting the rows in our selection,

this is going to be our argument here, the rng argument.

Because that's going to be a range.

That represents on the spreadsheet

whatever the range goes into the argument of the function.

So, I'm going to change those.

We're going to count the number of rows, count the number of columns.

We're still going to do our nested iteration

instead of if Selection.Cells mod n equals zero.

This is going to be rng,

and we're still going to count the number of items.

We never have message boxes in a function.

So instead of outputting the result in the message box,

the result or output of a function is always the name of the function.

So I'm just going to go and copy the name,

and that goes on the left side of an equal sign where we assign n

or c. This should be c. The number of items that are

divisible by N that's going to be the output of the function,

and that's what remains in the cell on the spreadsheet.

So, let's go ahead and do this.

We need to put a breakpoint if we want to step through this.

So, over here on the spreadsheet I'm going to put in count divisible by

N. And I'm going to put in as the first argument this range here,

and then the second argument is going to be a three.

We're going to count how many of these items are divisible by three.

We press enter, we go over here to the debug mode and I can go through,

and it looks like it's working kind of the same.

It counted one for the first row,

and then we keep going,

and our count is now up to two.

And we go to the last row,

and we count one more,

and then the output of the function is three.

So, it results here in a permanent value of three on the spreadsheet.

So that sorta outlines the difference between using

a sub to do different things to compute a single value,

and also using a function that acts on an array.

In the next screencast, I'm going to kind of show you the difference

between how we can use functions of arrays,

as opposed to array functions.

Now array functions is where the output itself is not a single value,

but instead is an array.