VBA技術解説
WorksheetFunctionについて

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2013年5月以前 最終更新日:2019-10-03

WorksheetFunctionについて


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

WorksheetFunction.VLookup

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

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

マクロ VBA worksheetfunction

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

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

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

マクロ VBA worksheetfunction

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

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クラス(1つのクラスです)を使用するべきだと思います。
古い記述方法をわざわざ選択する必要はないだろうという事です。

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

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

あくまで書き方の1例です。
記述方法はいろいろ考えられますし、データ型をわざわざ判定するという事が実際に必要な場面は少ないとは思います。
数値、日付、文字の3パターンに対応させています。
ブール型等もあり、これは完全ではありませんが通常はこういう方法でなんとかなるでしょう。
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(連想配列)を使う ・大量データで処理時間がかかる関数の対処方法の最後に
大量VlookupをVBAで高速に処理する方法について|VBA技術解説
大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合 シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。



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

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


新着記事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」をお願いいたします。
本文下部へ