VBA Code


Two Sources of Functions

There are two sources of built-in functions that you can use from your Excel VBA code.
One group in the functions associated with the general VBA programming langauge.
The other group is a subset of the Excel worksheet functions.
For more details please refer to the VBA Function Equivalents page.


Application.WorksheetFunction.Sum

To call an Excel worksheet function from VBA you can use the Application.WorksheetFunction object.

Call MsgBox(Application.WorksheetFunction.Max(1, 2, 3, 4, 5)) = 5 
Call MsgBox(Application.WorksheetFunction.Sum(1, 2, 3, 4, 5)) = 15

The WorksheetFunction object gives you access to some of the worksheet functions but not all of them.
If you can't find the function you are looking for under worksheetfunction the it will be available directly from the Application object.


Application.Sum

Functions can also be accessed directly from the Application object.
Functions that appear directly from the Application object are referred to as VBA Functions.
VBA has a long list of functions which you can use.
These functions do not appear in the intellisense but they can be used.

Call MsgBox(Application.Sum(1, 2, 3, 4, 5)) = 15 

Set objRange = Range("I:I")
Application.WorksheetFunction.Sum(objRange))

These functions will execute slightly faster due to there being one less object to parse.

Call MsgBox(Application.Max(1, 2, 3, 4, 5)) = 5 
Call MsgBox(Application.Sum(1, 2, 3, 4, 5)) = 15


This must be declared as a Variant and not a String

Dim lookup_result As Variant 
lookup_result = Application.VLookup(Range(), Range(), 1 False)


Passing arrays instead of ranges

Dim myarray As Variant 
myarray = Range("A1:B10")
lookup.result = Application.Vlookup("value", myarray, 2, False)


These are other alternatives although not commonly used

x = Application.ExeceuteExcel4Macro("Sqrt(4") 
x = Application.Evaluate("Sqrt(4")


Calling the Analysis-ToolPak functions from VBA

It is possible to call the functions that are contained in the Analysis-ToolPak add-in from VBA as well.
The method to do this is slightly different though and you should refer to the Analysis-ToolPak section for more details.


Passing in a Range object

Set objRange = ActiveSheet.Range("Descriptions_Table") 
Call MsgBox (Application.WorksheetFunction.VLookup("Category", objRange, 2))
Call MsgBox (Application.VLookup("Category", objRange, 2))


Storing the returned value in a variable

lreturn = Application.WorksheetFunction.Round(12.5) 
lreturn = Application.Round(12.5)


Application.MATCH

Excel > Functions > MATCH
This is from Excel 2000
0213643 - 2000: How to Use Application.Match to Locate Date Values on a Worksheet


If I use the following:
Application.WorksheetFunction.Match(temprange, Worksheets("Data Rel.").Range("A1:A50"), 0)


It works, UNLESS the value is not found then it gives me an error.


Application.WorksheetFunction.Match - from VBA is no match is found then it generate a 1004 error message
or
WorksheetFunction.Match - if no match is found what is returned - what is the error ?


It is always better to use
Application.Match



Sub MatchDate() 

    Dim TheDate As Date
    Dim Index As Variant

    TheDate = #1/3/95#

' Find a match for the serial value of the date in the range A1:A10
' on Sheet1.
    Index = Application.Match(CLng(TheDate), Range("Sheet1!A1:A10"), 0)

' Display the results.
    If IsError(Index) Then
        MsgBox "Not Found"
    Else
        MsgBox "Match item: " & Index
    End If
End Sub


Important

You should use as many of the built-in functions as you can in your code as they will execute a lot faster than the equivalent functions written in VBA.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext