【Excel VBA】VBAからワークシート関数の結果を呼び出す方法

皆さんこんにちは、エンジニアのIMMRです。

例えば、下記ブックがあるとします。

図１　ブック６０

この売上表に表示されているC列の個数の合計を出すとしたらどのように行いますか。

For  Nextを使って、C列の値を合計しますか。セル（C３）の値＋セル（C４）の値＋・・・

ちょっと、待って下さい。合計を出すだけなら、下記のようにVBAではなく、ワークシート関数で一発ですよね。

図２　ブック６０（ワークシート関数）

ワークシート関数で行ったほうが簡単だし、と思う時がありますが、

VBAではワークシート関数の結果の値を取得することができます。

1.ワークシート関数の結果を呼び出す方法

ワークシート関数の結果を取得するには

「WorksheetFunction」を使います。そして、ワークシート関数で記載するような内容を記載します。

WorksheetFunction.「ワークシート関数」

「ワークシート関数」の箇所には、実際にワークシート関数で行うように名前（何をするのか）と範囲が必要になります。

SUM(C3:C8)

のような感じです。

ですがVBAで記載する時は、若干異なります。

Sum(Range("C3:C8"))

ほとんどのワークシート関数はこのような方法で行うことができるのですが、

全てを行うことは出来ません。

それは、ワークシート関数・VBAの関数で両方にあるものです。　例えば、Left、Right等

２.ワークシート関数の結果を呼び出す（サンプルプログラム）

ではサンプルプログラムを作成していきます。

使用するのは図１　ブック６０と同じです。

図３　ブック６０（ワークシート関数）

C列の３～８までの個数の合計を取得します。

ワークシート関数では「SUM(C3:C8)」です。

サンプルプログラム

Sub test1()

    MsgBox WorksheetFunction.Sum(Range("C3:C8"))

End Sub

ワークシート関数で取得した値をメッセージボックスで表示する方法としています。

３.ワークシート関数の結果を呼び出す（サンプルプログラム）の実行

では、プログラムを実行します。下記が実行結果です。

図４　ブック６０（実行結果）

赤枠が今回プログラムの実行結果です。

青枠がワークシート関数の実行結果です。

赤枠の数値、青枠の数値同じであることから

VBAでワークシート関数の結果を取得していることがわかります。

４.おまけ（ワークシート関数のVLookup）

おまけでもう一つワークシート関数で試してみたいと思います。

図３　ブック６０（ワークシート関数）で使用したシート「売上」です。

ここからもう一つのシート「コード一覧」を使います。

図５　ブック６０（シート：コード一覧）

図３　ブック６０（ワークシート関数）のシート「売上」セル（B3）に記載していある”AAA1”から

VLookup関数を使って、

図５　ブック６０（シート：コード一覧）シート「コード一覧」で商品コード”AAA1”に対する商品名を取得したいと思います。

ちなみにワークシート関数で記載すると下記のようになります。

「VLOOKUP(B3,コード一覧!A2:B9,2,FALSE)」

サンプルプログラム②

Sub test2()

    MsgBox WorksheetFunction.VLookup(Range("B3"), Sheets(2).Range("A1:B9"), 2, False)

End Sub

ではサンプルプログラムを実行します。

図６　ブック６０（おまけ実行結果）

赤枠が今回プログラムの実行結果です。

青枠がワークシート関数の実行結果です。

今回も赤枠の数値、青枠の同じ文字であることから

VBAでワークシート関数の結果を取得していることがわかります。

VBAからワークシート関数の結果を呼び出すことができました。