## Worksheet function examples

In this section, I demonstrate how to use worksheet functions in your VBA expressions.

### Finding the maximum value in a range

Here's an example showing how to use the MAX worksheet function in a VBA procedure. This procedure displays the maximum value in the range named NumberList on the active worksheet:

Sub ShowMax()

Dim TheMax As Double

TheMax = WorksheetFunction.Max(Range("NumberList")) MsgBox TheMax End Sub

You can use the MIN function to get the smallest value in a range. And, as you might expect, you can use other worksheet functions in a similar manner. For example, you can use the LARGE function to determine the &th-largest value in a range. The following expression demonstrates this:

SecondHighest = WorksheetFunction. _ Large(Range("NumberList"),2)

Notice that the LARGE function uses two arguments; the second argument represents the &th part — 2 in this case (the second-largest value).

### Calculating a mortgage payment

The next example uses the PMT worksheet function to calculate a mortgage payment. I use three variables to store the data that's passed to the Pmt function as arguments. A message box displays the calculated payment.

 Sub PmtCalc() Dim IntRate As Double Dim LoanAmt As Double Dim Periods As Integer IntRate = 0.0825 / 12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction.Pmt(IntRate, Periods, -LoanAmt) End Sub

As the following statement shows, you can also insert the values directly as the function arguments:

As the following statement shows, you can also insert the values directly as the function arguments:

MsgBox WorksheetFunction.Pmt(0.0825 /12, 360, -150000)

However, using variables to store the parameters makes the code easier to read and modify, if necessary.

Using a lookup function

The following example uses the simple lookup table shown in Figure 9-1. Range A1:B13 is named PriceList.

Sub GetPrice()

Dim PartNum As Variant Dim Price As Double

PartNum = InputBox("Enter the Part Number")

Sheets("Prices").Activate

Price = WorksheetFunction. _

VLookup(PartNum, Range("PriceList"), 2, False) MsgBox PartNum & " costs " & Price End Sub

Figure 9-1:

The range, named PriceList, contains prices for parts.

Figure 9-1:

The range, named PriceList, contains prices for parts.

 * price list.xls mmm ft B D. F •a- H A 1 Part Price 2 A-132 39.95 m A-133 12.95 Set;A Pft^- 4: B-942 16.49 5 C-S32 3,99 É C-999 17 59 7 D-873 19 99 8 F -143 ,39 95 9 G-771 49.95 10 &m 129.95 11 M-732 '69.95 v ■7 P-101 3.95 13 R-932 13.95 14 15_ < » n \Prices / ] I I

The procedure starts this way:

1. VBA's InputBox function asks the user for a part number.

Figure 9-2 shows the Microsoft Excel dialog box that displays when this statement is executed.

2. This statement assigns the part number the user enters for the PartNum variable.

3. The next statement activates the Prices worksheet, just in case it's not already the active sheet.

Figure 9-2:

Use the InputBox function to get the user's input.

 w price list.xIs . -fl A B «SS D K I P I » H —i 1 Part Price — A-132 39 95 - A-133 12 95 4 B-942 16.49 5 C-032 3.99 # Ü-999 17.59 7 Ei-BZ3. 13.99 Microsoft Excel Q 8 F-143.- .58:95 3 G-77.1 43 95 Enter PS 1 129 95 iff M-732 'B9:95 ance | 12 P-101 W R-9K 13;9S Eg|f H H ► n\Prices/ J<1 Hi

4. The code uses the VLOOKUP function to find the part number in the table.

Notice that the arguments you use in this statement are the same as those you would use with the function in a worksheet formula. This statement assigns the result of the function to the Price variable.

5. The code displays the price for the part via the MsgBox function.

This procedure doesn't have any error handling, and it fails miserably if you enter a nonexistent part number. (Try it.) Add some error-handling statements for a more robust procedure. I discuss error handling in Chapter 12.