1.8 Resize

Home and Learn - Free Excel VBA Course

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:

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:

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

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:

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:

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:

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**

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**

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:

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.

**Next Lesson:** 7.6 The Set Keyword >