VBA-13マクロで関数｜VLOOKUPはエラー対策してから使う

エクセルのワークシートで使う関数とマクロの関数は別です

マクロを構築するにあたり関数はとても重要な機能です。

セルに書く関数を使った事がある方ならコードを書く際も関数を使いたいと考える人はいるはずです。

今回はマクロの中で関数を使ってみます。

関数は使用方法から2つに分ける事が出来ます。混乱しない様に分けて考える必要があります。

EnjoyExcel

ワークシートの関数を理解していないとVBAで関数を使うのは難しいです。

ワークシート関数がある程度理解出来ている前提で説明していきますのでご注意ください。

いくつか関数が出てきます。分からない方は都度関数の仕様を調べながら読んでください。

最初に関数の定義を確認しておきましょう。その後ワークシート関数とは少し違った関数を紹介します。

最後にワークシート関数を使った事例を紹介させていただきます。

前回の記事

繰り返し処理について紹介させていただきました。

ここまでくるとプログラミングをやっているなという感じが出てきますよね。

前回記事VBA-12繰り返し処理マクロではループを有効に活用しよう

本記事での課題

今回の課題です。

EnjoyExcel

課題

2つの関数の違いについて理解しよう

初心者向けエクセルマクロ学習シリーズ

関数の定義を確認

関数は大きく分けて以下の様に分ける事が出来ます。

両方とも関数です

1つのマクロを関数として扱う時・・・関数
ワークシート関数を使う時・・・ワークシート関数

1つずつ確認していきます。

CASE1：1つのマクロを関数として扱う時

Sub　～　End Sub　までが１つのプログラムだと紹介してきました。

プログラムというと伝わらない事があるかもしれませんのでここで呼び名を直します。

正式にはプロシージャと言います。「１つのサブプロシージャが・・・」という感じで使います。

例えば最終行や最終列を認識するコードはプロシージャ毎に書くと面倒だしコード自体も長くなります。

そんな時は別で最終行と最終列を確認するプロシージャを用意して都度参照させるような建付けにしてます。

関数をイメージでとらえる

１枚の画像を用意しました。

緑の部分が今自分が書いているコードで青の矢印方向に命令が進むと仮定します。

上から順にコードが実行されると黄色の範囲内にあるA地点とB地点を通過する事になります。

黄色枠の範囲内では仕事の対象となる表の最終行と最終列を認識する予定です。

その際各地点の命令は外出しした赤の箱の中にある銀色の箱「最終行」と「最終列」に任せる事にします。

各箱にはそれぞれの要素を導き出す為のサブプロシージャが居ます。

結果を緑のプロシージャの黄色枠内のAとBに返す様にコードを作りこんでいるとします。

この赤い箱自体を「関数」と呼びます。セルに書く関数からすると非常に大掛かりなものに見えます。

ただし引数（ひきすう）を与えて結果を返すという観点からするとこれも関数です。

効果としては同じ様なコードを書かなくても良くなります。工数削減です。

今回の事例からすると最終行と列を確認するコードは毎回書かなくて良いです。

呼び出すだけでOKという事です。

もう少し具体的に考える

メインプロシージャから2つの関数を呼び出す様を画像にしました。

良く使う作業はFunction・・・というプロシージャとしてコードを準備します。これが関数です。

メインプロシージャ稼働後は引数と一緒にFunctionプロシージャに値を求める様指示を出します。

（メインプロシージャ内のaは列番号、bは行番号です）

Functionプロシージャは与えられた引数を使って作業をします。

Function最終行では列番号aをもとに最終行を探します
Function最終列では行番号bをもとに最終列を探します

FunctionプロシージャでEnd Subを迎える度にメインプロシージャに値を返すという仕組みです。

これでメインプロシージャでは最終行と最終列のコードを書かなくてもそれぞれの値を得る事ができます。

結果を返すという意味では関数なのですが実際は1つのプロシージャになります。

ワークシート関数と同じ様に考えるとちょっと規模が大きくて違和感ありますが慣れてください。

以下リンクはコードの書き方について解説した記事です。

今回の1～14のシリーズ内では説明できない事を書いています。基本的には読み飛ばして下さい。

少し難しいので勉強が進んだ方はチャレンジしてみてください。

関連記事:マクロはどのモジュールに書くのかコード次第なのか？違いを教えて

あくまで書籍等情報を仕入れる際混乱しない様にこんな考え方もあるという説明をしました。

興味ある方は調べてみてください。

CASE2：ワークシート関数として使う時

次にワークシート関数です。これは御馴染みのセルに書く関数とほぼ同じです。

コードの先頭に　WorksheetFunction. と書くと使える関数が出てきます。

以下はVLOOKUP関数を探している最中の画像です。

このようにセルに書く関数もいくつかは使える様になってます。

ただし以下のような決まりがあります。

ワークシート関数を使う時のポイント

書き方はVBAの作法を用いる
ワークシート関数の中でも使えない関数もある
WorkSheetFunction.・・・　を書かない関数もある

またいつもの例外ばっかですね。でも1つ1つ使いながら覚えていくしかないです。

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関数

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