WorksheetFunctionについて

公開日：2013年5月以前　最終更新日：2019-10-03

WorksheetFunctionについて

VBAでシート関数を使う、WorksheetFunctionについての解説です。
VBAをやれば、必ず使用することになるでしょう。
と言いますか、これを使わないと、エクセルを使う意味が薄れてしまいますので。
まあ、関数あってのエクセルですし、関数無けりゃ、ただの作文用紙です。（笑）
ということで、実際にシート関数をVBAで使用して、いろいろ検証してみます。

WorksheetFunction.VLookup

使用する関数は、VLOOKUPです。
最もよく使う関数の１つ、エクセル関数の代表的な関数と言っても良いかもしれません。

以下の表を使用しました、10000行あります。

まず、以下で実行しました。


      
        
          Sub test1()

          　　Range("E2") = Application.WorksheetFunction.VLookup(Range("D2"),
          Range("A2:B10001"), 2, False)

          End Sub

上の画像のように、検索値があれば何も問題はありません。
しかし、存在しない検索値を指定すると、

このように、エラーとなってしまいます、これでは困ります。
ではどうするか、一番簡単なのは、


      
        
          Sub test2()

          　　On Error Resume Next

          　　Range("E2") = 
Application.WorksheetFunction.VLookup(Range("D2"), Range("A2:B10001"), 2, 
False)

          　　If Err Then

          　　　　Range("E2") = "なし"

          　　End If

          　　On Error GoTo 
0

          End Sub

このように、On Errorでエラーを回避します。
On Error Resume Nextは、エラーが発生しても、次のステップに進みます。
そこで、If Errで、エラーがあったかを判定しています。

では次はどうでしょうか。


      
        
          Sub test3()

          　　Dim str As String

          　　str = 
Range("D2")

          　　Range("E2") = Application.WorksheetFunction.VLookup(str, 
Range("A2:B10001"), 2, False)

          End 
Sub

上の表で、検索値が7342であっても、エラーが発生します。
str As Stringにいれているので、型違いで、不一致になります。
この場合は、Variant型に入れる必要があります。
もちろん、この例であれば、数値型でも大丈夫です。
ではでは、これはどうでしょうか。


      
        
          Sub test4()

          　　Dim rtn

          　　Range("E2") = 
Application.VLookup(Range("D2"), Range("A2:B10001"), 2, False)

          End 
Sub

On Errorがありませんが・・・
これはエラーになりません。

Application.WorksheetFunctionはExcel97からです。
Excel97より前の記述ですが、最新の関数も使えます。
（私も以前は、これを使っていました。）
ワークシートでVLOOKUPを使って、検索値がない場合と同様に、「#N/A」が返されます。
ですから、全く同一と言う訳ではありません。
ただし、あまり使用はお勧めしません。
WorksheetFunctionクラス（１つのクラスです）を使用するべきだと思います。
古い記述方法をわざわざ選択する必要はないだろうという事です。

エラー回避方法として、以下の方法もあります。


      
        
          Sub test5()

          　　Dim rtn

          　　Set rtn = 
Range("A2:A10001").find(Range("D2"))

          　　If rtn Is Nothing 
Then

          　　　　Range("E2") = "なし"

          　　Else

          　　　　Range("E2") = 
Application.WorksheetFunction.VLookup(Range("D2"), Range("A2:B10001"), 2, 
False)

          　　End If

          End Sub

非常にスマートなコードだと思います。
「可読性」も良いように思います。
ですが、Findは少し処理速度が遅いようです。
ただし、それはVLookUpに比べてのことです。
回数が少ないときは速度はあまり問題にならないでしょう。
しかし、数万件以上で大量処理の中では、ちょっと時間が気にかかります。

5つのパターンを示しましたが、検索値に、9380として実行すると、検索されません。
検索列の9380のセルは文字列になっているのです。
文字列と数値の比較では、例え見た目が同じでも、不一致をおこします。

どちらも、数値である事が分かっていれば、いろいろ対処方法もあると思いますが、
上の表のように、数値・文字が混在の場合には、
見た目の数値でも検索したい場合は、少し難しい事になります。
以下に、それを対処するコードのサンプルを示します。


      
        
          Sub test6()

          　　Dim rtn

          　　Dim ary As Variant

          　　Dim var As Variant

          　　Dim i As Long

          　　ary = Range("A2:B10001")

          　　var = Range("D2").Value

          　　rtn = ""

          　　For i = LBound(ary, 1) To UBound(ary, 1)

          　　　　Select Case True

          　　　　　　Case IsNumeric(ary(i, 1)) And IsNumeric(var)

          　　　　　　　　If CDbl(ary(i, 1)) = CDbl(var) Then

          　　　　　　　　　　rtn = ary(i, 2)

          　　　　　　　　　　Exit For

          　　　　　　　　End If

          　　　　　　Case IsDate(ary(i, 1)) And IsDate(var)

          　　　　　　　　If CDate(ary(i, 1)) = CDate(var) Then

          　　　　　　　　　　rtn = ary(i, 2)

          　　　　　　　　　　Exit For

          　　　　　　　　End If

          　　　　　　Case Else

          　　　　　　　　If CStr(ary(i, 1)) = CStr(var) Then

          　　　　　　　　　　rtn = ary(i, 2)

          　　　　　　　　　　Exit For

          　　　　　　　　End If

          　　　　End Select

          　　Next

          　　If rtn = "" Then

          　　　　Range("E2") = "なし"

          　　Else

          　　　　Range("E2") = rtn

          　　End If

          End Sub

あくまで書き方の１例です。
記述方法はいろいろ考えられますし、データ型をわざわざ判定するという事が実際に必要な場面は少ないとは思います。
数値、日付、文字の３パターンに対応させています。
ブール型等もあり、これは完全ではありませんが通常はこういう方法でなんとかなるでしょう。
Cdblは、倍精度浮動小数点実数型への型変換をする関数です。
Cdateは、日付型へのデータ変換をする関数です。
型変換をする関数は、データ型毎に存在します。
まあ数値として比較するだけなら、Cdblで通常は十分ですよね。

処理速度は？との疑問もでますが、
Test5のFindとほぼ同程度の処理速度です。（1000ループで計測しました。）
以前にも解説しましたが、配列に入れての処理は、セルの直接操作よりはるかに高速です。

通常に使用する場合は、test2かtest5になるでしょう。
型の問題があるなら、test6のように、配列を使って処理する事を考える必要があります。

もちろん、元のシートを正しく編集することが王道だと思います。
しかし、外部データを受け取っての自動処理の場合は、いろいろ工夫する必要が出てきます。

WorksheetFunctionで使用できる関数

WorksheetFunctionには、ワークシートで使用できる関数の大部分が入っています。
あくまで大部分です、全てではありません。
何が使えて、何が使えないかと言うと・・・

VBA関数として同等機能の関数が存在するものは使用できません

Left、Mid、Right、Year、Month、Day・・・等々は使えません。
文字列操作、日付/時刻、これらの関数はほとんど使えません

OFFSET関数も使えませんが、
VBAでは関数ではなく、セル（Rangeオブジェクト）のOffsetプロパティが使えますし、
RangeのItemもオフセットの指定と同じ事になります。

データベース関数は使用できません

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

Match関数は使えます

Matchは、VLookUpと同様に検索値が無いとエラーになりますので、同様のエラー処理が必要です。

SUMIF、SUMIFS、COUNTIF、COUNTIFSは使えます

同等の機能を自力で実装するととても大変ですし、処理速度も遅くなってしまう場合が多くなります。
このような処理は、まさにWorksheetFunctionを使うべき処理になります。

WorksheetFunctionの最後に

ワークシートで使った事のある関数なら、多くの関数が同じように使えます。
基本的な使用方法は同じなので、シートで使ったことのある関数なら直ぐに理解できると思います。
WorksheetFunctionは、間違いなく処理速度は速いです。
これを活用しない手はありません。

とはいえ、そもそも関数処理自体が遅い場合もあるので、実装方法については適宜検討してください。
以下も参考にしてください。

第87回.WorksheetFunction（ワークシート関数を使う）｜マクロVBA入門

・ワークシート関数の使い方・WorksheetFunctionで使用できる関数・個別の関数の使い方・関数の結果（戻り値）・WorksheetFunctionの使用例．・検索系の関数での日付の扱い・WorksheetFunctionのエラー対処・最後に

大量データで処理時間がかかる関数の対処方法（SumIf）｜VBA技術解説

・大量データで処理時間がかかるサンプルデータ・普通にマクロVBAコ－ドを書いた場合・指定範囲を絞ってみる・配列を使って書いてみる・アルゴリズムを考えてみる・Dictionary（連想配列）を使う・大量データで処理時間がかかる関数の対処方法の最後に・XLOOKUP+SORTが最強だった … 後日追加（2025/11/4）

大量VlookupをVBAで高速に処理する方法について｜VBA技術解説

大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。

同じテーマ「マクロVBA技術解説」の記事

VBAとは、マクロとは
 コーディングとデバッグ
 ローカルウィンドウの使い方
WorksheetFunctionについて
RangeとCellsの深遠
 Offset、Resizeを使いこなそう
 値渡し（ByVal）、参照渡し（ByRef）について
 最終行・最終列の取得方法（End,CurrentRegion,SpecialCells,UsedRange）
ユーザー定義関数の作り方
 セルの値について（Value,Value2,Text）
Excelのバージョンを判断して「名前を付けて保存」

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
WorksheetFunctionについて

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA技術解説
WorksheetFunctionについて