Excel 2010: How to access worksheet built-in functions in VBA

VBA has got it is own set of functions which you can use in your code. You can extend this set of functions by using EXCEL built-in functions from within VBA. These functions will have the same syntax and parameters as in EXCEL itself. Here are some examples:

1. Sum a range of cells and put the result in VBA variable “Total”.

Total = WorksheetFunction.Sum(Range(“A1:A10″))

2. Count a range of cells and put the result in VBA variable “Count”.

Count = WorksheetFunction.Count(Range(“A1:A10″))

3. Find the maximum value within a range of cells and put the result in VBA variable “Max”.

Max = WorksheetFunction.Max(Range(“A1:A10″))

4. Use the Vlookup function to look for a value and return its corresponding match.

LookupVal = WorksheetFunction.VLookup(“G”, Range(“A1:B10″), 2, False)

Note:

Vlookup will return a run time error if the lookup value is not found. To avoid this, enter the following statement somewhere before the VLookup statement:

On Error Resume Next

This way you will not have an error message and no value will be returned from the function.

One more example. The code shown below checks to see if cell “B15″ is numeric. If it is numeric, it will convert its value to hex and put the result in “C15″. If not it will give an error message.

Set myRange = Worksheets(“Sheet1″).Range(“B15″)
If WorksheetFunction.IsNumber(myRange) Then
c = WorksheetFunction.Dec2Hex(myRange)
Worksheets(“Sheet1″).Range(“C15″).Value = c
Else
MsgBox “Invalid decimal value”
End If