VBA-13マクロで関数｜VLOOKUPはエラー対策してから使う
エクセルのワークシートで使う関数とマクロの関数は別です
マクロを構築するにあたり関数はとても重要な機能です。
セルに書く関数を使った事がある方ならコードを書く際も関数を使いたいと考える人はいるはずです。
今回はマクロの中で関数を使ってみます。
関数は使用方法から2つに分ける事が出来ます。混乱しない様に分けて考える必要があります。
EnjoyExcelワークシート関数を理解していないとVBAで関数を使えないです。
ワークシート関数の仕組みをある程度理解することをおすすめします。
ワークシート関数がある程度理解出来ている前提で説明していきますのでご注意ください。
いくつか関数が出てきます。分からない方は都度関数の仕様を調べながら読んでください。
最初に関数の定義を確認しておきましょう。その後ワークシート関数とは少し違った関数を紹介します。
最後にワークシート関数を使った事例を紹介させていただきます。
前回の記事
繰り返し処理について紹介させていただきました。
ここまでくるとプログラミングをやっているなという感じが出てきますよね。
本記事での課題
今回の課題です。
EnjoyExcel課題：
2つの関数の違いについて理解しよう
関数の定義を確認
関数は大きく分けて以下の様に分ける事が出来ます。
- 1つのマクロを関数として扱う時・・・関数
- ワークシート関数を使う時・・・ワークシート関数
1つずつ確認していきましょう。その前に1点お知らせです。
今までは1つのプログラム・・・という使い方をしてきました。以降は呼び名を変えます。
正式にはプロシージャと言います。「１つのプロシージャが・・・」という感じで使います。
CASE1：1つのマクロを関数として扱う時
最初は1つのマクロを関数として使うというケースについて説明していきます。
まずは関数をイメージでとらえてください
ある表の最終行と最終列を取得する為のコードを１枚の画像で表現してみました。
黒背景の部分がメインプロシージャです。赤の太矢印方向に命令が進むと仮定します。
メインプロシージャのコードが実行されると「A」と「B」を通過するという流れです。
処理が「A」と「B」に到達した時点で画像左側の緑の箱に作業を任せるという仕様になっています。
各箱にはそれぞれの要素を導き出す為のサブプロシージャが居ます。
箱「A」では最終行、箱「B」では最終列を取得し値をメインプロシージャに返します。
VBAではこの緑の箱自体を「関数」と呼びます。イメージしにくい方はExcelのワークシート関数を想像してください。
今回の一連の作業はまさにワークシート関数の動きと同じなんですよ。
引数（ひきすう）を与えてから戻り値を取得するという流れはワークシート関数と同じです。
もう少し具体的に考える
メインプロシージャから2つの関数を呼び出す様子を画像にしました。
例題としてワークシートにある表から最終行と最終列を取得してみます。まずはワークシートをご覧ください。
最終的には「12」と「4」という数値が取得されるという事をふまえて以降のコードをご覧ください。
1つのメインプロシージャと2つの関数を使って一連の作業を表現しています。
画像の右側がメインプロシージャです。左側が関数となります。
関数は「Function・・・」というプロシージャとしてコードを準備しています。
メインプロシージャ稼働後は引数を使ってFunctionプロシージャに値を求めるという仕様です。
最終的には変数「a」は行番号、変数「b」は列番号が格納されることになります。
これでメインプロシージャでは最終行と最終列のコードを書かなくてもそれぞれの値を得る事ができます。
ワークシート関数と同じ様に考えるとちょっと規模が大きくて違和感ありますが慣れてください。
関連記事
以下リンクはコードの書き方について解説した記事です。
今回の1～14のシリーズ内では説明できない事を書いています。基本的には読み飛ばして下さい。
少し難しいので勉強が進んだ方はチャレンジしてみてください。
あくまで書籍等情報を仕入れる際混乱しない様にこんな考え方もあるという説明をしました。
興味ある方は調べてみてください。
CASE2：ワークシート関数として使う時
次にワークシート関数です。これは御馴染みのセルに書く関数とほぼ同じです。
コードの先頭に WorksheetFunction. と書くと使える関数が出てきます。
以下はVLOOKUP関数を探している最中の画像です。
このようにセルに書く関数もいくつかは使える様になってます。
ただし以下のような決まりがあります。
- 書き方はVBAの作法を用いる
- ワークシート関数の中でも使えない関数もある
- WorkSheetFunction.・・・ を書かない関数もある
またいつもの例外ばかりですね。1つ1つ使いながら覚えていくしかないです。
CASE2の具体例：VLOOKUP関数
VLOOKUP関数をVBAで書くと以下のようになります。
型（形）はセルに書いている関数と同じですが作法はVBAです。
ちょっと混乱しますがゆっくり見ていただけると分かると思います。
このVLOOKUP関数を書いたコードは以下のシートに対して指示をしています。
どうやって稼働しているのか見ていきましょう。
ワークシートの画面は以下のようなレイアウトです。
個人情報風のデータは「なんちゃって個人情報」で生成したダミーデータを使っています。
A6からの表ですがB列に名前が居るのでB6を一番左の列にとったリストを第2引数にしています。
先程のVLOOKUP関数が示しているのはこのような内容です。
「B2の値がB列にいたらいたらC2にI列の部署を持ってきてください」というコードです。
結果は「経理」となりますね。最終行はA列で認識して最終列は6行目で認識という感じです。
仕上げで実行ボタンやC2の値をクリアするボタン、B7からI列の最終行までを削除するボタンを用意します。
これで簡易的な検索ツールが出来ます。これならVLOOKUP関数が分からない方でも使えますね。
課題
実はVLOOKUP関数には乗り越えないといけない課題があります。
VLOOKUP関数実行後の #N/A です。
VLOOKUP関数を書ける人なら誰もが1度は見た事あるキーワードです。
マクロでこれを出してしまうとコードがエラーで止まります。以下のようなメッセージボックスが出ます。
実行時エラーが出るとマクロが止まってしまいます
実行時エラー1004はいろんな要因があります。1つは取得したいデータが存在しないというエラーです。
文言から推測しても結果が取得出来ないんだ＝「データがない」と判断します。
マクロの建付けを知らない人がこのデータを使うとどうなるでしょうか。
きっと「エラーが出ます」と問合せがきてしまいますね。改善の必要があります。
VLOOKUP関数実行時のエラー対策
検索対象となるデータがデータベースに無い時にはエラーになってしまいます。
VLOOKUP関数を使ったコードは不完全なコードでした。
エラーが出る原因からすると今後も起こる可能性があります。
ではどうしたら良いでしょうか？今まで勉強したことで使えそうなものがあるはずです。
ワークシート関数を組み合わせてエラーが出ない様な作業フローを構築しましょう
IF文を使って対策できる
エラーが出るときと出ないときの条件は明確なので条件分岐を使って考えてみましょう。
検索される側のデータ内に対象のデータがあるときはVLOOKUPを実行します。
検索される側のデータ内に対象のデータが無い時はVLOOKUPを実行しない様にします。
という事は条件分岐が使えそうです。IF文を採用しましょう。
CountIf関数を使って対策する
データの有無を確認するにはいろいろな方法があります。
今回もワークシート関数を使いましょう。CountIf関数を使います。
CountIf関数の説明
建付けは以下参照ください。
CountIf（第１引数,第２引数）
第1引数
関数実行時の対象となるデータベースの範囲です。
第2引数
データベースの中で検索したい文字です。
対策方法
以下のような条件を設定します。第1引数にデータベース（検索される側）の値を取ります。
第2引数に検索対象の値を用意します。
これでデータベースの中に検索対象の情報が居るのか確認出来ますね。
最初の引数にデータベースのキーが居る列、次の引数で検索ワードを指定します。
CountIf（第1引数,第2引数）＞0
上記をIF文の条件にしましょう。TRUEだったらVLOOKUP関数を実行させます。
FALSEだったら”該当なし”等の言葉を出してあげればエラーは出ないです。
参考
基本的にVLOOKUP関数の第1引数となるデータはデータベース内に1つじゃないとダメです。
2つ以上だとデータベースに問題があります。
よって本来は CountIf(第1引数,第2引数）=1 という条件が正です。
エラー対策済みのコード
以下コードをご覧ください。（コードをコピーする際はOption Explicitが重複しない様に注意してください）
Option Explicit
Sub VLOOKUP()
'************************************************************
'VLOOKUP関数を使うがエラー対策が必要
'先にCountIfでデータの有無を確認しTrueの際は
'VLOOKUP関数を実行する様にした
'************************************************************
'変数の定義（日本語使用しています）
Dim 最終行 As Long
Dim 最終列 As Long
'************************************************************
'最終行と最終列を取得
最終行 = Cells(Rows.Count, 1).End(xlUp).Row
最終列 = Cells(6, Columns.Count).End(xlToLeft).Column
'************************************************************
'セルB2の名前がリストの中に1つ以上あったら・・・
If WorksheetFunction.CountIf(Range(Cells(6, 2), Cells(最終行, 2)), Range("B2")) > 0 Then
'VLOOKUP関数を実行
Range("C2") = WorksheetFunction.VLOOKUP _
(Range("B2"), Range(Cells(6, 2), Cells(最終行, 最終列)), 8, False)
Else
'セルC2に 該当なし を展開
Range("C2") = "該当なし"
End If
End Subまとめ
VLOOKUP関数は単体だと業務の仕様に対応出来ない事があります。
そんなときは複数の関数を組み合わせて使う事でエラーを回避してください。
次回が最後です。今まで勉強したことをある程度使うことができる課題を用意しています。
参考：おすすめ書籍
初心者様向けの書籍です。多くの書籍とは一風変わった事を書いてますが共感が持てたので紹介します。
EnjoyExcel「マクロを勉強するのは少しでも仕事を効率化させる為」であり
「マクロを完璧にマスターするわけではない」という考え方です