業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、セルに入力規則を設定するValidationオブジェクトの基本的な使い方を解説。数値の範囲や日付などで手入力条件をAddする前に、Deleteメソッドで初期化(削除)することが必要です。
今回は「入力規則」に関するTipsを紹介する。「数値の範囲を指定する入力規則」「日付に関する入力規則」の2つのTipsについて解説する。
「入力規則」は、ユーザーにデータを入力させる場合などには、必要な処理だ。どんな場合に入力規則を使うかも含めて解説していくので、ぜひ参考にしてほしい。
例えば、図1のように、「氏名」「年齢」「住所」を入力するセルがあったとする。入力規則を利用すると、「年齢」のセルには「18歳以上70歳未満」のデータしか受け付けないようにできる。
このように、ユーザーの「年齢」を限定するなど、指定の範囲や、決められた値のデータを入力させる場合に、入力規則は必要だ。通販サイトなどで商品を購入する際の、個人情報を入力する場合などに使用されている入力規則と、同じ利用方法だと考えればいいだろう。
With {オブジェクト}.Validation
.Delete
.Add Type:={表1の値}, _
Operator:={表2の値}, _
Formula1:={最小値},Formula2:={最大値}
End With
{オブジェクト}には、Rangeオブジェクトを指定する。Deleteメソッドで該当する入力規則を削除(初期化)しておく。セルには入力規則を1種類しか設定できないので、必ずDeleteしておく必要がある。
「Type」には、{表1の値}を指定し、「Operator」には、{表2の値}を指定する。「Formula1」には{最小値}、「Formula2」には、{最大値}を指定するが、「Formula2」は省略可で「Formula1」のみで指定することもできる。
定数 | 説明 |
---|---|
xlValidateCustom | 任意の数式を使用してデータを検証する |
xlValidateDate | 日付値 |
xlValidateDecimal | 数値 |
xlValidateInputOnly | 値が変更された場合のみ検証する |
xlValidateList | 指定したリストに値が存在する必要がある |
xlValidateTextLength | 文字列の長さ |
xlValidateTime | 時間値 |
xlValidateWholeNumber | 全数値 |
参考:「XlDVType 列挙型 (Microsoft.Office.Interop.Excel) |
定数 | 説明 |
---|---|
xlBetween | 次の値の間 |
xlEqual | 次の値に等しい |
xlGreater | 次の値を超える |
xlGreaterEqual | 次の値以上 |
xlLess | 次の値未満 |
xlLessEqual | 次の値以下 |
xlNotBetween | 次の値の間以外 |
xlNotEqual | 次の値に等しくない |
参考:「XlFormatConditionOperator 列挙型 (Microsoft.Office.Interop.Excel)」 |
図1で、「年齢」に指定した「入力規則」にのっとった値を入力するコードは、リスト1になる。VBE(Visual Basic Editor)を起動してModule1内にリスト1のコードを記述しよう。
Option Explicit Sub 指定した数値入力() With Range("C3").Validation .Delete .Add Type:=xlValidateWholeNumber, _ Operator:=xlBetween, _ Formula1:="18", Formula2:="70" End With End Sub
「年齢」を入力する「C3」のセルに、Validationオブジェクトで、入力規則を適用する。先にも書いたように、セルには入力規則を1種類しか設定できないので、必ずDeleteしておく必要がある(4行目)。
5行目の「Type」には、表1の「xlValidateWholeNumber」を指定し、「数値全般」を対象とする。6行目の「Operator」には、表2の「xlBetween」を指定し、7行目の「Formula1」と「Fomula2」で指定した範囲内の値を入力可能にする。
VBEのメニューから[実行]→[Sub/ユーザーフォームの実行]を実行し、データを入力していくと、図2のように表示される。
入力規則にのっとった数値が入力された場合は、そのまま入力できるが、入力規則にのっとっていない場合は、「入力した値は正しくありません。」と表示される。これはプログラムで表示させているわけではなく、Excelからの警告メッセージとなる。
図3のような入力セルがあったとしよう。入力規則を利用すると、指定された日付以外の「日付」が入力されると、Excelから警告メッセージが表示される。購入した商品の日付を入力することで、その商品が保証期間内であるかどうかを確認するために利用できる。
「保障書に記載されている期間」に、正常な値を入力するマクロはリスト2になる。
Option Explicit Sub 指定した日付入力() With Range("G3").Validation .Delete .Add Type:=xlValidateDate, _ Operator:=xlLessEqual, _ Formula1:="2014/3/31" End With End Sub
Validationオブジェクトで、「G3」のセルに入力規則を適用する。先にも書いたように、セルには入力規則を1種類しか設定できないので、必ずDeleteしておく必要がある(4行目)。
5行目の「Type」には、表1の「xlValidateDate」を指定し、「日付値」を対象とする。6行目の「Operator」には、表2の「xlLessEqual」を指定し、7行目の「Formula1」に、保証期間の値を指定する。Formula1に指定された日付より、小さい日付が入力された場合は、保証期間内となる。
VBEのメニューから[実行]→[Sub/ユーザーフォームの実行]を実行し、データを入力すると、図4のように表示される。
入力規則にのっとった数値が入力された場合は、そのまま入力できるが、入力規則に則っていない場合は、「入力した値は正しくありません。」と表示される。
今回は入力規則に関するTipsを2つ紹介した。入力規則はユーザーに間違ったデータを入力させないためにも、非常に大切な処理だ。
指定した範囲以外のデータを入力したり、指定した日付以外のデータを入力したりすることはよくあることだ。注意事項として記述しておくだけでは、どうしても人間である以上間違ってデータを入力することはある。そんな場合、入力されたデータをマクロでチェックして警告メッセージを表示するようにすると、入力間違いはほぼ防止できるだろう。
会社で新入社員などにデータを入力させる場合には、このような入力規則を設定しておくと、新入社員も安心してデータの入力ができるのではないだろうか。ぜひ取り入れて試してほしい。
薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所
薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。
1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。
1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。
2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。
Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。
Microsoft MVP for Development Platforms - Client App Dev(Oct 2003-Sep 2012)。
Microsoft MVP for Development Platforms - Windows Phone Development(Oct 2012-Sep 2013)。
Microsoft MVP for Development Platforms - Client Development(Oct 2013-Sep 2014)。
Copyright © ITmedia, Inc. All Rights Reserved.