Microsoft Office Tutorials and References
In Depth Information
UDF Examples That Solve Common Tasks
Outside the range, cell C1 serves the dual purpose of receiving the UDF, and also displaying the color
you need to sum by. With this approach, the UDF only needs one argument to specify the range to sum:
Function SumColor(RangeToSum As Range) As Long
‘Declare the necessary variables.
Dim ColorID As Integer, ColorCell As Range, mySum As Long
‘Identify the ColorID variable so you know what color to look for.
ColorID = Range(Application.Caller.Address).Interior.ColorIndex
‘Loop through each cell in the range.
For Each ColorCell In RangeToSum
‘If the cell’s color matches the color we are looking for,
‘keep a running subtotal by adding the cell’s number value
‘to the mySum variable.
If ColorCell.Interior.ColorIndex = ColorID Then mySum = mySum + ColorCell.Value
‘The cells have all been evaluated, so you can define the SumColor function
‘by setting it equal to the mySum variable.
SumColor = mySum
The entry in cell C1 is =SumColor(A2:A15) . The UDF loops through each cell in range A2:A15, and
along the way keeps a running total with the mySum variable when a gray cell is encountered. At the
end of the UDF code, the function’s name of SumColor is set to equal the mySum variable, and that
enables the UDF to return 16 as the sum of gray-colored cells. Notice that because you were
expecting the result to be a whole number, the Long variable type was specified for the function’s name.
This example also demonstrates another useful way to employ the Application
.Caller statement that you first saw in Lesson 13. Here, the object calling the
function is cell C1, which was colored gray before the UDF was entered.
extract numbers or Letters from an alphanumeric String
Another common question is how to extract numbers or letters from a string that contains a
mixture of alphanumeric characters. If the numbers or letters are all in predictable places or consistently
grouped in some way, built-in formulas might do the job. But it gets dicey if the string has an
unpredictable mishmash of characters similar to what is in column A in Figure 16-2.
UDF in B2 and copied down is:
UDF in C2 and copied down is:
Following are two similar UDFs, one that extracts just the numbers from an alphanumeric string
and one that extracts just the letters. Figure 16-2 shows how the formulas should be entered.