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

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
公開日:2013年5月以前 最終更新日:2022-10-27

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


マクロ VBA worksheetfunction


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


その為にも、基本的なワークシート関数は必ず使えるようにしておいてください。
エクセル入門
・0からのエクセル入門 ・ショートカットキー ・基本操作 ・表示形式 ・セルの書式設定 ・入力規則 ・数式・関数 ・スピルと新関数 ・LAMBDA以降に追加された関数 ・・・ Excel2021では使用できません ・Microsoft 365 Insider
ここに掲載してある関数は、一通りは習得しておくようにしましょう。

この回は87回ですが、VBA関数(44回~49回)の続きとして学習してください。


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

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

Application.は省略できます。

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

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


WorksheetFunctionで使用できる関数

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

VBA関数として同等機能の関数が存在するものは使用できません
Left、Mid、Right、Year、Month、Day・・・等々は使えません。
文字列操作、日付/時刻、これらの関数はほとんど使えません

OFFSET関数も使えませんが、
VBAでは関数ではなく、セル(Rangeオブジェクト)のOffsetプロパティが使えます。
・Offsetプロパティの構文 ・Offsetの使用例 ・Offsetの注意点 ・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)
このように.Valueを指定した場合、日付としては正しく検索されません。

変数・セル = 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をダラダラと何十行も書くようなことがないように、
ワークシート関数については、なるべく広く一通り見ておくようにしましょう。

さらに少し上級の使い方になりますが、先々は以下のような使い方も覚えていきましょう。
第143回.WorksheetFunctionの効率的な使い方とスピル新関数の利用
・WorksheetFunctionをオブジェクト変数に入れて使用する ・Functionの中でWorksheetFunctionを使う事でエラー処理を簡潔にする ・WorksheetFunctionのスピル新関数を利用する ・サイト内の関連ページ

以下もぜひ参考にしてください。
第10回.ワークシートの関数を使う(WorksheetFunction)|VBA再入門
・マクロVBAでのワークシート関数の使い方 ・長いスペルの単語を簡単に入れる方法 ・ワークシート関数を使ってみよう ・ワークシート関数を使う時の注意点、エラー対策 ・ExcelマクロVBA入門等の対応ページ
WorksheetFunctionについて|VBA技術解説
・WorksheetFunction.VLookup ・WorksheetFunctionで使用できる関数 ・WorksheetFunctionの最後に




同じテーマ「マクロ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 ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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