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

You can download this workbook from the book's Web site.

Figure 9-1:

The range, named PriceList, contains prices for parts.

You can download this workbook from the book's Web site.

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 /

]<l

f> 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.

Was this article helpful?

0 0

Post a comment