[Excel][VBA] 入力規則の設定によりブックが壊れてしまう例／入力規則リストの参照範囲を動的に変更する例

0.README.md

[Excel][VBA] 入力規則の設定によりブックが壊れてしまう例／入力規則リストの参照範囲を動的に変更する例

エクセルの入力規則（リスト）をVBA（Range.Validation.Add()）で設定したときに、場合によってはブックが壊れてしまう、という話。
特にリストをカンマ区切りの文字列で直接指定するような場合は、255文字を超えないように要注意（リスト(候補)が動的に変わるような場合、カンマ区切りで直接指定するよりも、ワークシートの範囲参照で指定するのがベター）。

入力規則リストの参照範囲を動的に変更する例

下記のような不具合が発生しないように、リスト(候補)はワークシートへの参照の形で指定するのがよいと考えられる。
参照範囲は固定範囲（予め想定される最大の範囲）でもよいが、ここでは参照元の候補数が更新された場合に、Worksheet_Changeイベントによって参照範囲を動的に変更するようにしている。

ソースコード

• 標準モジュール：Mod_Validation（Const Publicは環境に応じて要変更）
• リストが存在するワークシートのモジュール

不具合発生例

Range.Validation.Add()の引数Formula1に、255文字より多くの文字列を指定すると不具合が発生する（カンマ区切りで直接リスト(候補)を指定するような場合に起こりやすいと思われる）。

ソースコード

• 不具合発生テスト用標準モジュール(壊れても構わないブックで試すこと！)

不具合発生の過程（推測を含む）

VBAでセルの入力規則を設定、このとき、Range.Validation.Add()の引数であるFormula1に256文字以上を指定
　↓
とくにエラーもなく実行される

　↓
入力規則にもしっかりと反映されている

　↓
安心して保存（←☠実はこの時点ですでにエクセルブックとしては壊れている）
　↓
ブックを閉じてから、開き直す
　↓
「一部の内容に問題が見つかりました」と開き直られる←☠

　↓
回復を試みる
　↓
入力規則が削除される←☠（入力規則のみならず書式などがおかしくなることもある模様）

覚書

• Range.Validation.Add()で、Formula1に指定できるのは255文字までで、それ以上の文字数になると動作がおかしくなる(一見普通に動作しているように見えるが、保存して閉じた後で開き直すとブックが壊れている)
• 入力規則に(範囲指定で)表示できる選択肢は最大32767個まで（それ以上の範囲を指定しておいてもドロップダウンリストに表示されない）
• 入力規則の指定は、VBAで設定するまでもなく、範囲で最大行(例：1048576)固定で指定しておいても（空欄は無視する指定にしてあれば）とりあえず実用的な速度で動作はするが、入力規則を利用するマクロやアドインの作りによっては動作が重くなってしまう（例：入力規則リスト選択改善アドイン）ので、必要最小限の範囲指定にするほうがベター
• Range.Validation.Add()のFormula1でワークシートの範囲(数式)を指定する場合、Excelのオプション＞数式＞数式の処理の「R1C1 参照形式を使用する」の状態（Application.ReferenceStyle）に応じた参照形式で指定する必要がある

元ネタ

• たか@Alcスロリーマン@TINtaka777さんのツイート
• 関連スレッド

参考

• 「一部の内容に問題が見つかりました」の原因は、入力規則リストの元の値のオーバーフローだった！ | damのブログラミング雑記ブログ

Mod_Validation.vba

Option Explicit

Public Const ValidationSheetName = "Sheet1"
Public Const ValidationCellRow = 1
Public Const ValidationCellColumn = 3

Public Const ListSheetName = "Sheet2"
Public Const ListColumn = 74
Public Const ListBeginRow = 5

Sub UpdateValidationList()
    Dim ValidationSheet As Worksheet: Set ValidationSheet = ThisWorkbook.Worksheets(ValidationSheetName)
    Dim ListSheet As Worksheet: Set ListSheet = ThisWorkbook.Worksheets(ListSheetName)
    
    Dim ListMaxCount As Long
    With ListSheet
        ListMaxCount = .Cells(.Rows.Count, ListColumn).End(xlUp).Row - ListBeginRow + 1
    End With
    If ListMaxCount < 1 Then Exit Sub
    
    Dim ValueListRange As Range: Set ValueListRange = ListSheet.Cells(ListBeginRow, ListColumn).Resize(ListMaxCount, 1)
    
    With ValidationSheet.Cells(ValidationCellRow, ValidationCellColumn).Validation
        Call .Delete
        Call .Add( _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlEqual, _
            Formula1:="=" & ValueListRange.Address(ReferenceStyle:=Application.ReferenceStyle, External:=True) _
        )
        .IgnoreBlank = True
    End With
End Sub

Sheet2.vba

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Columns(ListColumn), Target) Is Nothing Then Exit Sub
    Call UpdateValidationList
End Sub

XXX_NgPattern.vba

Option Explicit

' https://twitter.com/TINtaka777/status/1513670209881911297
' 「一部の内容に問題が見つかりました」の原因は、入力規則リストの元の値のオーバーフローだった！ | damのブログラミング雑記ブログ
' https://dampgblog.hinohikari291.com/validation-list-overflow/

Sub TestDataValidation()
'    Dim WorkFormula: WorkFormula = WorksheetFunction.Rept("A,", 127) & "Z" ' 255文字→OK
    Dim WorkFormula: WorkFormula = WorksheetFunction.Rept("A,", 128) & "XXX,YYY,ZZZ" ' 256文字以上→NG
    With Sheet1.Range("A1").Validation
        Call .Delete
        Call .Add(Type:=xlValidateList, Operator:=xlEqual, Formula1:=WorkFormula) ' 直接文字列指定の場合、カンマ含めて255文字まで
    End With
End Sub