連載
» 2014年08月01日 18時00分 UPDATE

VBA/マクロ便利Tips:新入社員も安心。Excelで数値や日付を打ち込む際の入力規則・条件を設定するValidation

業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、セルに入力規則を設定するValidationオブジェクトの基本的な使い方を解説。数値の範囲や日付などで手入力条件をAddする前に、Deleteメソッドで初期化(削除)することが必要です。

[薬師寺国安,PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本Tipsの環境:Windows 8.1 Enterprise(64ビット)+Excel 2013


 今回は「入力規則」に関するTipsを紹介する。「数値の範囲を指定する入力規則」「日付に関する入力規則」の2つのTipsについて解説する。

 「入力規則」は、ユーザーにデータを入力させる場合などには、必要な処理だ。どんな場合に入力規則を使うかも含めて解説していくので、ぜひ参考にしてほしい。

入力規則を設定するValidationオブジェクトの基本

 例えば、図1のように、「氏名」「年齢」「住所」を入力するセルがあったとする。入力規則を利用すると、「年齢」のセルには「18歳以上70歳未満」のデータしか受け付けないようにできる。

 このように、ユーザーの「年齢」を限定するなど、指定の範囲や、決められた値のデータを入力させる場合に、入力規則は必要だ。通販サイトなどで商品を購入する際の、個人情報を入力する場合などに使用されている入力規則と、同じ利用方法だと考えればいいだろう。

ExcelVBATips13_01.png 図1 「氏名」「年齢」「住所」を入力するセルがあり。「年齢」のセルは「18歳以上70歳未満」のデータしか受け付けないようになっている

セルの入力規則を設定するValidationオブジェクトの書式

With {オブジェクト}.Validation

 .Delete

 .Add Type:={表1の値}, _

  Operator:={表2の値}, _

  Formula1:={最小値},Formula2:={最大値}

End With


 {オブジェクト}には、Rangeオブジェクトを指定する。Deleteメソッドで該当する入力規則を削除(初期化)しておく。セルには入力規則を1種類しか設定できないので、必ずDeleteしておく必要がある。

 「Type」には、{表1の値}を指定し、「Operator」には、{表2の値}を指定する。「Formula1」には{最小値}、「Formula2」には、{最大値}を指定するが、「Formula2」は省略可で「Formula1」のみで指定することもできる。

表1 Typeに指定する値
定数 説明
xlValidateCustom 任意の数式を使用してデータを検証する
xlValidateDate 日付値
xlValidateDecimal 数値
xlValidateInputOnly 値が変更された場合のみ検証する
xlValidateList 指定したリストに値が存在する必要がある
xlValidateTextLength 文字列の長さ
xlValidateTime 時間値
xlValidateWholeNumber 全数値
参考:「XlDVType 列挙型 (Microsoft.Office.Interop.Excel)


表2 Operatorに指定する値
定数 説明
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
リスト1 指定した「年齢」を入力するマクロ

 「年齢」を入力する「C3」のセルに、Validationオブジェクトで、入力規則を適用する。先にも書いたように、セルには入力規則を1種類しか設定できないので、必ずDeleteしておく必要がある(4行目)。

 5行目の「Type」には、表1の「xlValidateWholeNumber」を指定し、「数値全般」を対象とする。6行目の「Operator」には、表2の「xlBetween」を指定し、7行目の「Formula1」と「Fomula2」で指定した範囲内の値を入力可能にする。

 VBEのメニューから[実行]→[Sub/ユーザーフォームの実行]を実行し、データを入力していくと、図2のように表示される。

ExcelVBATips13_02.png 図2 入力規則にのっとったデータは入力できるが、入力規則にのっとっていないデータの場合は警告メッセージが表示される

 入力規則にのっとった数値が入力された場合は、そのまま入力できるが、入力規則にのっとっていない場合は、「入力した値は正しくありません。」と表示される。これはプログラムで表示させているわけではなく、Excelからの警告メッセージとなる。

日付に関する入力規則

 図3のような入力セルがあったとしよう。入力規則を利用すると、指定された日付以外の「日付」が入力されると、Excelから警告メッセージが表示される。購入した商品の日付を入力することで、その商品が保証期間内であるかどうかを確認するために利用できる。

ExcelVBATips13_03.png 図3 「保証期間」を入力するセルを用意した

 「保障書に記載されている期間」に、正常な値を入力するマクロはリスト2になる。

Option Explicit
Sub 指定した日付入力()
  With Range("G3").Validation
    .Delete
    .Add Type:=xlValidateDate, _
         Operator:=xlLessEqual, _
        Formula1:="2014/3/31"
  End With
End Sub
リスト2 「保障証に記載されている期間」に入力するマクロ

 Validationオブジェクトで、「G3」のセルに入力規則を適用する。先にも書いたように、セルには入力規則を1種類しか設定できないので、必ずDeleteしておく必要がある(4行目)。

 5行目の「Type」には、表1の「xlValidateDate」を指定し、「日付値」を対象とする。6行目の「Operator」には、表2の「xlLessEqual」を指定し、7行目の「Formula1」に、保証期間の値を指定する。Formula1に指定された日付より、小さい日付が入力された場合は、保証期間内となる。

 VBEのメニューから[実行]→[Sub/ユーザーフォームの実行]を実行し、データを入力すると、図4のように表示される。

ExcelVBATips13_04.png 図4 入力規則に反するデータ(日付)が入力されると、警告メッセージが表示される

 入力規則にのっとった数値が入力された場合は、そのまま入力できるが、入力規則に則っていない場合は、「入力した値は正しくありません。」と表示される。

入力規則で新入社員も安心

 今回は入力規則に関するTipsを2つ紹介した。入力規則はユーザーに間違ったデータを入力させないためにも、非常に大切な処理だ。

 指定した範囲以外のデータを入力したり、指定した日付以外のデータを入力したりすることはよくあることだ。注意事項として記述しておくだけでは、どうしても人間である以上間違ってデータを入力することはある。そんな場合、入力されたデータをマクロでチェックして警告メッセージを表示するようにすると、入力間違いはほぼ防止できるだろう。

 会社で新入社員などにデータを入力させる場合には、このような入力規則を設定しておくと、新入社員も安心してデータの入力ができるのではないだろうか。ぜひ取り入れて試してほしい。

著者プロフィール

kuniyasu.jpg

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

薬師寺国安事務所代表。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© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。