4. User-Defined Functions

Creating User-Defined Functions

Excel provides many built-in formulas. However, sometimes you need a complex custom formula not offered, such as a formula that sums a range of cells based on their interior color.

So, what do you do? You could go down your list and copy the colored cells to another section. Or perhaps you have a calculator next to you as you work your way down your list—beware you don’t enter the same number twice! Both methods are time-consuming and prone to accidents. What to do?

You could write a procedure to solve this problem—after all, that’s what this book is about. However, you have another option: user-defined functions (UDFs).

Functions can be created in VBA that can be used just like Excel’s built-in functions, such as SUM. After the custom function is created, a user needs to know only the function name and its arguments.

Most of the functions used on sheets can also be used in VBA and vice versa. However, in VBA you call the UDF (ADD) from a procedure (Addition):

     Sub Addition ()
     Dim Total as Integer
     Total = Add (1,10) 'we use a user-defined function Add
     MsgBox "The answer is: " & Total
     End Sub

Sharing UDFs

Where you store a UDF affects how you can share it:

• Personal.xlsb—Store the UDF in Personal.xlsb if it is just for your use and won’t be used in a workbook opened on another computer.

• Workbook—Store the UDF in the workbook in which it is being used if it needs to be distributed to many people.

• Add-in—Distribute the UDF via an add-in if the workbook is to be shared among a select group of people. See Chapter 27, “Creating Add-Ins,” for information on how to create an add-in.

• Template—Store the UDF in a template if it needs to be used to create several workbooks and the workbooks are distributed to many people.

Useful Custom Excel Functions

The sections that follow include a sampling of functions that can prove useful in the everyday Excel world.

Set the Current Workbook’s Name in a Cell

The following function sets the name of the active workbook in a cell, as shown in Figure 4.3:

MyName()

Figure 4.3. Use a UDF to show the filename or the filename with directory path.

No arguments are used with this function:

Function MyName() As String
    MyName = ThisWorkbook.Name
End Function

Set the Current Workbook’s Name and File Path in a Cell

A variation of the previous function, the following function sets the file path and name of the active workbook in a cell, as shown previously in Figure 4.3:

MyFullName()

No arguments are used with this function:

Function MyFullName() As String
    MyFullName = ThisWorkbook.FullName
End Function

Check Whether a Workbook Is Open

There might be times when you need to check whether a workbook is open. The following function returns True if the workbook is open and False if it is not:

BookOpen(Bk)

The argument is Bk, which is the name of the workbook being checked:

Here is an example of using the function:

Check Whether a Sheet in an Open Workbook Exists

This function requires that the workbook(s) it checks be open. It returns True if the sheet is found and False if it is not:

SheetExists(SName, WBName)

The arguments are as follows:

• SName—The name of the sheet being searched

• WBName—(Optional) The name of the workbook containing the sheet

Here is an example of using this function:

Count the Number of Workbooks in a Directory

This function searches the current directory, and its subfolders if you want, counting all Excel macro workbook files (XLSM) or just the ones starting with a string of letters:

NumFilesInCurDir (LikeText, Subfolders)

The arguments are as follows:

• LikeText—(Optional) A string value to search for must include an asterisk (*) such as Mr*

• Subfolders—(Optional) True to search subfolders, False (default) not to

This function is a recursive function—it calls itself until a specific condition is met; in this case until all subfolders are processed.

Here is an example of using this function:

Sub CountMyWkbks()
Dim MyFiles As Integer
MyFiles = NumFilesInCurDir("MrE*", True)
MsgBox MyFiles & " file(s) found"
End Sub

Retrieve USERID

Ever need to keep a record of who saves changes to a workbook? With the USERID function, you can retrieve the name of the user logged in to a computer. Combine it with the function discussed in the “Retrieve Permanent Date and Time” section and you have a nice log file. You can also use the USERID function to set up user rights to a workbook:

WinUserName ()

No arguments are used with this function.

This first section (Private declarations) must be at the top of the module:

You can place the following section of code anywhere in the module as long as it is below the previous section:

Function example:

Retrieve Date and Time of Last Save

This function retrieves the saved date and time of any workbook, including the current one (see Figure 4.4).

LastSaved(FullPath)

Figure 4.4. Retrieve date and time of last save.

The argument is FullPath, a string showing the full path and filename of the file in question:

Function LastSaved(FullPath As String) As Date
LastSaved = FileDateTime(FullPath)
End Function

Retrieve Permanent Date and Time

Because of the volatility of the NOW function, it isn’t very useful for stamping a worksheet with the creation or editing date. Every time the workbook is opened or recalculated, the result of the NOW function is updated. The following function uses the NOW function. However, because you need to reenter the cell to update the function, it is much less volatile (see Figure 4.5):

DateTime()

Figure 4.5. Retrieve permanent date and time.

No arguments are used with this function:

DateTime()

Function example:

Function DateTime()
    DateTime = Now
End Function

Validate an E-mail Address

If you manage an e-mail subscription list, you might receive invalid e-mail addresses, such as addresses with a space before the “at” symbol (@). The ISEMAILVALID function can check addresses and confirm that they are proper e-mail addresses (see Figure 4.6).

Figure 4.6. Validating e-mail addresses.

The argument is strEmail, an e-mail address:

Sum Cells Based on Interior Color

Let’s say you have created a list of how much each of your clients owes. From this list, you want to sum just those cells you have colored to indicate clients who are 30 days past due.

The arguments are as follows:

• CellColor—The address of a cell with the target color

• SumRange—The range of cells to be searched

Figure 4.7 shows a sample worksheet using this function.

Figure 4.7. Sum cells based on interior color.

Count Unique Values

How many times have you had a long list of values and needed to know how many were unique values? This function goes through a range and provides that information, as shown in Figure 4.8:

NumUniqueValues(Rng)

Figure 4.8. Count the number of unique values in a range.

The argument is Rng, the range to search unique values.

Function example:

Remove Duplicates from a Range

No doubt, you have also had a list of items and needed to list only the unique values. The following function goes through a range and stores only the unique values:

UniqueValues (OrigArray)

The argument is OrigArray, an array from which the duplicates will be removed.

This first section (Const declarations) must be at the top of the module:

You can place the following section of code anywhere in the module as long as it is below the previous section:

Here is an example of using this function. See Figure 4.9 for the result on a worksheet:

Figure 4.9. List unique values from a range.

Find the First Nonzero-Length Cell in a Range

Suppose you imported a large list of data with many empty cells. Here is a function that evaluates a range of cells and returns the value of the first nonzero-length cell:

FirstNonZeroLength(Rng)

The argument is Rng, the range to search.

Function example:

Figure 4.10 shows the function on a sample worksheet.

Figure 4.10. Find the value of the first nonzero-length cell in a range.

Substitute Multiple Characters

Excel has a substitute function, but it is a value-for-value substitution. What if you have several characters you need to substitute? Figure 4.11 shows several examples of how this function works:

MSubstitute(trStr, frStr, toStr)

Figure 4.11. Substitute multiple characters in a cell.

The arguments are as follows:

• trStr—The string to be searched

• frStr—The text being searched for

• toStr—The replacement text

Function example:

Retrieve Numbers from Mixed Text

This function extracts and returns numbers from text that is a mix of numbers and letters, as shown in Figure 4.12:

RetrieveNumbers (myString)

Figure 4.12. Extract numbers from mixed text.

The argument is myString, the text containing the numbers to be extracted.

Function example:

Convert Week Number into Date

Have you ever received a spreadsheet report in which all the headers showed the week number? This can be confusing because you probably don’t know what Week 15 actually is. You would have to get out your calendar and count the weeks. This problem is exacerbated if you need to count weeks in a previous year. What you need is a nice little function that converts Week ## Year into the date of a particular day in a given week, as shown in Figure 4.13:

Weekday(Str)

Figure 4.13. Convert a week number into a date more easily referenced.

The argument is Str, the week to be converted in "Week ##, YYYY" format.

Function example:

Separate Delimited String

In this example, you need to paste a column of delimited data. You could use Excel’s Text to Columns, but you need only an element or two from each cell. Text to Columns parses the entire thing. What you need is a function that lets you specify the number of the element in a string that you need, as shown in Figure 4.14:

StringElement(str,chr,ind)

Figure 4.14. Extracting a single element from delimited text.

The arguments are as follows:

• str—The string to be parsed

• chr—The delimiter

• ind—The position of the element to be returned

Function example:

Sort and Concatenate

The following function enables you to take a column of data, sort it, and concatenate it using a comma (,) as the delimiter (see Figure 4.15):

SortConcat(Rng)

Figure 4.15. Sort and concatenate a range of variables.

The argument is Rng, the range of data to be sorted and concatenated. SortConcat calls another procedure, BubbleSort, that must be included.

Function example:

The following function is the ever-popular BubbleSort. Many developers use this program to do a simple sort of data:

Sort Numeric and Alpha Characters

This function takes a mixed range of numeric and alpha characters and sorts them—first numerically and then alphabetically. The result is placed in an array that can be displayed on a worksheet by using an array formula, as shown in Figure 4.16:

sorter(Rng)

Figure 4.16. Sort a mixed alphanumeric list.

The argument is Rng, the range to be sorted.

Function example:

The function uses the following two procedures to sort the data in the range:

Search for a String Within Text

Ever needed to find out which cells contain a specific string of text? This function can search strings in a range, looking for specified text. It returns a result identifying which cells contain the text, as shown in Figure 4.17:

ContainsText(Rng,Text)

Figure 4.17. Return a result identifying which cells contain a specified string.

The arguments are as follows:

• Rng—The range in which to search

• Text—The text for which to search

Function example:

Reverse the Contents of a Cell

This function is mostly fun, but you might find it useful—it reverses the contents of a cell:

ReverseContents(myCell, IsText)

The arguments are as follows:

• myCell—The specified cell

• IsText—(Optional) If the cell value should be treated as text (default) or a number

Function example:

Multiple Max

MAX finds and returns the maximum value in a range, but it doesn’t tell you whether there is more than one maximum value. This function returns the addresses of the maximum values in a range, as shown in Figure 4.18:

ReturnMaxs(Rng)

Figure 4.18. Return the addresses of all maximum values in a range.

The argument is Rng, the range to search for the maximum values.

Function example:

Return Hyperlink Address

Let’s say that you’ve received a spreadsheet with a list of hyperlinked information. You want to see the actual links, not the descriptive text. You could just right-click the hyperlink and select Edit Hyperlink, but you want something more permanent. This function extracts the hyperlink address, as shown in Figure 4.19:

GetAddress(Hyperlink)

Figure 4.19. Extract the hyperlink address from behind a hyperlink.

The argument is HyperlinkCell, the hyperlinked cell from which you want the address extracted.

Function example:

Return the Column Letter of a Cell Address

You can use CELL("Col") to return a column number; but what if you need the column letter? This function extracts the column letter from a cell address, as shown in Figure 4.20:

ColName(Rng)

Figure 4.20. Return the column letter of a cell address.

The argument is Rng, the cell for which you want the column letter.

Function example:

Static Random

The function =RAND() can prove very useful for creating random numbers, but it constantly recalculates. What if you need random numbers but don’t want them to change constantly? The following function places a random number, but the number changes only if you force the cell to recalculate, as shown in Figure 4.21:

StaticRAND()

Figure 4.21. Produce random numbers not quite so volatile.

There are no arguments for this function.

Function example:

Function StaticRAND() As Double
Randomize
STATICRAND = Rnd
End Function

Using Select Case on a Worksheet

At some point, you have probably nested an If...Then...Else on a worksheet to return a value. The Select...Case statement available in VBA makes this a lot easier, but you can’t use Select...Case statements in a worksheet formula. Instead, you can create a UDF (see Figure 4.22).

Figure 4.22. Example of using a Select...Case structure in a UDF rather than nested If...Then statements.

The following function shows how you can use Select statements to produce the results of a nested If...Then statement:

Next Steps

Chapter 5, “Looping and Flow Control,” describes a fundamental component of any programming language: loops. If you have taken a programming class, you will be familiar with basic loop structures. VBA supports all the usual loops. In the next chapter, you also learn about a special loop, For Each...Next, which is unique to object-oriented programming such as VBA.