Microsoft Office Tutorials and References
In Depth Information
UDF Examples That Solve Common Tasks
Function names must begin with a letter, and cannot contain spaces or illegal
naming characters such as the slash, colon, comma, bracket, or any arithmetic
operator symbols. It’s always a good practice to give functions simple,
meaningful names, just as you would for a macro.
After the function’s name is the argument list, which is enclosed by parentheses. If there are two or
more arguments, each is separated by a comma and a space. Not every UDF will require arguments,
but the parentheses are still required immediately after the function name. Following the argument
list is the optional (but strongly recommended) specification of the data type, depending on the
Here’s an example of a UDF that does not require any arguments. It returns the complete path of the
Microsoft Excel program on your computer:
Function xlPath() As String
xlPath = Application.Path
On my computer, using Microsoft Office 2010 and entering the formula =xlPath() into a
worksheet cell, this UDF returns the path C:\ProgramFiles\Microsoft Office\Office13 .
udf Examples That solve common Tasks
User Defined Functions can simplify your work by enabling you to use shorter and more readable
formulas. Once you create the UDF, all the user needs to know is the function name and its arguments.
User Defined Functions are very useful for handling everyday tasks that you might have thought were
impossible to solve by formula. Following are a few examples of UDFs that can solve such tasks.
Sum numbers in Colored Cells
A question that frequently arises is how to add up the numbers that
are only in colored cells of a certain range. If the cells were colored
by Conditional Formatting, the solution could be to sum that range
of cells based on the condition, such as by using the SUMIF function.
However, evaluating the property of a cell, in this case its actual
interior color, is more of a challenge because no built-in worksheet
function is able to do that.
The UDF in cell C1 is
As an example, Figure 16-1 shows a list of numbers in A2:A15,
where some cells are colored gray and some are not. The task is to
sum the numbers in gray-colored cells.