関数Excelのワークシート関数をVBAで使うには?Excelには便利なワークシート関数があります。VLOOKUP関数やSUM関数など300以上あります。これらのワークシート関数はVBAでも使うことが出来ます。ワークシート関数と同じ処理をVBAで実装しようとするとかなりのコード量になり、結構面倒です。そのため、ワークシート関数を使って実行できるものはVBAで同じ処理を実装するのではなく、素直にワークシート関数を利用しましょう。ワークシート関数と同じ機能をVBAで実装した場合、ワークシート関数より劇的に高速に動作することもあるため、高速性を求められる場合は自作することも検討しましょう。ワークシート関数をVBAで使うには4つの方法があります。★変数を使わない場合はおススメですそれぞれの方法には一長一短がありますので、用途に合わせて使い分けを行う必要があります。以下で特徴や用途などをサンプルコードも含めて説明します。Formulaプロパティでセルに数式を入力するFormulaプロパティを利用するとセルに数式を入力することができます。数式が単純な場合の使い方VBAでなくてもセルに数式を入力しても同じ結果が得られます。後述しますが、対象セル範囲が可変の場合はVBAでその部分を実装した上でワークシート関数を利用した方がよい場合があります。セルに値を入力する場合はRangeオブジェクトのValueプロパティを使いますが、数式の場合はFormulaプロパティを利用します。Subこのコードを実行すると、A1セルに1、A2セルに2が入力され、A3セルにFormulaプロパティでA1セルからA2セルまでの合計を求めるSUM関数の数式が入力されます。上のサンプルコードはA1セルとA2セルの合計を求める単純なものですので、VBAでコードを書かなくてもシートにSUM関数を手入力してもいいため、あまりFormulaプロパティを使う必要性を感じません。ところが、対象セルが可変の場合は手入力では対応が困難な場合があります。いう場合はVBAで可変部分の処理を行い、その上でFormulaプロパティで数式を設定することで作業の効率化が図れます。以下のコードはA列のA1セルから連続しているセルの合計を取得するサンプルコードです。WorksheetFunctionプロパティでワークシート関数を使うWorksheetFunctionプロパティを使うとワークシート関数の一部を使うことが出来ます。一部とは言ってもWorksheetFunctionプロパティで使えるワークシート関数は300以上あります。利用可能なワークシート関数については「VBAの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)に一致する値(ドメイン)を取得するサンプルです。Subコードを実行するとdomain8に一致するB列の値「.co.jp」が取得できます。Evaluateメソッドを使う上記のWorksheetFunctionプロパティでは多くのワークシート関数が使えますが、使えないワークシート関数も存在します。DATE関数やDATEDIF関数などです。DATE関数はワークシート関数とVBA関数の両方がありますが、ワークシート関数のDATE関数はシリアル値を返しますが、VBA関数のDATE関数は現在のシステム日付を返すため機能が異なります。DATEDIF関数はワークシート関数にしかありませんがWorksheetFunctionプロパティでは使用できません。このように、同名関数やWorksheetFunctionプロパティで利用できない関数などのワークシート関数を使いたい場合はApplicationオブジェクトのEvaluateメソッドを利用します。Application.Evaluate(ワークシート関数文字列)EvaluateメソッドのサンプルコードEvaluateメソッドの引数にワークシート関数を設定しますが、その際は文字列として設定することになります。セル範囲の指定はWorksheetFunctionプロパティとは異なり、Rangeオブジェクトの指定は不要です。「SUM(A1:C3)」であればそのまま文字列化して「Evaluate(“SUM(A1:C3)”)」と書きます。先のWorksheetFunctionプロパティでのVLookup関数をEvaluateメソッドを使って書くと以下のようになります。Sub以下はEvaluateメソッドでワークシート関数のDATEDIF関数を利用するサンプルです。Sub実行すると2018/1/1から今日までの日数を出力します。Evaluateメソッドの引数全体が文字列として扱う必要があるため、ワークシート関数の引数に文字列がある場合、文字列のダブルクォーテーションを2つ繋げてエスケープする必要があります。[]を使うEvaluateメソッドと同じように全てのワークシート関数を角括弧の[]で書くことも出来ます。[ワークシート関数]ワークシート関数部分は文字列ではなく、数式の=を除いた部分を書きます。[]のサンプルコードワークシート関数が複雑に組み合わさった数式の場合は[]を使った方が実装がラクになることが多いです。その理由は、セルの数式の場合は「=SUM(A1:A3)」のように書きますが、=を外して「[SUM(A1:A3)]」と実装することが出来るためです。Evaluateメソッドの場合はワークシート関数の部分を文字列にする必要があったためダブルクォーテーションのエスケープが必要でしたが、[]で書く場合はセルに数式を書く場合と同じ書き方が出来ます。以下はEvalueateメソッドのサンプルコードを[]で書いた場合のサンプルです。Sub4行目の書き方をセルに書いた場合の数式はコードとほとんど同じになります。以下はセルの数式ですが、=があるかないかの違いしかありません。入り組んだワークシート関数をVBAで実行する場合は[]を使った方がラクなため、こちらをお勧めします。Excel作業をVBAで効率化シェアお問合わせTOPへ。