All right, I'm just going to go through a couple of examples in this screencast. We are going to create a VBA array function called SortVector, that's going to sort an input vector in ascending order. Now in a previous screencast and this was a screencast in part one of the course in week four, we created a subroutine that would take a selection and sort it. But this is different because we don't want to sort a selection. We want to create a new region of the spreadsheet where we can then type in this function SortVector and it will result in the sorted vector in a different area of the spreadsheet. So we're not changing the original range in the spreadsheet. Just as a reminder, this is what we created in week four of part one of the course. We created a subroutine, so we have a selection and the sort subroutine sorts that selection, all right? But now what we want to do is something different. We want to have a region here, a vector, but I want to be able to type in here something like SortVector of, and then we can use that as our argument, and I can do Ctrl+Shift+Enter. So this is an array function. We have the output, is an array, or a vector in this case. So lets go ahead and create this. Recall that this is known as a Bubble Sort what we're going to be doing, and this is the flowchart. The first thing we do is we input the vector A, then we count the rows of A, then we enter into an outer For loop that iterates from i = 2 up to n where n is the number of rows. Then we enter into this inner For loop where we have a second index j that's going from 2 up to n. And inside the inner For loop, we're going to check to see if A(j-1) is greater than A(j). If that's the case, recall, then that means that those two elements are not sorted in ascending order. And so if that's the case, then we're going to swap. So this algorithm here, over on the right inside this inner For loop, is the swap routine. And one thing I didn't show on here is just that the false end of this will just go back to somewhere over here. So this little swap subroutine then sorts those two. It sorts pairs into ascending order, and we keep going and going and going. And then when we're done, we exit. And at the end of this, A is now sorted smallest to largest. So I've started my SortVector function here. We have a range, the argument is, I'm just going to call rng and that's Dim'd as a range. Now because this is an array formula, SortVector has to be a variant. The default is variant, so I'm not going to put as variant. I need to Dim i, j, and n. So in our flowchart, we have two indices of iteration. We have the outer For loop, we're iterating over i. And we have an inner For loop, we're iterating over j. n is the number of rows, so that's also something that we need to Dim. We need to Dim this temporary variable, so I can Dim Temp as Integer. We also are going to bring in this range. So the argument rng, we're going to bring in as a new vector. Because we're going to be outputting it in a different region, so have to define a local array A. And A is also going to be a variant, so I just Dim A. We don't know the size yet, but I can use rng.Rows.Count to count the number of rows. Now A is going to be a column vector. So I can ReDim A as number of rows with one column. Now, we're ready to enter into the outer For loop here. i goes from 2 to n. Inside the outer For loop, we have j going from 2 to n. And then we have a one way if then. If A(j-1), and we're also going to have comma 1 because this is the row number, and we need a column, so column 1. If that's greater than j, that means that those two items are not in ascending order and we need to swap them. And this just swaps those two elements. So I've got my outer For loop and my inner For loop. If A (j-1) is greater than A(j), then we're going to make that swap. So these three lines of code will swap the two to sort that in ascending order. Then we need to keep going and going, a total of n-1 times for each of j and i. When we're done iterating, then our A is going to be in ascending order, which should be sorted, and the output of a function is the only thing we need to do. The output of the function is always the name of the function. So we can just say SortVector = A. So I think we are ready to go. I'm going to go ahead and put a break point up here. Then go into Excel and type in SortVector. The argument is going to be that range over there. Ctrl+Shift+Enter because it's an array function, and now I can step through. Actually, I'm noticing we forgot to do one thing and that is we need to define what A is. So I'm just going to say A = rng, so that's our argument. Now I can keep going. You see now it brings in A as 4, 9, 1, and 3. A is a four by one. And we can keep going. And those first two are in the correct order. The next pair will not be, so we should bump into the swap routine. And we didn't change it over here because we're not working with this vector on the spreadsheet. We're working with A. And so you notice here, down in the Locals window, we swapped the 9 and the 1. So that was element two and three. We've swapped them. All right. So we can keep going. So we swapped them again. So we have the 3 and the 9 just got swapped. But we have to go n-1 times of the inner loop and n-1 iterations of the outer loop. So we keep going and at the end, we output the result on the spreadsheet. So this is an array function. A subroutine would just change the items and the order of the original selection or range, but in array function you can type in a function and it'll give you kind of a sorted argument. So we're going to make a slight modification to our function. And instead of an array function, we're just going to make a function named ksmallest with arguments the array and k. So here, in this example, ksmallest of this array, this vector here, comma 3, would be the third smallest. There is a function in Excel, it's known as the small function that does the same thing, but this is just some good practice, and this would give us a third smallest, which is -1. So this is going to make use of the function SortVector we just did. The only difference is we're not going to output the entire vector. We're just going to output the third component. We have our range, but then I have k, which will be 3 in this example, the third smallest. A is going to be sorted from small to high. So if we want the kth element, the kth smallest, then we would just pluck out A(k, 1), and that would be the ksmallest. So everything's going to be the same. The only difference at the very end, the output of this, ksmallest, is going to be A(k, 1), all right? So we can go through this. Let me put the break point there. We're going to do ksmallest of the vector here. And we want the third smallest, so we put a three as the second argument. I press Enter, and we bump into our code here. We do the same thing as we did before. We pick up that and we sort this in the Locals window, then in VBA. We are sorting the vector A. And I'm just going to go Debug, Run to Cursor. We bumped out of there. Let's just make sure that, so the smallest one is -7, second smallest is -3, third smallest is -1, and so on. So the output then we're going to take k as 3, in this example. So I'm going to take item (3, 1), which is -1. I'm going to remove that break point and just continue, and we get -1. So that's how we can kind of adapt this Bubble Sort into making an array function and also just a function. Thanks for watching.