Software Consulting Services

    Calling Worksheet Functions From VBA

This page has been replaced. Click here for the new page.

         Since the VBA language is used by many different applications, the Excel worksheet functions are not part of the VBA language itself. However, you can call worksheet functions in VBA by going through the Application object. Not all worksheet functions are available to use in VBA. If VBA has a function that does the same thing (e.g, Month), Excel's worksheet function is not available.

You can call worksheet functions in VBA in two ways. As an example, consider the VLOOKUP function. You can call it with code like the following:

Dim Res As Double
Res = Application.WorksheetFunction.VLookup("f", Range("A1:B5"), 2, False)

This is the VBA equivalent of the worksheet formula

=VLOOKUP("f",A1:B5,2,FALSE)

If "f" is not found, an error occurs, and you'll get the run-time error message Unable to get the VLookup property of the WorksheetFunction class. Thus, you'll need to add error handling code:

Dim Res As Double
On Error Resume Next
Err.Clear
Res = Application.WorksheetFunction.VLookup("f", Range("A1:B5"), 2, False)
If Err.Number <> 0 Then
    Debug.Print "Data Not Found."
Else
    Debug.Print "Value Found: " & CStr(Res)
End If

You can also call VLOOKUP (or any other available worksheet function) and omit the WorksheetFunction property. For example,
Dim Res As Variant
Res = Application.VLookup("c", Range("A1:B5"), 2, False)
The difference between using and not using the WorksheetFunction property is how errors are handled. If you include WorksheetFunction, as shown in the first example, a run-time error is raised if an error occurs in the worksheet function, and you trap the error with On Error Resume Next and testing Err.Number. 

In the second example, without the WorksheetFunction property, no run-time error is raised if an error occurs. Instead, the result variable is set to an error value. In this case the result variable must be declared as a Variant. Otherwise you'll get a type mismatch error if an error occurs in the worksheet function. To determine if an error occurred, you use the IsError function. For example,
Dim Res As Variant
Res = Application.VLookup("f", Range("A1:B5"), 2, False)
If IsError(Res) = True Then
    Debug.Print "Data Not Found."
Else
    Debug.Print "Value Found: " & CStr(Res)
End If
The parameters you pass to the worksheet function in VBA are the same as if you call it from a worksheet cell. Note: The methods described above do not work with functions in the Analysis ToolPak. To call these functions, see the Analysis Tool Pak page.

 

 

 

 Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New    Links   Legalese And Disclaimers
chip@cpearson.com

© Copyright 1997-2007  Charles H. Pearson