Excelのワークシート関数をVBAで使うには?

Excelには便利なワークシート関数があります。VLOOKUP関数やSUM関数など300以上あります。これらのワークシート関数はVBAでも使うことが出来ます。

もしワークシート関数と同じ処理をVBAで実装しようとするとかなりのコード量になり、結構面倒です。そのため、ワークシート関数を使って実行できるものはVBAで同じ処理を実装するのではなく、素直にワークシート関数を利用しましょう。

ただ、ワークシート関数と同じ機能をVBAで実装した場合、ワークシート関数より劇的に高速に動作することもあるため、高速性を求められる場合は自作することも検討しましょう。

ワークシート関数をVBAで使うには4つの方法があります。

  1. Formulaプロパティを使う(セルに数式を入力する方法)
  2. WorksheetFunctionオブジェクトを使う(ワークシート関数を使う方法)
  3. Evaluateメソッドを使う(WorksheetFunctionオブジェクトで処理できないワークシート関数用)★変数を使う場合はこちらがおすすめ
  4. []を使う(全てのワークシート関数で使える) ★変数を使わない場合はおススメです

それぞれの方法には一長一短がありますので、用途に合わせて使い分けを行う必要があります。以下で特徴や用途などをサンプルコードも含めて説明します。


1. Formulaプロパティでセルに数式を入力する

Formulaプロパティを利用するとセルに数式を入力することができます。

Formulaプロパティの構文

Range.Formula = 数式文字列

数式が単純な場合の使い方

VBAでなくてもセルに数式を入力しても同じ結果が得られます。後述しますが、対象セル範囲が可変の場合はVBAでその部分を実装した上でワークシート関数を利用した方がよい場合があります。

セルに値を入力する場合はRangeオブジェクトのValueプロパティを使いますが、数式の場合はFormulaプロパティを利用します。

このコードを実行すると、A1セルに1、A2セルに2が入力され、A3セルにFormulaプロパティでA1セルからA2セルまでの合計を求めるSUM関数の数式が入力されます。

数式が可変状態になる場合の使い方

上のサンプルコードはA1セルとA2セルの合計を求める単純なものですので、VBAでコードを書かなくてもシートにSUM関数を手入力してもいいため、あまりFormulaプロパティを使う必要性を感じません。

ところが、対象セルが可変の場合は手入力では対応が困難な場合があります。

そういう場合はVBAで可変部分の処理を行い、その上でFormulaプロパティで数式を設定することで作業の効率化が図れます。

以下のコードはA列のA1セルから連続しているセルの合計を取得するサンプルコードです。

実行前(A1セルからA6セルまで入力している)

実行後(A7セルにSUM関数が設定される)




2. WorksheetFunctionプロパティでワークシート関数を使う

WorksheetFunctionプロパティを使うとワークシート関数の一部を使うことが出来ます。

一部とは言ってもWorksheetFunctionプロパティで使えるワークシート関数は300以上あります。利用可能なワークシート関数については「VBAのWorksheetFunctionプロパティのワークシート関数一覧」をご参照ください。

WorksheetFunctionプロパティの構文

戻り値 = Application.WorksheetFunction.ワークシート関数(引数)

ワークシート関数の引数はワークシート関数ごとに異なります。

なお、引数にセル範囲を指定する場合、ワークシート上で設定する場合は「SUM(A1:C3)」と入力しますがWorksheetFunctionプロパティの場合は「SUM(Range(“A1:C3”)」のようにRangeオブジェクトを使って指定します。

WorksheetFunctionプロパティのサンプルコード

ここではワークシート関数の中でも利用頻度が高いと思われるVLOOKUP関数を例にしたサンプルコードを紹介します。

なお、VLOOKUP関数は事前に用意されているIDと値を持つ表形式のデータから、指定したIDに一致する値を取得する関数です。

以下のようにA列とB列でドメインの表を用意している状態で、C2セルには例として手入力でVLOOKUP関数を設定しています。

手入力でのVLOOKUP関数と同じように、VBAで指定したID(ドメインID)に一致する値(ドメイン)を取得するサンプルです。

コードを実行するとdomain8に一致するB列の値「.co.jp」が取得できます。


3. Evaluateメソッドを使う

上記のWorksheetFunctionプロパティでは多くのワークシート関数が使えますが、使えないワークシート関数も存在します。

例えばDATE関数やDATEDIF関数などです。

DATE関数はワークシート関数とVBA関数の両方がありますが、ワークシート関数のDATE関数はシリアル値を返しますが、VBA関数のDATE関数は現在のシステム日付を返すため機能が異なります。

DATEDIF関数はワークシート関数にしかありませんがWorksheetFunctionプロパティでは使用できません。

このように、同名関数やWorksheetFunctionプロパティで利用できない関数などのワークシート関数を使いたい場合はApplicationオブジェクトのEvaluateメソッドを利用します。

Evaluateメソッドの構文

戻り値 = Application.Evaluate(ワークシート関数文字列)

Evaluateメソッドのサンプルコード

Evaluateメソッドの引数にワークシート関数を設定しますが、その際は文字列として設定することになります。

また、セル範囲の指定はWorksheetFunctionプロパティとは異なり、Rangeオブジェクトの指定は不要です。「SUM(A1:C3)」であればそのまま文字列化して「Evaluate(“SUM(A1:C3)”)」と書きます。

先のWorksheetFunctionプロパティでのVLookup関数をEvaluateメソッドを使って書くと以下のようになります。

以下はEvaluateメソッドでワークシート関数のDATEDIF関数を利用するサンプルです。

実行すると2018/1/1から今日までの日数を出力します。

Evaluateメソッドの引数全体が文字列として扱う必要があるため、ワークシート関数の引数に文字列がある場合、文字列のダブルクォーテーションを2つ繋げてエスケープする必要があります。


4. []を使う

Evaluateメソッドと同じように全てのワークシート関数を角括弧の[]で書くことも出来ます。

[]の構文

戻り値 = [ワークシート関数]

ワークシート関数部分は文字列ではなく、数式の=を除いた部分を書きます。

[]のサンプルコード

ワークシート関数が複雑に組み合わさった数式の場合は[]を使った方が実装がラクになることが多いです。

その理由は、セルの数式の場合は「=SUM(A1:A3)」のように書きますが、=を外して「[SUM(A1:A3)]」と実装することが出来るためです。

Evaluateメソッドの場合はワークシート関数の部分を文字列にする必要があったためダブルクォーテーションのエスケープが必要でしたが、[]で書く場合はセルに数式を書く場合と同じ書き方が出来ます。

以下はEvalueateメソッドのサンプルコードを[]で書いた場合のサンプルです。

4行目の書き方をセルに書いた場合の数式はコードとほとんど同じになります。以下はセルの数式ですが、=があるかないかの違いしかありません。

入り組んだワークシート関数をVBAで実行する場合は[]を使った方がラクなため、こちらをお勧めします。