How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is almost the same as doing it for a basic macro, but getting the input is different.
If you don't know what a UDF is, view this tutorial to learn more: Custom Functions in Excel - UDF
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.
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.
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.
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
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.
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:
celldata = c.Value
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.
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.
If you want to test it out, go back to Excel and use the new function.
Result:
Here is the final UDF code:
Function CheckValue(rng)
result = False
For Each c In rng
celldata = c.Value
If celldata > 5 Then result = True
Next
CheckValue = result
End Function
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.