Excelデータの入力不備で計算エラーが発生して家に帰れない！――入力規則よりも簡単なチェックマクロとは：働く価値を上げる“VBA／GAS術”（3）（1/2 ページ）

1つのExcelファイルを複数人で編集する場合、想定していたのとは違う形式でデータを入力されてしまい、不備が発生する可能性も少なくない。その問題をExcel VBAで解決するにはどうすればいいのだろうか。

[高橋宣成，プランノーツ]

Excel VBAとGAS

　業務で発生するちょっとした面倒くさい業務。特定の操作を繰り返すだけなので、今のままでもいいけど、楽になったらいいなと思うこともあるのではないでしょうか。例えば業務を自動化すると時間が空くため、他の業務に時間を充てたり、残業時間を減らしたりできるかもしれません。

　本連載「働く価値を上げる“VBA／GAS術”」では、業務を効率化する手段として「Microsoft Excel」（マイクロソフトエクセル）で使える「VBA」（Visual Basic for Applications）と「G Suite」の「GAS」（Google Apps Script）の使い方を説明し、よくある業務課題の解決策を紹介します。

Excel VBAで簡単なチェックツールを作る

　Excelで顧客リスト、商品リスト、従業員リスト、備品購入リストなどさまざまなデータリストを取り扱うことも少なくありません。しかし、いざリストを使って作業するとき、次のように困ったことに遭遇することはないでしょうか？

• 数値を入れるべき欄に「円」や「個」などの単位が入力されているため、合計金額の計算式でエラーになる
• 日付欄に「2017/8/3〜2017/8/5」や「いつでも」など、日付形式で入っていないためフィルターが使えない

　もし多くの人と1つのExcelファイルを編集する場合、想定していたのとは違う形式でデータを入力される可能性があります。「データの入力規則」機能で、入力の制限をかけることは可能ですが、事前に設定をする必要があります。今回は、タイミング問わずデータを検査できるようにするため、Excel VBAで「入力内容のチェックをするマクロ」を作成していきます。

　Excel VBAの基本については、連載第1回の『さらば残業！　面倒くさいエクセル業務を楽にする「Excel VBA」とは』を参照してください。

備品購入リストの例

　今回、図1のような備品購入リストを用意しました。

図1　Excelシート上の備品購入リスト

　C3セルの「3個」、D6セルの「250円」というデータは、本来は数値のみで入力すべきデータのはずが単位まで入っています。そのため実際にE列に、C列の値とD列の値を乗算して金額を求める数式を入力すると、図2のようにエラーになってしまいます。

図2　Excelで文字列を乗算するとエラーになる

データが数値かどうかをチェックするプロシージャ

　そこで、備品購入リストのC2からD6の範囲で数値ではないデータがあれば、そのセルの背景色を黄色に設定するプロシージャを作りました。

Sub 数値チェック()
Dim i As Long
For i = 2 To 6
    If Not IsNumeric(Sheet1.Cells(i, 3).Value) Then Sheet1.Cells(i, 3).Interior.Color = vbYellow
    If Not IsNumeric(Sheet1.Cells(i, 4).Value) Then Sheet1.Cells(i, 4).Interior.Color = vbYellow
Next i
End Sub

　このプロシージャを実行すると、図3のように数値でないデータのあるセル背景色が黄色に変更されます。

図3　Excelシートのデータを数値チェックする

数値チェックをするプロシージャ

　ではプロシージャ「数値チェック()」の内容について解説します。

Dimステートメントで変数を宣言する

　まず、以下の部分を見ていきましょう。

Dim i As Long

　ここでは「Dim」ステートメント（命令文）というコードを使って「変数の宣言」を行っています。変数は、プログラムの実行状況によって、文字列や数値などさまざまなデータを記憶するものです。変数の宣言を行うことで、宣言以降の処理で変数を使用できる状態になります。Dimステートメントの書式は下記の通りです。

Dim 変数名 As 型

　変数の宣言時に、変数名とともに、「型」を指定する必要があります。型は変数に入れるデータの種類を指定するもので、例えば整数であれば「Long」、文字列であれば「String」、日付であれば「Date」となります。そのため今回のDimステートメントは、「iという名前の変数を整数で用意してね」という命令となるわけです。

繰り返し処理を行う「For〜Next」文

　人の手では面倒な繰り返しの作業でも、プログラムさえすればコンピュータがそれを高速かつ正確に実行します。VBAでは、その繰り返し処理を実現する構文として「For〜Next」文が用意されています。書式は以下の通りです。

For 変数 = 初期値 To 最終値
    '処理
Next 変数

　この構文で使う変数は、「繰り返しの回数をカウントする整数」です。この変数を「イテレータ」と呼びます。イテレータの値は、「初期値」にセットされ、For〜Next間の処理が繰り返されるたびに、1ずつ自動で加算されていきます。イテレータの値が「最終値」に達成すると繰り返し処理を終了します。今回の例では、以下の部分が該当します。

For i = 2 To 6
    '処理
Next i

　イテレータとしてDimステートメントで宣言した変数iを用い、その初期値は2。値が6に達するまで計5回、For〜Next文内の処理を行います。今回は、「とある行のB、C列に入っている値が数値かどうかを判定し、数値でなければセルの色を黄色に変更する」という下記コードの処理を行っていきます。

If Not IsNumeric(Sheet1.Cells(i, 3).Value) Then Sheet1.Cells(i, 3).Interior.Color = vbYellow
If Not IsNumeric(Sheet1.Cells(i, 4).Value) Then Sheet1.Cells(i, 4).Interior.Color = vbYellow

For〜Next文内の処理

　分解すると、下記リストの命令を行っていることになります。

• 【1】「Sheet1」でワークシートを指定
• 【2】「Cells」でセルの値を参照
• 【3】「If Not IsNumeric(……) Then」で2〜6行目のC列、D列についてその値が数値かどうかを判断
• 【4】「Interior.Color = vbYellow」でセルの色を黄色に変更する

　次は、1つ1つの命令内容を理解するために、使うコードをイミディエイトウィンドウで記しながら、確認してきます。