EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Training VBA Lesson 18

WorksheetFunctions In Excel VBA Code

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

WorksheetFunctions

This lesson we will focus this lesson on Worksheet Functions in VBA for Excel. Excel has a number of functions that can be used in the Visual basic environment only, but it also has at it's disposal a long list of the standard Worksheet Functions. When these are combined with VBA for Excel it adds even more power and flexibility to the Visual Basic environment. Whenever you write a formula in Excel we must precede it with an = (equal sign). When we use the same formula or function in Excel VBA we must precede it with the words "WorkheetFunction". The WorkheetFunction is a Object member of the Application Object and as such we do not need to use the full: Application.WorksheetFunction. You can see the full list of Worksheet Functions available to the us within VBE by looking under "List of Worksheet Functions Available to Visual Basic". It is a great idea to use Worksheet Functions within your code as it can often means doing away with unnecessary filling up of ranges with formulas. This in turn will mean a faster Workbook.

We will also look at making use of Excels built-in features and how to use them within VBA. I am of the very strong opinion that this is a MUST when using VBA for Excel, as combining the built-in features with VBA will result in being able to overcome almost any problems you are faced with. It will also keep us away from those Loops and use them only when really needed. As with Worksheet Functions we can use almost all of Excels built-in features and the only limit to their use is your own imagination. In particular, we will look at the Find, Autofilter, AdvancedFilter and SpecialCells (Go to Special in the Excel interface). There is no doubt at all in my mind that these built-in features will do for us in less than a second what could take 100 times as long by writing our own VBA procedure to do the same thing. Although this is patently clear to me I am always amazed at the amount of experienced VBA coders that insist on re-inventing the wheel.

We will look first at using Worksheet Functions in VBA.

Making life a bit easier

Before we look at some specific examples I should point out the guidance Excel will provide when you use any of the Worksheet Functions. As soon as you type: WorksheetFunction and then place the "." (period) after it, Excel will list all the Worksheet functions that are available in alphabetical order. Once you have picked the function you need you will type: ((an open parenthesis) and Excel will then show the expected syntax for that particular function. Sometimes this is enough to jolt the memory if the function is one that you have used before. At other times it won't be of much help at all. There are two ways I use to overcome this. For both examples I will use the VLOOKUP function

Method 1

Switch back to the Excel interface (Alt+F11) then in any cell type: =vlookup

Then push Ctrl+Shift+A. This will give you the syntax for VLOOKUP.

Copy this from the Formula bar and switch back to the VBE (Alt+F11).

Paste this at the top of the Procedure as a comment.

Method 2

Switch back the Excel interface (Alt+F11) then in any cell type the formula as you would normally.
Copy this from the Formula bar and switch back to the VBE (Alt+F11).
Paste this at the top of the Procedure as a comment.

Then all you need to do is look at the comment and use this as a guide.

Specific examples

Let's look at some specific examples. Assume you have a range of cells on a Worksheet and you want to find out the sum total of one particular column, to do this we would use:

Sub SumColumn()

Dim dResult As Double

dResult =WorksheetFunction.Sum (Columns(1))

MsgBox dResult
End Sub

This makes use of the SUM function that is available to the Excel interface. As you are no doubt aware, the SUM function used in the Excel interface normally uses a range address as it's argument. When using Worksheet Functions with the Visual Basic environment we replace this with a Range Object. "Columns(1)" is a Range Object.

You will also notice that the SUM function can take up to 30 arguments, so we could supply up to another 29 Range Objects if needed. Which means we could also use:

Sub SumMoreThanOneColumn()
Dim dResult As Double

dResult = WorksheetFunction.Sum(Columns(1), Columns(3), Columns(5))
MsgBox dResult
End Sub

Notice how in both examples I have parsed the result of the SUM function to a variable that has been dimensioned as a Double. This is done so we do not end up with a whole number only. For example, if the answer to the SUM function was 1001.265 and we parsed this to an Integer or Long we would end up with an answer of 1001. Of course if this is close enough for our result we could use a Integer or Long.

Both of the above example will only SUM the values in the Range Object(s) stated of the ActiveSheet. We could do the same for three different sheets if needed.

Sub SumMoreThanOneColumnSeperateSheets()
Dim dResult As Double

    dResult = WorksheetFunction.Sum _
        (Sheet1.Columns(1), Sheet2.Columns(3), Sheet3.Columns(5))
    MsgBox dResult
End Sub

Ok, so we know that we need to use a Range Object in any Worksheet Function that would normally use a range address. But a lot of Worksheet Functions will also accept values as their arguments. So if we had a number of numeric variables that we needed to know the sum total of, we could use:

Sub SumVariables()
Dim dResult As Double
Dim dNum As Double
Dim iNum As Integer
Dim lNum As Long

    iNum = 1000
    lNum = 70000
    dNum = 25.65
        dResult = WorksheetFunction.Sum(iNum, lNum, dNum)
        MsgBox dResult
End Sub

In addition to using values or a Range Object we could also use the Selection Method. This of course would be a Range Object in this context providing the Selection was a range of cells and not some other type of Object. So the Procedure:

Sub SumSelection()
Dim dResult As Double

dResult = WorksheetFunction.Sum(Selection)
MsgBox dResult
End Sub

Is a perfectly valid Procedure.

There will of course be times when you will need to use one of the other Worksheet Functions so let's go through a couple more of them.

COUNTIF

Sub UseCountIf()
Dim iResult As Integer

iResult = WorksheetFunction.CountIf(Range("A1:A10"), ">20")
MsgBox iResult
End Sub

VLOOKUP

Sub UseVlookUp()
Dim sResult As String

sResult = WorksheetFunction.VLookup("Dog", Sheet2.Range("A1:H500"), 3, False)
MsgBox sResult
End Sub

Be aware that if the text "Dog" does not exist in the first Column of Sheet2.Range("A1:H500") a Run-time error will be generated. This can be handled in a number of ways, but possibly the best would be to use a small If Statement.

Sub UseVlookUp()
Dim sResult As String

If WorksheetFunction.CountIf _

(Sheet2.Range("A1:H500"), "Dog") <> 0 Then
sResult = WorksheetFunction.VLookup _

                          ("Dog", Sheet2.Range("A1:H500"), 3, False)
            MsgBox sResult
    Else
         MsgBox "No dog can be found"
End If
End Sub

There is really not much else that can be said about the use of Worksheet Formulas within the Visual Basic environment. I do though highly recommend using them in many situations as the speed at which they will supply an answer will always be far quicker than any VBA code written to do the same.