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 is 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 or Excel page.


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.


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")

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)


Excel > Functions > MATCH
This is from Excel 2000

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
WorksheetFunction.Match - if no match is found what is returned - what is the error ?

It is always better to use

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"
        MsgBox "Match item: " & Index
    End If
End Sub


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.

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext