21.2 For Each
While you could use a variety of techniques to solve the two problems in this section, they are especially well suited to using For Each loops. In fact, anytime you pass a range to a function, you will typically use a For Each loop.
Add Squares
For the first scenario, you are asked to create a UDF named “AddSquares” that squares the amount in each cell from the input range and then adds them together (i.e., AddSquares(b8:c:11)=234 ).
Again, I know how tempting it is to simply look at the code block below, but you will be better served by jumping straight to the class file and then returning when you have implemented your solution.
Here is the solution we used to solve the problem:
As you can see, we pass in a range, and then we iterate over each cell in that range. For each cell, we simply square its value and add that to the total. In this case, we used the name of the function since this is the result that will ultimately be returned.
Combine Emails for an Email List
Your email program requires each address to be separated by a semicolon. Create a UDF named “MyEmailList” that combines the emails from any range of cells and formats them for your email program. Yes, we know we can easily do this with the TEXTJOIN function, but we are practicing our VBA here. 😊
Again, our function will need to accept a range and iterate over this range. In this case, you can take the value of the current cell, add a semicolon to it, and add that to the running total of all email addresses.
Here is the simple function we created that solves this problem: