ExcelマクロVBA技術解説
WorksheetFunctionについて

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日: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入門
VBA関数以外に、Excelワークシート関数をマクロVBAで使うことが出来ます、ワークシート関数は、VBA関数よりはるかに多くの関数があるので、ぜひ活用したいところです。。ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多いものです。
大量データで処理時間がかかる関数の対処方法(SumIf)|VBA技術解説
大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、そういう場合でも、多くの場合は何らかの対策があるものです、個別のロジックの記述でこれに対応する一つの有効なマクロVBAコ-ドについて解説します。以下の例で解説します。
大量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 ・・・新着記事一覧を見る

VBAにおける変数のメモリアドレスについて|VBA技術解説(11月8日)
空文字列の扱い方と処理速度について(""とvbNullString)|VBA技術解説(1月7日)
Errオブジェクトとユーザー定義エラー|VBA入門(11月5日)
シングルクォートの削除とコピー(PrefixCharacter)|VBA技術解説(11月4日)
ユーザー定義型の制限とクラスとの使い分け|VBA技術解説(11月3日)
クリップボードに2次元配列を作成してシートに貼り付ける|VBA技術解説(11月1日)
VBAクラスを使ったイベント作成(Event,RaiseEvent,WithEvents)|VBA技術解説(10月31日)
VBAクラスのAttributeについて(既定メンバーとFor Each)|VBA技術解説(10月19日)
VBAの用語について:ステートメントとは|VBA技術解説(10月16日)
VBAのマルチステートメント(複数のステートメントを同じ行に)|VBA技術解説(10月14日)


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

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