'入力規制のテスト用ブックを作成するマクロ（Excel97）

'標準モジュールに以下のコードをコピーし、
'MakeTestBookマクロを実行してください。

Option Explicit

Sub MakeTestBook()
    Dim vKenMei As Variant
    Dim i As Long, j As Long

    vKenMei = Array("北海道", "青森県", "岩手県", "宮城県", "秋田県", _
        "山形県", "福島県", "茨城県", "栃木県", "群馬県", "埼玉県", _
        "千葉県", "東京都", "神奈川県", "山梨県", "長野県", "新潟県", _
        "富山県", "石川県", "福井県", "岐阜県", "静岡県", "愛知県", _
        "三重県", "滋賀県", "京都府", "大阪府", "兵庫県", "奈良県", _
        "和歌山県", "鳥取県", "島根県", "岡山県", "広島県", "山口県", _
        "徳島県", "香川県", "愛媛県", "高知県", "福岡県", "佐賀県", _
        "長崎県", "熊本県", "大分県", "宮崎県", "鹿児島県", "沖縄県")

    Workbooks.Add xlWorksheet
    ActiveSheet.Name = "都道府県リスト"
    ActiveWorkbook.Worksheets.Add.Name = "住所データ"
    ActiveWorkbook.Worksheets.Add.Name = "住所入力"
    ActiveWorkbook.Names.Add "KenMei", "=住所データ!$A$2:$A$10000"
    ActiveWorkbook.Names.Add "KenList", "=都道府県リスト!$B$2:$E$48"
    ActiveWorkbook.Names.Add Name:="JushoList", _
        RefersToR1C1:="=OFFSET(KenMei,VLOOKUP(!RC[-1],KenList,3,FALSE),1," & _
        "VLOOKUP(!RC[-1],KenList,4,FALSE),1)"

    With ActiveWorkbook.Sheets("都道府県リスト")
        .Range("A1:E1").Value = Array("都道府県番号", _
            "都道府県", "先頭行1", "先頭行2", "行数")
        With .Range("A2:B2")
            j = LBound(vKenMei)
            For i = 0 To 46
                .Offset(i).Value = Array(i + 1, vKenMei(j))
                j = j + 1
            Next
        End With
        .Range("C2:C48").FormulaR1C1 = "=MATCH(RC[-1],KenMei,0)-1"
        .Range("D49").FormulaR1C1 = "=COUNTA(KenMei)"
        .Range("D2:D48").FormulaR1C1 = "=IF(ISERROR(RC[-1]),R[1]C,RC[-1])"
        .Range("E2:E48").FormulaR1C1 = "=R[1]C[-1]-RC[-1]"
    End With
    With ActiveWorkbook.Sheets("住所データ")
        .Range("A1:B1").Value = Array("都道府県", "住所1")
        .Range("A2:A7").Value = "北海道"
        .Range("A8:A12").Value = "青森県"
        .Range("A13:A17").Value = "岩手県"
        .Range("B2").Value = "（北海道）住所1"
        .Range("B2").AutoFill .Range("B2:B7")
        .Range("B8").Value = "（青森県）住所1"
        .Range("B8").AutoFill .Range("B8:B12")
        .Range("B13").Value = "（岩手県）住所1"
        .Range("B13").AutoFill .Range("B13:B17")
    End With

    With ActiveWorkbook.Sheets("住所入力")
        .Range("A1:B1").Value = Array("都道府県", "住所1")
        .Range("A2").Value = vKenMei(LBound(vKenMei))
        With .Range("A2:A100").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=OFFSET(KenList,0,0,,1)"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .IMEMode = xlIMEModeNoControl
            .ShowInput = True
            .ShowError = True
        End With
        With Range("B2:B100").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=JushoList"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .IMEMode = xlIMEModeNoControl
            .ShowInput = True
            .ShowError = True
        End With
        .Select
        .Range("A2").Select
    End With
End Sub