Loop through a Range of Cells in a UDF in Excel

If you don't know what a UDF is, view this tutorial to learn more: Custom Functions in Excel - UDF

Sections:

Loop through a Range of Cells in a UDF

Working Function

Final Code

Notes

Loop through a Range of Cells in a UDF

In order to do this we need to do three things, a range reference so we know what cells to loop through, then a loop, then access the values in the cells from within the loop.

Range Reference

Since a UDF is a custom function in Excel, the user will be the one to input the range that we loop through and we need to first allow them to give the range to the UDF.

1. Hit Alt + F11 to go to the VBA Editor window and go to Insert > Module to get a new module and start the function.
2. Enter the basics needed for the function:
   
3. Create the argument that the user will use to select a range, this goes in between the parenthesis. The name of the argument can be anything so long as it isn't already being used by VBA; common practice is to name the argument for the range as rng.
   
4. That's it, now you have an argument in the function and this argument will contain the entire range of cells that the user selects. The next step is to loop through it.

Note: Often you will see the arguments in the parenthesis written like this rng as Range. This syntax simply tells VBA what type of variable this is. It is "good practice" to do that but, in reality, for most of us writing small macros for work, that step is not necessary and so I will usually leave it out to make the code simpler and easier to understand.

Loop through the Range

Now that we have one variable that will contain the range, it's time to loop through that range.

We do this using a For Each Next Loop

1. In the UDF we made above, type For Each c In rng
   
   For Each starts the loop.
   c is simply a variable, it can be called anything except names already in use by VBA. This variable is what we will use within the loop to access the values in the cells. c is commonly used to indicate "cells".
   In is another part of the loop syntax and is needed always.
   rng is the argument variable from in-between the parenthesis that contains the user-supplied range.
2. Type Next a few lines down. This will be the bottom or end of the loop.
   
   Everything we want to do with the cells selected by the user will go inside of this loop.
3. That's it.

That's all there is to looping through a range of cells selected by the user in a UDF, User Defined Function, in Excel. Now, let's do something with these cells and access their values within the loop.

Do Something With Each Cell in The Range

Now that we are looping through the range, we should do something with the values in each cell.

I will set a simple variable equal to the output of the cells:

I added this line:

The way that you reference the cells in the range from within the loop is to use the variable that you created in the first line of the For Each statement, in this case c.

Remember that a loop will iterate through each element in an array, or, in this case, in the range of cells. That means that, each time the loop runs, it goes through one cell in the range, and we can then get the value from that one cell and do something with it.

The .Value that comes after the c in the loop tells Excel that we want to get the value or data that is currently inside of the cell that we are looping through.

celldata is simply another variable that is being used to store the cell's value, accessed via c.Value, so it can be more easily used further down in the loop.

Working Function

To make this example more useful, I am going to input a simple IF statement to check if any cell in the user-selected range is higher than 5 and then I will add the last line to make the UDF actually work.

1. Add the IF statement that will check each cell.
   
   The IF statement checks if the value of the current cell in the loop, which is now stored in the celldata variable, is greater than 5 or not and, if so, sets the result variable equal to TRUE.
2. Add a new line at the top of the function to set the default value of result to FALSE. This is so it will automatically output FALSE if no data in the range is greater than 5. This must go outside the loop to ensure it is always set as FALSE by default.
   
3. Add the last line of the function, the line that makes it return a value, and set it equal to the result variable.
   
4. That's it!

If you want to test it out, go back to Excel and use the new function.

Result:

Final Code

Here is the final UDF code:

Notes

These are pretty basic VBA concepts but, in the context of a UDF, it can seem more confusing than it actually needs to be.

Remember, excel stores a user-selected range as an array of cells and they will be stored in whichever argument you set it to when you create the UDF. Once you have an argument that stores the range, you simply need to loop through that range.

Make sure to download the file attached to this tutorial so you can test this out in Excel.