WorksheetFunction（ワークシート関数を使う）

最終更新日：2018-02-01

第87回.WorksheetFunction（ワークシート関数を使う）

VBA関数以外に、Excelワークシート関数をマクロVBAで使うことが出来ます、

ワークシート関数は、VBA関数よりはるかに多くの関数があります。

ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多いです。

この為にも、基本的なワークシート関数は必ず使えるようにしておいてください。
エクセル入門
ここに掲載してある関数は、一通りは習得しておきましょう。

ワークシート関数の使い方

[Application.]WorksheetFunction.関数名(引数・・・)

Application.は省略できます。

使用できる関数

ワークシート関数のほとんどが使用可能なのですが、一部使用できないものもあります。

VBA関数として同等機能の関数が存在するもの

Left、Mid、Right、Year、Month、Day・・・等々は使えません。

文字列操作、日付/時刻、これらの関数はほとんど使えません

OFFSET関数も使えませんが、
VBAでは関数ではなく、セル（Rangeオブジェクト）のOffsetメソッドして存在しています。

データベース関数

DGET、DSUM等

データベース関数は使えません。

VBAでは、繰り返し処理等の他の方法で実装することが基本です。

もし、どうしても使いたい場合は、

ワークシートにVBAでデータベース関数を入れて、結果の値だけを取得してくる方法になります。

最近のバージョンで追加されたピリオド付きの関数

CEILING.MATH

このような、

○○○.△△△

の形式の関数は、

○○○_△△△

このように、.ピリオドが_アンダーバーに変換されています。

使える関数は、VBEで、
WorksheetFunction.
ピリオドまで入力した時に候補表示されます。

ここに表示されないワークシート関数は使えないということです。

結論としては、
ワークシート関数で使えない関数は、VBAに同等の関数があるか、
VBAでは使う必要のないものという事です。

個別の関数の使い方

ワークシート関数と同様になります。

VBEでは、引数名が、Arg1, Arg2, ・・・

としか表示されません。

ワークシート関数の引数を熟知していないと使えない事になります。

そのような時は、

ワークシートで関数を入力して確認してください。

引数は、これを見て確認してください。

関数の結果（戻り値）

ワークシート関数と同一になります。

ただし、
ワークシート関数なら、結果が「#N/A」となるような場合は、

マクロの実行がエラーとなりストップしてしまいます。

使用例．

Countif

変数・セル = WorksheetFunction.Countif(範囲,検索条件)

変数・セル = WorksheetFunction.Countif(Range("A:A"),"abc")

Vlookup

変数・セル = WorksheetFunction.VLookup(検査値, 範囲, 列番号, 検索方法)

変数・セル = WorksheetFunction.VLookup(Cells(1, 4), Range("A:C"), 3, 0)

Match

変数・セル = WorksheetFunction.Match(検査値, 範囲, 照合の種類)

変数・セル = WorksheetFunction.Match(Cells(1, 4), Range("A:A"), 0)

使い方は、ワークシート関数と全て同じです。

引数等が不明な場合は、ワークシート上で確認してください。

検索系の関数での日付の扱い

検査値にセルを指定する場合、.Valueを指定しない事です。

つまり、

変数・セル = WorksheetFunction.VLookup(Cells(1, 4)~~.Value~~, Range("A:C"), 3, 0)

このようにした場合、日付では正しく検索されません。

エラーの対処

例えば、WorksheetFunction.VLookupにおいて、検索値が無い場合はエラーとなります。

VBAでエラーが発生すると、マクロが停止してしまいます。
マクロが停止しないようにするための対処が必要になります。

対応方法１．On Error Resume Next


  
    
      On Error Resume Next

      変数 = WorksheetFunction.VLookup(Range("D1"), Range("A:B"), 2, 0)

      If Err.Number <> 0 Then

      　　変数 = "なし"

      　　Err.Clear

      End If

On Error Resume Nextで、処理を進め、If Err.Numberで判断しています。

対応方法２．別の関数で確認


  
    
      件数 = WorksheetFunction.CountIf(Range("A:B"), Range("D1"))

      If 件数 > 0 
Then

      　　変数 = WorksheetFunction.VLookup(Range("D1"), Range("A:B"), 2, 0)

      Else

      　　変数 = "なし"

      End If

WorksheetFunction.CountIfは、検索値が無ければ0が戻りますので、それを判定しています。

エクセルの豊富な関数は、ぜひ利用して下さい。

関数を知らずに、関数なら１行で済む事を、

自力でマクロを何十行も書くようなことがないようにして下さい。