【EXCEL：VBA】入力規則が設定されている全てのセルを取得したい

この記事では、ExcelVBAを利用して「指定されたEXCELに設定されている入力規則を全て取得する」方法について説明しています。

入力規則が設定されているセルを1つ1つ指定して確認することは、大変な作業です。

今回ご紹介する機能を利用すれば、入力規則が設定されているセル番地と設定されている内容を一瞬で取得することができます。

それでは、指定したEXCELから設定されている全ての入力規則を調べる機能を作成していきましょう。

困っている女性

EXCELに設定されている入力規則を1つ1つ調べる作業があるの。

困っている女性

過去に作ったEXCELのどのセルに入力規則が設定されているか、1つ1つセルを調べる必要があり、すごく大変なの。

困っている女性

入力規則に設定した内容を更新しなければならないことがよくあり、時間がかかるし、見つけるのに漏れが発生してしまうの。

それなら、VBAを使えばボタン1つ押すだけで、EXCELに設定されている入力規則を全て調べることができるよ。

解決する男性

しかも、どのセルにどんな設定がされているか一覧化してくれるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

概要図（入力規則が設定されている全てのセルを取得する機能）

今回作成する「入力規則が設定されている全てのセルを取得する機能」の概要図は、以下です。

入力規則が設定されている全てのセルを取得する機能の概要図

EXCELファイルパスとシート名を指定すると、指定されたEXCELの指定されたシートから全ての入力規則情報を取得し、シートに出力します。

◆基本的な「入力規則」については、以下の記事を参照してください。

2019-05-27【EXCEL：データの入力規則】「同じ文字を何度も入力するのが面倒！！」を解決する

2019-06-03【EXCEL：データの入力規則】リストから対象データを選びたい

2019-06-14【EXCELのINDIRECT関数の使い方】名前付きセルを利用して絞り込みリストを作りたい

指定したEXCELから、設定されている入力規則を取得する機能を、1つ1つ順番に説明していきます。

EXCELファイル指定欄、シート名指定欄、取得した入力規則情報の表示欄を準備する

まず、調べたいEXCELファイルとシート名を指定する欄を作成します。

次に、取得したそのシートに設定されている入力規則の情報を表示する欄を作成します。

「メイン」シートを作成する

メインシートを作成する

「メイン」という名前のシートを作成します。
A1セルに「ファイルパス」のタイトル行を作成します。
B1とC1セルを結合し、「ファイルパス」の入力欄を作成します。
A2セルに「シート名」のタイトル行を作成します。
B2とC2セルを結合し、「シート名」の入力欄を作成します。
3行目に「セル」、「入力規則1」、「入力規則2」のタイトル行を作成します。
B列、C列の表示形式に「文字列」を指定します。
4行目以降は、取得した入力規則の情報を書き込む欄になります。

4行目以降の「セル」、「入力規則1」、「入力規則2」にはVBAプログラムが記入してくれます。

「セル」にはセル番地が出力されます。

入力規則には、2つ設定する欄があり、その2つが「入力規則1」、「入力規則2」に出力されます。

B列、C列の表示形式に「文字列」を指定したのは、入力規則に設定されている内容をそのまま表示するためです。

なぜなら、入力規則にはEXCEL関数を指定することができ、表示形式を「文字列」にしていないと、EXCEL関数の実行結果が表示されてしまうからです。

VBAで入力規則の情報を取得する機能を作成する

開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。

Microsoft Visual Basic for Applicationsを起動する

「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。

VBAプログラムを入力する

標準モジュールを追加して、以下のソースを書きます。

Public Sub Main_Proc()
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim wb As Workbook
    Dim MaxRow As Long
    Dim nowRow As Long
    Dim rng As Range
    
    '①メインシートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②データ入力されているA列の最終行を取得する
    MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row
    
    '③最終行が4行目以降なら、4行目以降のA列からC列のセルをクリアする
    If MaxRow >= 4 Then
        shtMain.Range("A4:C" & MaxRow).ClearContents
    End If
    
    '④対象EXCELファイルを開く
    Set wb = Workbooks.Open(shtMain.Range("B1"))
            
    '⑤対象シートを変数に格納する
    Set shtData = wb.Sheets(shtMain.Range("B2").Text)
    
    nowRow = 4
    
    '⑥入力規則が設定されているセル分、ループを繰り返す
    For Each rng In shtData.Cells.SpecialCells(xlCellTypeAllValidation)
        
        '⑦入力規則の設定が空でない場合、セル番地と設定値を取得する
        If rng.Validation.Formula1 <> "" Then
        
            shtMain.Cells(nowRow, 1) = rng.Address(False, False)
            shtMain.Cells(nowRow, 2) = rng.Validation.Formula1
            shtMain.Cells(nowRow, 3) = rng.Validation.Formula2
            
            nowRow = nowRow + 1
        End If
    Next
    
    '⑧対象EXCELファイルを閉じる
    wb.Close
    
    Set wb = Nothing
    
    MsgBox "完了"
    
End Sub

Public Sub Main_Proc()

Dim shtMain As Worksheet

Dim shtData As Worksheet

Dim wb As Workbook

Dim MaxRow As Long

Dim nowRow As Long

Dim rng As Range

'①メインシートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("メイン")

'②データ入力されているA列の最終行を取得する

MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

'③最終行が4行目以降なら、4行目以降のA列からC列のセルをクリアする

If MaxRow >= 4 Then

shtMain.Range("A4:C" & MaxRow).ClearContents

End If

'④対象EXCELファイルを開く

Set wb = Workbooks.Open(shtMain.Range("B1"))

'⑤対象シートを変数に格納する

Set shtData = wb.Sheets(shtMain.Range("B2").Text)

nowRow = 4

'⑥入力規則が設定されているセル分、ループを繰り返す

For Each rng In shtData.Cells.SpecialCells(xlCellTypeAllValidation)

'⑦入力規則の設定が空でない場合、セル番地と設定値を取得する

If rng.Validation.Formula1 <> "" Then

shtMain.Cells(nowRow, 1) = rng.Address(False, False)

shtMain.Cells(nowRow, 2) = rng.Validation.Formula1

shtMain.Cells(nowRow, 3) = rng.Validation.Formula2

nowRow = nowRow + 1

End If

'⑧対象EXCELファイルを閉じる

wb.Close

Set wb = Nothing

MsgBox "完了"

End Sub

最後にEXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。

EXCELファイルに設定されている入力規則の情報を取得する機能の使い方

それでは、設定されている入力規則を調べる方法を説明します。

まず、B1セルに調べたいEXCELファイルのパスを入力します。

次に、B2セルに調べたいEXCELシート名を入力します。

次に、「開発」タブの「マクロ」をクリックします。

VBAプログラムを実行する

先程作成した自作関数「Main_Proc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックして設定されている入力規則を取得します。

実行ボタンをクリックする

しばらく待つと、指定したEXCELファイルの指定したシートに設定されている入力規則が全て表示します。

A列には入力規則が設定されているセル番地が、B列とC列には入力規則に設定されている情報が表示します。

設定されている入力規則を取得した結果

上記の図では、7つ入力規則が表示されています。

実際に調べたEXCELファイルは、以下のようになっています。

入力規則が設定されているEXCELファイル

B2セルからB8セルまで入力規則が設定されています。

B2セルには、入力値の種類に「整数」が指定されています。

入力規則に「整数」が指定されている

B3セルには、入力値の種類に「小数点数」が指定されています。

入力規則に「小数点数」が設定されている

B４セルには、入力値の種類に「リスト」が指定されています。

入力規則に「リスト」が設定されている

B5セルには、入力値の種類に「日付」が指定されています。

入力規則に「日付」が設定されている

B6セルには、入力値の種類に「時刻」が指定されています。

入力規則に「時刻」が設定されている

B7セルには、入力値の種類に「文字列（長さ指定）」が指定されています。

入力規則に「文字列（長さ）」が設定されている

B8セルには、入力値の種類に「ユーザ設定」が指定されています。

入力規則に「ユーザ設定」が設定されている

ちなみに、入力値の種類を「リスト」と「ユーザ設定」を選択すると、設定できる欄が1つだけ入力可能になっています。

それ以外の入力値の種類を選択すると、設定できる欄が2つ入力可能になっています。

設定されている入力規則の情報を調べるVBAプログラム（Main_Proc）の説明

それでは、VBAプログラム（Main_Proc）を説明します。

①参照するシートを変数に格納する

'①参照するシートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")

1 2	'①参照するシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン")

ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。

②データ入力されているA列の最終行を取得する

'②データ入力されているA列の最終行を取得する
MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

1 2	'②データ入力されているA列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

ここでは、A列の入力されている最終行を変数に格納しています。

何度も実行していると、前回実行時に取得した入力規則情報が残っています。

今回取得した情報を出力する前に、その情報をクリアするために最終行を取得しています。

最終行を取得することで、どこまでのセルをクリアするべきかがわかるからです。

③最終行が4行目以降なら、4行目以降のA列とB列とC列のセルをクリアする

'③最終行が4行目以降なら、4行目以降のA列からC列のセルをクリアする
If MaxRow >= 4 Then
    shtMain.Range("A4:C" & MaxRow).ClearContents
End If

'③最終行が4行目以降なら、4行目以降のA列からC列のセルをクリアする

If MaxRow >= 4 Then

shtMain.Range("A4:C" & MaxRow).ClearContents

End If

ここでは、先程取得した最終行が4以上なら、セルの値をクリアする必要があります。

その場合、A4セルからC列の最終行までをクリアします。

この処理によって、すでに入力されているセル番地と入力規則1および入力規則2がクリアされます。

④対象EXCELファイルを開く

'④対象EXCELファイルを開く
Set wb = Workbooks.Open(shtMain.Range("B1"))

1 2	'④対象EXCELファイルを開く Set wb = Workbooks.Open(shtMain.Range("B1"))

ここでは、指定されたEXCELファイルを開いています。

開いたEXCELワークブックを変数に格納しています。

⑤対象シートを変数に格納する

'⑤対象シートを変数に格納する
Set shtData = wb.Sheets(shtMain.Range("B2").Text)

1 2	'⑤対象シートを変数に格納する Set shtData = wb.Sheets(shtMain.Range("B2").Text)

ここでは、指定されたシートを変数に格納しています。

⑥入力規則が設定されているセル分、ループを繰り返す

'⑥入力規則が設定されているセル分、ループを繰り返す
For Each rng In shtData.Cells.SpecialCells(xlCellTypeAllValidation)

1 2	'⑥入力規則が設定されているセル分、ループを繰り返す For Each rng In shtData.Cells.SpecialCells(xlCellTypeAllValidation)

ここでは、入力規則が設定されているセルを取得し、そのセル分に対して、以降の処理を繰り返し実行します。

⑦入力規則の設定が空でない場合、セル番地と設定値を取得する

'⑦入力規則の設定が空でない場合、セル番地と設定値を取得する
If rng.Validation.Formula1 <> "" Then

    shtMain.Cells(nowRow, 1) = rng.Address(False, False)
    shtMain.Cells(nowRow, 2) = rng.Validation.Formula1
    shtMain.Cells(nowRow, 3) = rng.Validation.Formula2
    
    nowRow = nowRow + 1
End If

'⑦入力規則の設定が空でない場合、セル番地と設定値を取得する

If rng.Validation.Formula1 <> "" Then

shtMain.Cells(nowRow, 1) = rng.Address(False, False)

shtMain.Cells(nowRow, 2) = rng.Validation.Formula1

shtMain.Cells(nowRow, 3) = rng.Validation.Formula2

nowRow = nowRow + 1

End If

ここでは、入力規則の設定が空でない場合、「メインシート」のA列にセル番地、B列に入力規則1、C列に入力規則2を出力しています。

⑧対象EXCELファイルを閉じる

'⑧対象EXCELファイルを閉じる
wb.Close

1 2	'⑧対象EXCELファイルを閉じる wb.Close

ここでは、開いたEXCELファイルを閉じています。

結果として、指定されたEXCELファイルの指定されたシートから、設定されている入力規則の情報を取得することができるようになりました。

それでは、設定されている入力規則の情報を取得するツールを作って、作業を効率化しましょう。

以上です。

概要図（入力規則が設定されている全てのセルを取得する機能）

EXCELファイル指定欄、シート名指定欄、取得した入力規則情報の表示欄を準備する

「メイン」シートを作成する

VBAで入力規則の情報を取得する機能を作成する

EXCELファイルに設定されている入力規則の情報を取得する機能の使い方

設定されている入力規則の情報を調べるVBAプログラム（Main_Proc）の説明

①参照するシートを変数に格納する

②データ入力されているA列の最終行を取得する

③最終行が4行目以降なら、4行目以降のA列とB列とC列のセルをクリアする

④対象EXCELファイルを開く

⑤対象シートを変数に格納する

⑥入力規則が設定されているセル分、ループを繰り返す

⑦入力規則の設定が空でない場合、セル番地と設定値を取得する

⑧対象EXCELファイルを閉じる

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル