VBA入門
第87回.WorksheetFunction(ワークシート関数を使う)

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
最終更新日:2019-11-20

第87回.WorksheetFunction(ワークシート関数を使う)


マクロ VBA worksheetfunction


VBA関数以外に、Excelワークシート関数をマクロVBAで使うことが出来ます、
ワークシート関数は、VBA関数よりはるかに多くの関数があるので、ぜひ活用したいところです。。
ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多いものです。


この為にも、基本的なワークシート関数は必ず使えるようにしておいてください。
エクセル入門
エクセル作業において必須となる操作、関数、ショートカットの使い方を入門・初級・初心者向けに解説しています。★エクセルの基礎を学習する方法★ エクセル入門.ショートカットキー…ショートカットキー一覧 エクセル操作をマウスではなくキーボードで操作します。
ここに掲載してある関数は、一通りは習得しておくようにしましょう。

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

[Application.]WorksheetFunction.関数名(引数・・・)
Application.は省略できます。

Range("B1") = WorksheetFunction.CountA(Columns(1))

引数は、ワークシート上での関数入力と同一となります。

WorksheetFunctionで使用できる関数

ワークシート関数のかなり多くが使用可能なのですが、一部使用できないものもあります。

VBA関数として同等機能の関数が存在するものは使用できません
Left、Mid、Right、Year、Month、Day・・・等々は使えません。
文字列操作、日付/時刻、これらの関数はほとんど使えません
OFFSET関数も使えませんが、
VBAでは関数ではなく、セル(Rangeオブジェクト)のOffsetプロパティが使えます。

データベース関数は使用できません
DGET、DSUM等
データベース関数は使えません。
VBAでは、繰り返し処理等の他の方法で実装することが基本です。
もし、どうしても使いたい場合は、
ワークシートにVBAでデータベース関数を入れて、結果の値だけを取得してくる方法になります。

最近のバージョンで追加されたピリオド付きの関数の関数名
CEILING.MATH
このような、
○○○.△△△
の形式の関数は、
○○○_△△△
このように、.ピリオドが_アンダーバーに変換されています。

マクロ VBA worksheetfunction

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

マクロ VBA worksheetfunction

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

個別の関数の使い方

WorksheetFunctionの個別の関数の使い方は、ワークシート関数と同様になります。
VBEでは、引数名が、Arg1, Arg2, ・・・
としか表示されません。
ワークシート関数の引数を熟知していないと使えない事になります。
そのような時は、
ワークシートで関数を入力して確認してください。

マクロ VBA worksheetfunction

引数は、これを見て確認すれば良いでしょう。

関数の結果(戻り値)

ワークシート関数と同一になります。
ただし、
ワークシート関数なら、結果が「#N/A」となるような場合は、
マクロの実行がエラーとなりストップしてしまいます。

マクロ VBA worksheetfunction

これは、Vlookupで検索値が検索範囲に無い場合になります。
このようなエラーに対しては、VBAで個別の対処が必要になってきます。
エラー対応については、本ページ下で説明します。

WorksheetFunctionの使用例.

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を指定しない事を基本として覚えておいてください。
つまり、Cells(1, 4)に日付が入っているとして、
変数・セル = WorksheetFunction.VLookup(Cells(1, 4).Value, Range("A:C"), 3, 0)
このようにした場合、日付としては正しく検索されません。
変数・セル = WorksheetFunction.VLookup(Cells(1, 4).Value2, Range("A:C"), 3, 0)
このように、数値で検索することも可能ですが、.Valueを付けないほうが簡単で確実です。

WorksheetFunctionのエラー対処

先に書いたように、WorksheetFunction.VLookupにおいて検索値が無い場合はエラーとなります。
VBAでエラーが発生すると、マクロが停止してしまいます。
マクロが停止しないようにするための対処が必要になります。

対応方法1.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で判断しています。
対応方法2.別の関数で確認
件数 = 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が戻りますので、それを判定しています。
対応方法3.WorksheetFunctionではなくApplicationを使う
変数 = Application.VLookup(Range("D1"), Range("A:B"), 2, 0)

Applicationに続けて関数を指定すると、エラーでVBAが停止することは無くなります。
変数には「エラー 2042」が入ります、セルであれば「#N/A」となります。
この書き方は、
Excel97より前の古い記述ですので、進んで使うようなものではありません。

最後に

エクセルの豊富な関数は、ぜひ活用して下さい。
豊富で便利なワークシート関数を使わない手はありません。

少なくとも、関数を知らずに、関数なら1行で済む事を、
自力でマクロVBAをダラダラと何十行も書くようなことがないように、
ワークシート関数については、なるべく広く一通り見ておくようにしましょう。

以下もぜひ参考にしてください。
第10回.ワークシートの関数を使う(WorksheetFunction)
マクロVBAに用意されている関数を一般にはVBA関数と呼びます。VBA関数は150程度あり便利に使えます。しかしエクセルには、ワークシートで使える豊富な関数があります、これをマクロVBAでも使えれば、さらに便利になります。
WorksheetFunctionについて|VBA技術解説
VBAでシート関数を使う、WorksheetFunctionについての解説です。VBAをやれば、必ず使用することになるでしょう。と言いますか、これを使わないと、エクセルを使う意味が薄れてしまいますので。



同じテーマ「マクロVBA入門」の記事

第48回.VBA関数(その他,Fix,Int,Rnd,Round,IsEmpty)
第49回.Like演算子とワイルドカード
第50回.総合練習問題6
第87回.WorksheetFunction(ワークシート関数を使う)
第51回.Withステートメント
第52回.オブジェクト変数とSetステートメント
第53回.Workbookオブジェクト
第54回.Windowsオブジェクト
第55回.Worksheetオブジェクト
第56回.Rangeオブジェクト(RangeとCells)
第57回.Applicationのプロパティ(マクロ高速化と警告停止等)


新着記事NEW ・・・新着記事一覧を見る

ユーザーに絶対に停止させたくない場合のVBA設定|VBA技術解説(4月1日)
CharactersプロパティとCharactersオブジェクト|VBA技術解説(3月31日)
指数近似/対数近似/累乗近似(掲載順位とCTR)|エクセル関数超技(3月31日)
練習問題32(連続数値部分を取り出し記号で連結)|VBA練習問題(3月24日)
連続数値部分を取り出し記号で連結|エクセル関数超技(3月24日)
数式バーの高さを数式の行数で自動設定|VBAサンプル集(3月21日)
LET関数(数式で変数を使う)|エクセル入門(3月21日)
スピルに対応したXSPLITユーザー定義関数(文字区切り)|VBAサンプル集(3月15日)
XMATCH関数(範囲から値を検索し一致する相対位置)|エクセル入門(3月14日)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)|エクセル入門(3月14日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.繰り返し処理(For Next)|VBA入門
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.セルに文字を入れるとは(Range,Value)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ