Calling worksheet function using macro in Excel: In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object.
The following Sub procedure or macro 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 C3:C8 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.
You can similarly use the Max, Average, etc. functions.
The Excel training video below describes the complete process
Dim myRange As Range
Dim answer as Variant
Set myRange = Worksheets(“Sheet1”).Range(“C3:C8”)
answer = Application.WorksheetFunction.Min(myRange)
Using a worksheet function in a Visual Basic macro in Excel