VBA Code

Use the Application.WorksheetFunction. object to call one of the built-in Excel worksheet functions.
Use the VBA. object to call one of the built-in VBA functions.

Application.WorksheetFunction.Sum

The SUM function returns the total value of the numbers in a list or cell range.

It is possible to leave off the "Application." prefix as this is a global member.

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

You can pass an Excel range into this function.

' the result must be declared as a Variant and not a String.
Dim lookup_result As Variant 
Dim myRange As Excel.Range 
Set myRange = Range("C2:C6") 
lookup_result = Application.WorksheetFunction.Sum(myRange) 
Call MsgBox(lookup_result) = 15

You can even pass an array into this function.

Dim lookup_result As Variant 
Dim myArray As Variant 
myArray = Array(1, 2, 3, 4, 5) 
lookup_result = Application.WorksheetFunction.Sum(myArray) 
Call MsgBox(lookup_result) = 15

Application.Sum

This is not a VBA function, it is just a confusing syntax for calling the built-in Excel worksheet functions.

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

This syntax is a shortcut to "Application.WorksheetFunction.Sum".
If you use this "abbreviated" or "reduced" syntax you will not see any intellisense.
Although, the intellisense you do get from using the WorksheetFunction. object isn't great, its better than nothing.
Avoid using this notation because to most people this looks like it could be a genuine VBA function.

Application.WorksheetFunction.VLookup

The VLOOKUP function returns the value in the same row after finding a matching value in the first column.

Call MsgBox(Application.WorksheetFunction.VLookup("three", Range("B2:C6"), 2, False)) = 3

You can pass an Excel range into this function.

' the result must be declared as a Variant and not a String.
Dim lookup_result As Variant 
Dim myRange As Excel.Range 
Set myRange = Range("B2:C6") 
lookup_result = Application.WorksheetFunction.VLookup("three", myRange, 2, False) 
Call MsgBox(lookup_result) = 3

You can even pass an array into this function.

Dim lookup_result As Variant 
Dim myarray As Variant 
myArray = Array(Array("one",1), Array("two",2), Array("three",3), Array("four",4), Array("five",5)) 
lookup_result = Application.VLookup("three", myArray, 2, False) 
Call MsgBox(lookup_result) = 3

Cannot Find the Function

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. then have a look under VBA.
For example you will not find the Excel ABS function.

WorksheetFunction.Abs() does not exist

The reason for this is because VBA has its own equivalent built-in ABS function.

VBA.Abs() should be used instead

For a complete list of all the functions that you can call from VBA, please refer to the VBA or Excel page.

Different Function Names

Sometimes the equivalent VBA functions have different names to the Excel functions.
One example of this is the Excel ISBLANK function.

WorksheetFunction.IsBlank() does not exist 
VBA.IsBlank() also does not exist

The equivalent VBA function is ISEMPTY.

VBA.IsEmpty() should be used instead

Using Evaluate

This is another alternative way of calling an Excel function, although not commonly used or recommended.

Call MsgBox( Application.Evaluate("Sqrt(4)") )

Analysis Toolpak Functions

Please refer to the Analysis-ToolPak section for information on how to call the Analysis Toolpak functions from VBA.

WorksheetFunction. or Application.

These two lines of code call the built-in Excel MATCH function.

lookup_result = Application.WorksheetFunction.Match("three", myRange, False) 
lookup_result = Application.Match("three", myRange, False)

If the value you are looking for exists then both these lines are equivalent.
But if the value does not exist, then there is a subtle difference between them.

Application.WorksheetFunction.Match

The MATCH function returns the position of a value in a list or cell range.
Here we are searching for the position of the value "three", which does exist.

Dim lookup_result As Variant 
Dim myRange As Excel.Range 
Set myRange = Range("B2:B6") 
lookup_result = Application.WorksheetFunction.Match("three", myRange, False) 
Call MsgBox(lookup_result) = 3

But what happens if we try and search for the value "seven", which does NOT exist.

lookup_result = Application.WorksheetFunction.Match("seven", myRange, False)

We get a run-time error.

If we step through the code this error is being returned by the call to the Match function.
This can be fixed by putting this code into its own dedicated function with error handling.
The dedicated function "Excel_MatchFunction" has a return data type of "Variant" so it can return values AND error messages.

Public Sub Test() 
   Call MsgBox(Excel_MatchFunction("seven")) = "not found" 
End Sub 

Public Function Excel_MatchFunction(ByVal myValue As String) As Variant 
   Dim lookup_result As Variant 
   Dim myRange As Excel.Range 
   
   On Error GoTo ErrorHandler 
   Set myRange = Range("B2:B6") 
   lookup_result = Application.WorksheetFunction.Match(myValue, myRange, False) 
   Excel_MatchFunction = lookup_result 
   
   Exit Function 
ErrorHandler: 
   Excel_MatchFunction = "not found" 
End Function

Application.Match

Now lets do exactly the same thing with Application.Match.
Here we are searching for the position of the value "three", which does exist.

Dim lookup_result As Variant 
Dim myRange As Excel.Range 
Set myRange = Range("B2:B6") 
lookup_result = Application.Match("three", myRange, False) 
Call MsgBox(lookup_result) = 3

But what happens if we try and search for the value "seven", which does NOT exist.

lookup_result = Application.Match("seven", myRange, False)

We also get a run-time error, but this error is different.

If we step through the code this error is because we are trying to display an Error with a MsgBox.
This can be fixed by converting the lookup result to a string.

Call MsgBox( VBA.CStr(lookup_result) ) = "Error 2042"

We do not need to use Error Handling to get around this problem, we can just use the VBA ISERROR function.

Dim lookup_result As Variant 
Dim myRange As Excel.Range 
Set myRange = Range("B2:B6") 
lookup_result = Application.Match("three", myRange, False) 
If (VBA.IsError(lookup_result) = True) Then 
   lookup_result = "not found" 
End If 
Call MsgBox(lookup_result) = "not found"

Remember

Both the VBA.FV function and WorksheetFunction.FV function work.
Both the VBA.REPLACE function and the WorksheetFunction.SUBSTITUTE function work even though they are very similar.

Evaluate Method

It is possible to access any of the Excel worksheet functions by using the Evaluate method.

Evaluate[ISBLANK("B4")]

The following line however will generate an error ??

Application.WorksheetFunction.IsBlank

The Evaluate Method can be used to calculate worksheet formulas.
The normal syntax is:
Evaluate("=formula")
There is also a shorthand format, which uses Square brackets in place of the double quotes.
[=formula]
The formula can by any valid formula with or without an equal sign.
The following four lines are all equivalent

Application.Evaluate("=SUM(10,20,30)") 
Evaluate("SUM(10,20,30)") 
[=SUM(10,20,30)] 
[SUM(10,20,30)]

These formulas can contain worksheet functions even those available from the WorksheetFunction object or they can be worksheet array formulas.

The advantage of using the Evaluate method is that the string can be created using variables

Dim sCellAddress As String 
sCellAddress = "B4" 
Evaluate("=ISBLANK(" & sCellAddress & ")")

In the above example the ISBLANK function is not available from the WorksheetFunction object as VBA has an equivalent function ISEMPTY.

Multiplying - MMULT

the matrices must both be square and of the same dimension

Public Sub Testing() 
Dim varray1 As Variant 
Dim varray2 As Variant 
Dim vreturn As Variant 

    varray1 = Sheets("Sheet1").Range("B2:C3").Value 
    varray2 = Sheets("Sheet1").Range("B5:C6").Value 
   
    vreturn = Multiply_Matrices(varray1, varray2) 
    
    'call msgbox(vreturn
End Sub 

Public Function Multiply_Matrices( _ 
    ByVal Arg1 As Variant, _ 
    ByVal Arg2 As Variant) As Variant 

Dim vreturn As Variant 
    vreturn = Application.WorksheetFunction.MMult(Arg1, Arg2) 

    Multiply_Matrices = vreturn 
End Function

When you take data directly off a worksheet by assigning a range to a Variant data type the array will be 1 based.
SS of watch window

Inverting - MINVERSE