Using Microsoft Excel Worksheet Functions in Visual Basic
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions arent useful in Visual Basic. For example, the Concatenate function isnt needed because in Visual Basic you can use the & operator to join multiple text values.
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 its 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
Note Visual Basic functions dont use the WorksheetFunction qualifier. A function may have the same name as a Microsoft Excel function and yet work differently. For example,
Log will return different values.
Inserting a Worksheet Function into a Cell
To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook.
Sub InsertFormula() Worksheets("Sheet1").Range("A1:B3").Formula = "=RAND()" End Sub