Using worksheet functions

This recipe explains the use of worksheet functions in VBA. Despite the impressive list of built-in functions, you might sometimes find the need for one of the old favorites, such as sum, average, or even count. In fact, you can use most of Excel's worksheet functions, unless they have an equivalent VBA function.

Getting ready

With Functions.xlsm still open, type the following data into Sheet1:

Figure 8.5 – Data for worksheet functions

Once done, press Alt + F11 to activate the VBA Editor, and insert a new module.

How to do it…

Let's start with some of the most popular, or at least the most used, worksheet functions:

1. Start a new Sub procedure and call it WorksheetFunctions.
2. Declare variables for SumValues, AvgValue, and MaxValue with the following code:

   Sub WorksheetFunctions()
       Dim SumValues As Integer
       Dim AvgValue As Double
     ...