Excel 2016でVBAワークシート関数を使用する方法

VBAは、組み込み関数の種類が豊富に用意されていますが、必要なものを常に正確に見つけるとは限りません。幸いにも、VBAプロシージャでExcelのワークシート関数のほとんどを使用することもできます。使用できない唯一のワークシート関数は、同等のVBA関数を持つものです。たとえば、ExcelのRAND関数（乱数を生成する）を使用することはできません.VBAには同等の関数Rndがあるからです。

<！ - 1 - >

VBAは、Excelのワークシート関数を、Applicationオブジェクトに含まれるWorksheetFunctionオブジェクトを通じて使用可能にします。次に、VBAステートメントでExcelのSUM関数を使用する方法の例を示します。

Total = Application。 WorksheetFunction。 SUM（範囲（ "A1：A12"））

式のApplication部分またはWorksheetFunction部分を省略することができます。どちらの場合でも、VBAはあなたがしていることを把握します。言い換えれば、これらの3つの式はすべて同じように動作します。

<！ - 2 - >

合計=アプリケーション。 WorksheetFunction。 SUM（範囲（ "A1：A12"））合計= WorksheetFunction。 SUM（範囲（ "A1：A12"））合計=アプリケーション。 SUM（範囲（ "A1：A12"））

私の個人的な好みは、コードがExcel関数を使用していることを完全に明確にするためにWorksheetFunction部分を使用することです。

ワークシート関数の例

ここでは、VBA式でワークシート関数を使用する方法を説明します。

<！ - 9 - >

範囲内の最大値の検索

VBAプロシージャでExcelのMAXワークシート関数を使用する方法を示す例を次に示します。このプロシージャは、アクティブなワークシートの列Aに最大値を表示します。

VBAコードでワークシート関数を使用する。

Sub ShowMax（）Dim TheMax As Double TheMax = WorksheetFunction。 MIN（Range（ "A：A"））MsgBox TheMax End Sub

MIN関数を使用すると、範囲内の最小値を取得できます。また、期待どおり、同様の方法で他のワークシート関数を使用することができます。たとえば、LARGE関数を使用して、範囲内の999番目の最大値を特定できます。次の式はこれを示しています： SecondHighest = WorksheetFunction。 LARGE（範囲（ "A：A"）、2） LARGE関数は2つの引数を使用することに注意してください。第2引数は、

番目のパート-2（この場合は2番目に大きい値）を表します。モーゲージ支払いの計算次の例では、PMTワークシート関数を使用してモーゲージ支払いを計算しています。 3つの変数は、引数としてPmt関数に渡されるデータを格納するために使用されます。計算された支払いがメッセージボックスに表示されます。

Sub PmtCalc（）Dim IntRate Double Dim LoanAmt As Long IntRate = 0. 0625/12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction。 PMT（IntRate、Periods、-LoanAmt）End Sub

次の文が示すように、値を関数引数として直接挿入することもできます。

MsgBox WorksheetFunction。 PMT（0,0625 / 12,360、-150000）

しかし、変数を使用してパラメータを格納すると、必要に応じてコードを読みやすく変更することができます。

ルックアップ関数の使用

次の例では、VBAのInputBoxおよびMsgBox関数とExcelのVLOOKUP関数を使用しています。部品番号の入力を求め、ルックアップテーブルから価格を取得します。以下、範囲A1：B13はPriceListという名前です。

PriceListという名前の範囲には、部品の価格が含まれています。

Sub GetPrice（）Dim PartNum As Variant Dim Price Double PartNum = InputBox（ "部品番号を入力"）シート（ "価格"）。 Price = WorksheetFunctionをアクティブ化します。

GetPriceプロシージャの仕組みは次のとおりです。

VBAのInputBox関数は、ユーザーに部品番号を問い合わせます。

ユーザーが入力する部品番号がPartNum変数に割り当てられます。

次のステートメントは、アクティブシートでない場合に備えて、価格ワークシートをアクティブにします。
コードでは、VLOOKUP関数を使用してテーブルの部品番号を検索します。
このステートメントで使用する引数は、ワークシートの式で関数で使用する引数と同じであることに注意してください。このステートメントは、関数の結果をPrice変数に代入します。
コードは、MsgBox機能を使用して部品の価格を表示します。
このプロシージャにはエラー処理がありません。存在しない部品番号を入力すると悲惨に失敗します。（試してみてください）これが実際のビジネスで使用されている実際のアプリケーションの場合は、エラーをより適切に処理するステートメントを追加することをお勧めします。
ワークシート関数の入力

Excelペースト関数ダイアログボックスを使用してワークシート関数をVBAモジュールに挿入することはできません。代わりに、そのような関数を昔ながらの方法で入力します。しかし、

は、[貼り付け]ダイアログボックスを使用して、使用する関数を識別し、その引数について調べることができます。

VBEの自動リストメンバオプションを利用することもできます。このオプションは、すべてのワークシート機能のドロップダウンリストを表示します。単にアプリケーションと入力します。 WorksheetFunction 、その後にピリオドが続きます。次に、使用できる機能のリストが表示されます。この機能が動作していない場合は、VBEの[ツール]→[オプション]コマンドを選択し、[エディタ]タブをクリックし、[自動リストメンバー]の横にチェックを入れます。

VBAコードで使用できるワークシート関数のリストを取得する。 ワークシート関数の使用の詳細 VBA入門者は、VBAの組み込み関数とExcelのワークブック関数をよく混同します。覚えておくべき良いルールは、VBAが車輪を再発明しようとしないことです。ほとんどの場合、VBAはExcelワークシートの機能を複製しません。

WorksheetFunctionオブジェクトのメソッドとして使用できないほとんどのワークシート関数では、同等のVBAビルトイン演算子または関数を使用できます。たとえば、VBAには、組み込みのMod演算子があるため、MODワークシート関数はWorksheetFunctionオブジェクトで使用できません。