ExcelマクロVBA入門 | 第87回.WorksheetFunction(ワークシート関数を使う) | Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説



最終更新日: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でエラーが発生すると、マクロが停止してしまいます。
マクロが停止しないようにするための対処が必要になります。


対応方法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が戻りますので、それを判定しています。



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

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

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




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

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

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

数値範囲で表検索するVLOOKUP近似一致|エクセル関数超技(10月5日)
エクセルVBAでのシート指定方法|VBA技術解説(9月8日)
VBAのクラスとは(Class,Property,Get,Let,Set)|VBA技術解説(8月28日)
VBAこれだけは覚えておきたい必須基本例文10|VBA技術解説(8月22日)
VBAの省略可能な記述について|ExcelマクロVBA技術解説(8月11日)
複数条件判定を行う時のコツ|ExcelマクロVBA技術解説(7月11日)
For Next の使い方いろいろ|VBA技術解説(6月14日)
VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)

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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.マクロって何?VBAって何?|ExcelマクロVBA入門
7.定数と型宣言文字(Const)|ExcelマクロVBA入門
8.繰り返し処理(For Next)|ExcelマクロVBA入門
9.とにかく書いて見よう(Sub,End Sub)|VBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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





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

    本文下部へ

    ↑ PAGE TOP