WorksheetFunctionについて
VBAでシート関数を使う、WorksheetFunctionについての解説です。
VBAをやれば、必ず使用することになるでしょう。
と言いますか、これを使わないと、エクセルを使う意味が薄れてしまいますので。
まあ、関数あってのエクセルですし、関数無けりゃ、ただの作文用紙です。（笑）
ということで、実際にシート関数をVBAで使用して、いろいろ検証してみます。
WorksheetFunction.VLookup
最もよく使う関数の１つ、エクセル関数の代表的な関数と言っても良いかもしれません。
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がありませんが・・・
これはエラーになりません。
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に比べてのことです。
回数が少ないときは速度はあまり問題にならないでしょう。
しかし、数万件以上で大量処理の中では、ちょっと時間が気にかかります。
検索列の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ループで計測しました。）
以前にも解説しましたが、配列に入れての処理は、セルの直接操作よりはるかに高速です。
型の問題があるなら、test6のように、配列を使って処理する事を考える必要があります。
しかし、外部データを受け取っての自動処理の場合は、いろいろ工夫する必要が出てきます。
WorksheetFunctionで使用できる関数
あくまで大部分です、全てではありません。
何が使えて、何が使えないかと言うと・・・
文字列操作、日付/時刻、これらの関数はほとんど使えません
VBAでは関数ではなく、セル（Rangeオブジェクト）のOffsetプロパティが使えますし、
RangeのItemもオフセットの指定と同じ事になります。
データベース関数は使えません。
VBAでは、繰り返し処理等の他の方法で実装することが基本です。
もし、どうしても使いたい場合は、
ワークシートにVBAでデータベース関数を入れて、結果の値だけを取得してくる方法になります。
このような処理は、まさにWorksheetFunctionを使うべき処理になります。
WorksheetFunctionの最後に
基本的な使用方法は同じなので、シートで使ったことのある関数なら直ぐに理解できると思います。
WorksheetFunctionは、間違いなく処理速度は速いです。
これを活用しない手はありません。
以下も参考にしてください。
同じテーマ「マクロVBA技術解説」の記事
VBAとは、マクロとは
コーディングとデバッグ
ローカルウィンドウの使い方
WorksheetFunctionについて
RangeとCellsの深遠
Offset、Resizeを使いこなそう
値渡し（ByVal）、参照渡し（ByRef）について
最終行・最終列の取得方法（End,CurrentRegion,SpecialCells,UsedRange）
ユーザー定義関数の作り方
セルの値について（Value,Value2,Text）
Excelのバージョンを判断して「名前を付けて保存」
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- WorksheetFunctionについて
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。