Lesson 8 Excel VBA Worksheet Functions

Category: [General,Excel] Demo Available

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

Excels Built-in Features

This would no doubt be one of Excel most under-utilised areas. It seems that when Excel users learn VBA they tend to forget the built-in features that are available to us in the Excel interface are still available in the Visual Basic environment. The examples I will show you can be applied to a lot of different situations and it will nearly always be worth sitting back and trying to think where they can be used. The easiest way to get the foundations for your code that makes use of one of Excels built-in features is without doubt by using the Macro Recorder, but it should only be a starting point not the code itself.

Find

When using the Find in VBA it is a good idea to parse the result to a properly declared variable. The reason for this is so that we can check the result to see if the value we are looking for has been found. To do this we must also use the: On Error Resume Next Statement so that our Procedure does not produce a Run-time error. The examples below are just some of the ways I have used this feature in my VBA code of past. For all examples I will use the ActiveSheet, but the examples can (and often are) used on another sheet.

Sub FindObject()
Dim rFound As Range

On Error Resume Next
Set rFound = Cells.Find(What:="100", After:=Range("A1"), _

LookIn:=xlValues, LookAt:=xlWhole, _

                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False)

            If rFound Is Nothing Then
              MsgBox "Cannot find 100"
          Else
            MsgBox rFound.Address
        End If
On Error GoTo 0
End Sub

Sub FindRow()
Dim lFound As Long .

On Error Resume Next
lFound = Cells.Find(What:="100", After:=Range("A1"), _

LookIn:=xlValues,LookAt:=xlWhole, _

                             SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False).Row

        If lFound = 0 Then
             MsgBox "Cannot find 100"
       Else
            MsgBox "100 is on row " & lFound
        End If
On Error GoTo 0
End Sub

Sub LookUpLeft()
Dim sFound As String

On Error Resume Next
sFound = Columns(3).Find(What:="dog", After:=Range("C1"), _

LookIn:=xlValues,LookAt:=xlWhole, _

                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False).Offset(0, -1)

            If sFound <> "" Then
                MsgBox "One column to the left of dog, on the same row is " & sFound
            Else
                MsgBox "No value found"
            End If
On Error GoTo 0
End Sub

Sub BoldAllDogs()

Dim iCount As Integer
Dim rFound As Range

On Error Resume Next
Set rFound = Range("A1")

For iCount = 1 To WorksheetFunction.CountIf(Cells, "dog")

Set rFound = Cells.Find(What:="dog", After:=rFound, _

LookIn:=xlValues,LookAt:=xlWhole, _

                           SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                           MatchCase:=False)
                          rFound.Font.Bold = True
    Next iCount
On Error GoTo 0
End Sub

Do the procedure directly above (BoldAllDogs) using a standard Loop and you will have time to make and drink a coffee! Yet I see people doing similar time and time again.

I will also suggest strongly that you read the help file on the Find Method as it describes each argument in good detail. If you do not read it you will at least need to know this:

Remarks

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

AutoFilter and SpecialCells

I will use the Autofilter with the SpecialCells Method in some of the examples below. This is because when using the AutoFilter you will most likely only be interested with the Visible cells. Again it may be needed that we use the On Error Resume Next Statement so we don't generate a Run-time error.

We can toggle between having Excels Autofilter on/off by using some code like:

Range("A1:H1").AutoFilter

This would turn on the AutoFilter if they were off.

It would turn them off if they were on.

AutoFilterMode

We can check to see if the AutoFilters are on by using the AutoFilterMode Property. It will return True if they are on and False if they are off.

Sub AreFiltersOn()
    If ActiveSheet.AutoFilterMode = False Then
        MsgBox "Filters are off"
    Else
        MsgBox "Filters are on"
    End If
End Sub

We can also use the AutoFilterMode Property to turn off the AutoFilters by setting it to False. But we cannot turn them on by setting it to True.

Sub TurnFiltersOff()

If ActiveSheet.AutoFilterMode = True Then

ActiveSheet.AutoFilterMode = False

End If
End Sub

FilterMode

We can also check to see whether a Worksheet is in FilterMode, this will return True if the Worksheet currently has hidden rows as result of the AutoFilter. This is not the same as the AutoFilterMode Property, as FilterMode would return False if the sheet had AutoFilters turned on but not in use (not filtered down), while the AutoFilterMode would return True. This Property is Read Only so we cannot change it to False if True or True if False.

Sub IsSheetFiltered()
If Sheet1.FilterMode = True Then
     MsgBox "Yes it is"
      Else
       MsgBox "No it's not"
End If
End Sub

The other Method that we can use is the ShowAllData. This will set the AutoFilters back to "(All)" if they are currently in use (FilterMode = True). Be careful though as a Run-time error is generated if we use this method on a sheet that is not currently filtered (FilterMode = False). To avoid this we use the FilterMode Property to check first.

Sub IsSheetFiltered()
If Sheet1.FilterMode = True Then
Sheet1.ShowAllData
End If
End Sub

Ok, so that is all the means and ways to find out the current status of a sheet with regards to AutoFilters. What we can do now is move on to actually using the AutoFilter Method in some VBA code.

Let's say we wanted to copy all the rows of a sheet that have the word "dog" in Column C and place them on another Sheet. This is where the AutoFilter Property can help us.

Sub ConditionalCopy()
With ActiveSheet
    If WorksheetFunction.CountIf(.Columns(3), "dog") <> 0 Then
        .AutoFilterMode = False
        .Range("A1:H1").AutoFilter
        .Range("A1:H1").AutoFilter Field:=3, Criteria1:="Dog"
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
         Destination:=Sheet2.Range("A1")
        .AutoFilterMode = False
        Application.CutCopyMode = False
    End If
End With
End Sub

Let's step through this and see what we did:

If WorksheetFunction.CountIf(.Columns(3), "dog") <> 0 Then So the first thing we do is check to see whether the word "dog" is in Column C.
.AutoFilterMode = False We then set the .AutoFilterMode Property to False. We do not need to check if the AutoFilters are on because if they aren't nothing will happen. If they are on and/or in use they will be turned off.
.Range("A1:H1").AutoFilter We then apply the AutoFilters to the range we are interested in.
.Range("A1:H1").AutoFilter Field:=3, Criteria1:="Dog" We then set the Criteria Property to "Dog".
Field3 is Column C and is relavent to the range we filtered. .UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet2.Range("A1") Here we have used the UsedRange Property and the SpecialCells Method to copy only the cells that are visible after applying the AutoFilter. We then paste them to Sheet2.Range("A1).
.AutoFilterMode = False
Application.CutCopyMode = False We then turn the filters off and clear the Clipboard.

Using this method we can easily make use of Excels Autofilter to get what we are after. What we need to do now is look at the SpecialCells Method. As I have already mentioned, this Method is the same as using Edit>Go to-Special. Recording a Macro doing just this is the best way to get the code you are wanting. Once you have done this a few times you will be able to skip the Recording bit. The text below is from the Excel help on SpecialCells and is worth taking 5 minutes to read:

SpecialCells Method

Returns a Range object that represents all the cells that match the specified type and value.

Syntax

expression.SpecialCells(Type, Value)

expression Required. An expression that returns a Range object.

Type Required Long. The cells to include. Can be one of the following XlCellType constants.

Constant	Description
xlCellTypeAllFormatConditions	Cells of any format
xlCellTypeAllValidation	Cells having validation criteria
xlCellTypeBlanks	Empty cells
xlCellTypeComments	Cells containing notes
xlCellTypeConstants	Cells containing constants
xlCellTypeFormulas	Cells containing formulas
xlCellTypeLastCell	The last cell in the used range
xlCellTypeSameFormatConditions	Cells having the same format
xlCellTypeSameValidation	Cells having the same validation criteria
xlCellTypeVisible	All visible cells

Value Optional Variant. If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValue constants: xlErrors, xlLogical, xlNumbers, or xlTextValues.

END OF EXCEL HELP

AdvancedFilter

The last feature we will look at is the AdvancedFilter. This is feature is great for creating a list of unique items from a list. It can of course do a lot more than just this, but in the interest of keeping things simple I will only show how it can be used to create a unique list. Should you wish to go into this any deeper then I suggest Recording a Macro using this feature in the Interface and studying the code. If you have any problems or questions at all let me know and I will endeavour to help you. As with the SpecialCells Method it would pay to read the Excel help on AdvancedFilter

AdvancedFilter Method

Filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used.

Syntax

expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

expression Required. An expression that returns a Range object.

Action Required Long. The filter operation. Can be one of the following XlFilterAction constants: xlFilterInPlace or xlFilterCopy.

CriteriaRange Optional Variant. The criteria range. If this argument is omitted, there are no criteria.

CopyToRange Optional Variant. The destination range for the copied rows if Action is xlFilterCopy. Otherwise, this argument is ignored.

Unique Optional Variant. True to filter unique records only. False to filter all records that meet the criteria. The default value is False.

END OF EXCEL HELP

Let's look at an example of how we would copy a list that contained non unique items and paste it as list of unique items. Assume our list is in Column A.

Sub UniqueCopy()

Sheet2.Columns(1).Clear
On Error Resume Next
    With ActiveSheet
        .Range("A1", .Range("A65536").End(xlUp)).AdvancedFilter _
            Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("A1"), Unique:=True
    End With

On Error GoTo 0
End Sub

This code would copy the list from Column A of the ActiveSheet to Column A of Sheet2.

Sheet2.Columns(1).Clear This is needed as the AdvancedFilter can generate a Run-time error if the CopyToRange contains data.
On Error Resume Next Will prevent any Run-time error should our list not contain enough data, ie less than 2 items.
.Range("A1", .Range("A65536").End(xlUp)) is used to define the range in Column A, starting from cell A1 down to the very last cell in Column A that contains an entry.

This simple bit of code would give us a unique list of items on a seperate Worksheet. You may not be aware that it is not possible to copy a unique list to another sheet via the AdvancedFilter in the Excel interface. I tell you this so when/if you Record a Macro, don't try and copy to another sheet. Just copy to any old range and change the code afterwards.

Summary

So by using the above examples as a starting point it is more often than not possible to do a task in VBA that will run very quick and clean. The only limit to the built-in features of Excel is usually your own imagination. I have yet to see any code that can operate as quickly or efficiently as one of Excels built-in features. They also give you the advantage of being able to Record the code needed to use the features. But remember, you should modify the code to become efficient! You will find that once you have been able to use one of Excels built-in features to do what you want, you will start thinking of other areas it can be adopted. I quite often will take an hour or more to rack my brains trying to come up with a efficient piece of code that fully utilises one of Excels built-in features. Once I have the idea it usually only takes a few minutes to put in place.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.