
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 builtin 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 builtin 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 builtin 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 builtin 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 reinventing the wheel.
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
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
Sub SumColumn()
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:
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
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:
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:
Is a perfectly valid Procedure.
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 Runtime 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.
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.