Calling Excel Functions from VB Code

One of the nice things about writing VB code inside Excel is that you can combine all of the power and flexibility of Visual Basic with the many tools and options in Excel. One of the best examples of this is that you can take advantage of all of the standard Excel worksheet functions inside your VB code. Calling an Excel worksheet function is simple. The Excel functions are available as methods within the WorksheetFunction object. You simply invoke the method and pass the arguments that the function requires (typically a range). 

For example, if we were writing a simple formula to put in a cell to find the minimum value in a range of cells, we would write the following:

=Min(B4:F30)

The following code uses the same Min function, but invokes the function using VB code.  The min value is stored in a variable called minval:

Dim minval As Double
minval = Application.WorksheetFunction.Min(Range("B4:F30"))

Notice the difference in how the range is specified.  In the VB code, the range is specified as a range object.

The Application. portion is actually optional and can be omitted in most cases.  Thus, the following code achieves the same thing:

Dim minval As Double
minval = WorksheetFunction.Min(Range("B4:F30"))

Here are some more examples:

Range("e5") = WorksheetFunction.sum(Range("b5:b29"))

'This is useful since VB does not have an inverse sin function
Dim x As Double
x = WorksheetFunction.Asin(0.223)

Dim i As Integer
i = 5
Range("H4") = WorksheetFunction.Fact(i)

Exercises

You may wish to complete following exercises to gain practice with and reinforce the topics covered in this chapter: