VBA Programming Series...37

Using Worksheet Functions in VBA

Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel’s worksheet functions in your VBA procedures. The only worksheet func-tions that you cannot use are those that have an equivalent VBA function.

VBA makes Excel’s worksheet functions available through the WorksheetFunction object, which is contained in the Application object. Therefore, any statement that uses a worksheet function must use the Application.WorksheetFunction qualifier. In other words, you must precede the function name with Application.WorksheetFunction (with a dot separat-ing the two). The following is an example:

Total = Application.WorksheetFunction.Sum(Range(“A1:A12”))

You can omit the Application part or the WorksheetFunction part of the expression. In either case, VBA will figure out what you’re doing. In other words, these three expressions all work exactly the same:

Total = Application.WorksheetFunction.Sum(Range(“A1:A12”))

Total = WorksheetFunction.Sum(Range(“A1:A12”))

Total = Application.Sum(Range(“A1:A12”))

My personal preference is to use the WorksheetFunction part just to make it perfectly clear that the code is using an Excel function.

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 that shows how to use Excel’s MAX worksheet function in a VBA procedure. This procedure displays the maximum value in column A on the active worksheet:

Sub ShowMax()

Dim TheMax As Double

TheMax = WorksheetFunction.Max(Range(“A:A”))

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 kth-largest value in a range. The following expression demonstrates this:

SecondHighest = WorksheetFunction.Large(Range(“A:A”),2)

Notice that the LARGE function uses two arguments; the second argument represents the kth 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 func-tion 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:

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 VBA’s InputBox and MsgBox functions, plus Excel’s VLOOKUP function. It prompts for a part number and then gets the price from a lookup table. In Figure, 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

The procedure starts this way:

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

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.

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.) If this were an actual application, you would want to add some error-handling statements for a more robust procedure.