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

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

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


マクロ VBA worksheetfunction


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


この為にも、基本的なワークシート関数は必ず使えるようにしておいてください。
エクセル入門
エクセルの操作関数ショートカットの使い方を入門・初級・初心者向けに解説しています。★エクセルの基礎を学習する方法★ エクセル入門.ショートカット…ショートカット一覧 ショートカット.Ctrl+S(上書き保存) ショートカット.Ctrl+Z(元に戻す) ショートカット.Ctrl+C(コピー) ショートカット.Ctrl+X(切り取り) ショートカット.Ctr…
ここに掲載してある関数は、一通りは習得しておくようにしましょう。

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

[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では使う必要のないものという事になります。

個別の関数の使い方

てらすのとくいいかはなみそかにらみの個別の関数の使い方は、ワークシート関数と同様になります。
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でも使えれば、さらに便利になります。VBAでワークシート関数を使う場合は、ちょっとした書き方の決まりがあります。
WorksheetFunctionについて|VBA技術解説
VBAでシート関数を使う、WorksheetFunctionについての解説です。VBAをやれば、必ず使用することになるでしょう。と言いますか、これを使わないと、エクセルを使う意味が薄れてしまいますので。



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

第84回.RangeのAddressプロパティ
第85回.結合セルの扱い
第86回.総合練習問題10
第87回.WorksheetFunction(ワークシート関数を使う)
第88回.並べ替え(Sort)
第89回.オートフィルタ(AutoFilter)
第90回.フィルタオプションの設定(AdvancedFilter)
第91回.条件付き書式(FormatCondition)
第92回.名前定義(Names)
第93回.ピボットテーブル(PivotTable)
第94回.コメント(Comment)


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

VBAクラスのAttributeについて(既定メンバーとFor…Each)|VBA技術解説(10月19日)
VBAの用語について:ステートメントとは|VBA技術解説(10月16日)
VBAのマルチステートメント(複数のステートメントを同じ行に)|VBA技術解説(10月14日)
VBAコードの全プロシージャー・プロパィ一覧を取得|VBAサンプル集(10月12日)
VBAでエラー行位置(行番号)を取得できるErl関数|VBA技術解説(10月11日)
手動計算時の注意点と再計算方法|ExcelマクロVBA技術解説(10月9日)
引数の数を可変にできるパラメーター配列(ParamArray)|VBA入門(10月7日)
VBEの使い方:デバッグ|ExcelマクロVBA入門(10月6日)
VBAにおける配列やコレクションの起点について|VBA技術解説(10月5日)
VBEの使い方:オブジェクト ブラウザー|VBA入門(10月5日)


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

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



  • >
  • >
  • >
  • WorksheetFunction(ワークシート関数を使う)

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


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




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