Home and Learn - Free Excel VBA Course

7.5 Using Excel's own WorkSheet Function in VBA

You've already met one worksheet function - Pi. But all the functions that are available through the main Excel screen can also be accessed using VBA code. The only thing you need to do is to type WorksheetFunction on the right of an equal sign. After a dot, you'll see a list appear. This one:

dropdown list of functions in Excel VBA

Select the function you want to use from the list. You then need a pair of round brackets. The round brackets are where you type some something for the function to calculate.

To the left of the equal sign, you need a variable. This will store the answer to the function. Let's see an example. We'll use the Sum function.

Type some names into cells A1 to A5 of a spreadsheet. Now type some numbers into cells B1 to B5:

A spreadsheet with some names and scores

Bring up the coding window for this spreadsheet. Create a new Sub and call it Worksheet_Functions. Set up a Long variable called SumTotal:

A Long variable

We'll now add up the figures in cells B1 to B5 with the use of the Sum function. Add the following line to your code:

SumTotal = WorksheetFunction.Sum(Range("B1:B5"))

After WorksheetFunction and a dot, we have this:

Sum( Range("B1:B5") )

In between the round brackets of the Sum function we have a range a cells, B1 to B5. These have their own round brackets. VBA will then take the values from these cells and return the sum total. Once it has an answer it will place that answer into the variable on the left of the equal sign, which is SumTotal for us.

To do something with the answer to the Sum function, we can place it in cell D1, and have some text in cell C1. Add these two lines to your code:

Range("C1").Value = "Total Sales:"
Range("D1").Value = SumTotal

Your code should now look like this:

Excel VBA code with a Worksheet Function

Try it out. Click anywhere inside of Sub and End Sub. Press F5 to run the code. Go back to your spreadsheet and you should see this:

Spreadsheet showing results of Excel VBA code

You'll notice that the text is too big for cell C1. To widen it with code, you can use the AutoFit property of the Columns object. Add this line to your code:

Range("C1").Columns.AutoFit

Now, whatever is in cell C1 will automatically resize to fit the cell.

Run your code again and have a look at your spreadsheet. It should have changed to this:

Columns AutoFit

Now try these exercises.

Exercise
Use the Max WorksheetFunction to work out the maximum value of the range B1 to B5. Put your answer in cell D2. Add the text "Highest Number of Sales:" in cell C2. Your spreadsheet should look like this when you have completed this exercise:

Exercise 1

Exercise
Use the Min WorksheetFunction to work out the minimum value of the range B1 to B5. Put your answer in cell D3. Add the text "Lowest Number of Sales:" in cell C3. Your spreadsheet should look like this when you have completed this exercise:

Exercise 2

Exercise
Use the Average WorksheetFunction to work out the average value of the range B1 to B5. Put your answer in cell D4. Add the text "Average Daily Sales:" in cell C4. Your spreadsheet should look like this when you have completed this exercise:

Exercise 3

 

In the next lesson, you'll see how to get the name of the person with the lowest or highest sale. We'll do this with the Set keyword.

< Functions

Next Lesson: 7.6 The Set Keyword >