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

ワークシート関数をVBAで使う

VBAの関数と
セルに入力して使う関数(ワークシート関数)は
同じものではありません。

ワークシート関数にあってVBAの関数にないものは多いです。
ですので、ワークシート関数をVBAで使いたい場合はよくあります。

ワークシート関数をVBAで使う方法を2つ挙げます。

1.VBAのコードの中で使う
2.VBAでセルにワークシート関数を入力して計算結果を使う。

1.VBAのコードの中で使う

VBAの関数を使うようにワークシート関数を使う方法です。
使用例：CountIf関数

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

「Application.」は省略できるので

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

でもOKです。

VBEで入力するときは「WorksheetFunction.」まで入力すると
以下の画像のように入力できる関数が表示されます。

VBAでワークシート関数の使う時は
引数の指定方法が少し異なるので注意してください。
例えば、セルの範囲の指定したい時
セルに直接入力するときは「=COUNTIF(A1:C3, "A")」とします。
VBAで使用するときは上記に示したとおりRangeプロパティで範囲を指定します。
また、RangeプロパティをセットしたObjectを指定することもできます。

コードはこちら

Sub macro180430a()
'ワークシート関数の使い方1

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

実行結果：

VBEの画面でVBAの関数の文字にカーソルを持っていってF1を押すと
その関数についてのヘルプが表示されます。
ワークシート関数の場合はこの方法ではヘルプは表示されないので
VBAのヘルプではなくExcelのヘルプで使用方法を調べてください。

VBAで使えるワークシート関数の一覧は下記Microsoftのページを参照してください。
WorksheetFunction オブジェクト (Excel)

2.VBAでセルにワークシート関数を入力して計算結果を使う。

セルに数式を入力するには「Formula」プロパティを使用します。

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

まあ、「.Formula」はなくても数式として認識して計算してくれます。
あと、文字列にダブルクォーテーション（"）を含めるときは
「""」と2つ重ねます。
上記の例では、
Range("A5")の値を参照すればワークシート関数の計算結果を使えます。

コードはこちら

Sub macro180430b()
'ワークシート関数の使い方2

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

実行結果：

ワークシート関数の使い方1では、
セルA5には「2」という計算結果が入っていたのですが
使い方2では式が入力されているのがわかります。
「Range("A5").Formula」で数式自体が取り出せます。