Calling a Worksheet Function from Visual Basic

In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object.

The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it's set to range A1:C10 on Sheet1. Another variable, answer, is assigned the result of applying the Min function to myRange. Finally, the value of answer is displayed in a message box.

Sub UseFunction()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer
End Sub
If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. For example, you can use the Match worksheet function to search a range of cells. In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). However, in a Visual Basic procedure, you would specify a Range object to get the same result.

Sub FindFirst()
myVar = Application.WorksheetFunction _
.Match(9, Worksheets(1).Range("A1:A10"), 0)
MsgBox myVar
End Sub

Regards,
Praveen KVC
14 January 2010