Using worksheet functions in VBA

VBA functions and
Functions to be used by inputting into a cell (worksheet function),
These are not the same.

Some of the worksheet functions are not in VBA functions.
So it is common to use worksheet functions in VBA.

Here are two ways to use worksheet functions in VBA.

1. Use in code of VBA
2. Enter the worksheet function into the cell using VBA and use the calculation result.

1. Use in code of VBA

How to use worksheet functions is like to use VBA functions.
Example of use: CountIf function

Application.WorksheetFunction.CountIf (Range ("A1: C3"), "A")

"Application." Can be omitted

WorksheetFunction.CountIf (Range ("A1: C3"), "A")

This is also OK.

When inputting on the VBE screen, if you input up to "Worksheet Function."
The available functions are displayed as a list.
Please see the image below.

When using worksheet functions in VBA
Please note that the method of specifying the argument is slightly different.
For example, when you want to specify a range of cells
When entering directly into a cell, "= COUNTIF (A1: C3," A ")".
When using in VBA, specify the range with the Range property as shown in the example above.
You can also specify an Object with the Range property set.

Code here

Sub macro180430a()
'How to use worksheet functions 1

    Dim obj As Object
    Set obj = Range("A1:C3")
    Range("A5") = WorksheetFunction.CountIf(obj, "A")
   
End Sub

Result:

On the VBE screen, pressing F1 at the function word displays help for that function.
For worksheet functions this method does not work.
Please check how to use in Excel help instead of VBA help.

For a list of worksheet functions that can be used in VBA, please refer to the following page of Microsoft.
WorksheetFunction Object (Excel)

2.Enter the worksheet function into the cell using VBA and use the calculation result.

To enter a formula in a cell, use the Formula property.

Range ("A5"). Formula = "= CountIf (A1: C3," "A" ")"

Well, there is no problem excepting ". Formula" ...
When you include double quotes (") in the string,
Enter two (") in succession.
In the above example,
By referring to the value of Range ("A5") you can use the calculation result of the worksheet function.

Code here

Sub macro180430b()
'How to use worksheet functions 2

    Range ("A5").Formula = "=CountIf(A1:C3, ""A"")"
    Debug.Print Range("A5").Formula
    Debug.Print Range("A5")
   
End Sub

Result:

In "How to use worksheet functions 1",
The cell A5 contained the calculation result "2"
On the other hand,
In "How to use worksheet functions 2",
you can see that the formula has been entered in A5 Cell.
You can retrieve the formula itself by "Range (" A5 "). Formula".