「一部の内容に問題が見つかりました」の原因は、入力規則リストの元の値のオーバーフローだった！

2019年5月18日 VBA excel

ExcelVBAで作った自作ツールを開くと、以下画像のようなメッセージが出てきた。

結論を先に書けば、このメッセージが出た原因は、入力規則のリストの元の値に設定している文字列がオーバーフロー（255文字超）していたことだ。 その対応としては、入力規則の元の値に文字列を直接設定するのではなく、セル範囲を指定したことで解決した。

以下はどのように原因を特定したか、そして対応策の説明である。

入力規則が設定されている列を削除するとエラーが出なくなった

エラーの子画面で「はい」を選ぶとファイルは開くものの、書式が崩れていたりボタンなどが消え去っている。そのまま保存して再度開くとエラーメッセージは出ないが、これだと何が原因か分からない。 「いいえ」を選ぶとファイルは開かない。 「どうやって原因を特定すればいいの？」と、しばし途方に暮れていた。

偶然、別のパソコンでまったく同じファイルが開かれていた。そこで、このファイルを調査することとした。

まずは、数個あったシートを1個ずつ削除して保存し、閉じて開きなおす（リオープンする）とどうなるかをチェック。最初はエラーが続いたが、あるシートを削除したところで、エラー無く開くことができた。これにて、このシートに問題有りと当たりを付ける。

次に、このシートの値が設定されている右端の列から、順に列を削除してリオープンするとどうなるかをチェック。すると、ある列を削除したところでエラーが出なくなった。これにて、問題のある列を特定できた。

その列は、入力規則でリストから選択するように設定されている。試しに、列の削除ではなく、表示されている選択値を削除して保存し、リオープンしてみた。結果はエラーとなった。 ということは、表示されている値ではなく、リストの内容に問題がありそうだ。

入力規則リストの元の値の長さが原因！

ネットで当該エラーメッセージを検索していると、シート名に日本語が使われているのが問題とか、VBAのモジュール名に日本語が使われているのが問題というのがヒットしたが、日本語をローマ字に変更しても解決しなかった。

また、Excel2016向けに配信された更新プログラムの障害で当該エラーが出る、バージョン（ビルド）を古いものに戻すと正常に開けるという記事もあったが、その対応をしても解決しなかった。

いろいろとネットで調べているうちに、リスト内の文字列長に制限あることが判明（考えれば当然）。私が使っているExcel2016では255文字が上限のようだ。

そこで、問題となっている列について、上のセルから順にリストの文字列長を確認したところ、255文字を超えているセルを発見！

このセルを含む行を削除して保存後にリオープンしたところ、エラーが出なくなった！ 入力規則の元の値の長さが、問題の原因だ！

リストの元の値をセル範囲で設定して解決！

このエラーの原因となった列の入力規則は、プログラム（Excel VBA）で作成している。

プログラムでは、別シートに設定しているリスト表示用のセル範囲から1個ずつデータを取得し、カンマをデリミタとして結合して、リストの元の値としていた。 このリスト表示用のセル範囲の値が多すぎ、かつ、長すぎたために、リスト制限にひっかかってエラーになっていたわけだ。

そこで、セル範囲からデータを1個ずつ取得するのではなく、直接セル範囲をリストの元の値とするよう変更。コード的にもこの方が断然スマート。

リオープンしたところ、エラーが出ずに開いた！

今回の対応に２～３日ほどかかった。疲れたけど解決して良かった。

追記）エラーを起こすサンプルコードとその修正版

次の図１の E3 セルのリストは、B 列の値を元に入力規則を設定することで生成したもの。 また、 C 列は B 列の文字数の累計を表示したもの。 例えば C18 セルの 257 は、 B3 から B18 の各セルの文字列長の合計だ。 255 文字を超過しているので、手入力ならガードがかかって設定できないのだが、（まずいことに）プログラムからであれば設定可能だ。 実際、文字数累計が 255 を超過しているのに、リストの一番最後に「Mystery & Thrillers」が含まれてしまっている。
プログラムから入力規則の制限を無視して設定できることが、今回のような原因特定困難な不具合を作ることになる。

図１　sample1を実行して入力規則を生成

上図の E3 セルの入力規則を設定するプログラムが、以下の sample1 。B3 から B18 のセル範囲に対して "カテゴリーリスト" という名前付き範囲が設定されており、その範囲のメンバーを一つずつ取得して、入力規則用の文字列を生成している。

 1Sub sample1()
 2
 3  ' 入力規則のリストに設定する文字列を作成
 4  cnt = 1
 5  For Each elem In Range("カテゴリーリスト")
 6    If cnt = 1 Then
 7      buf = elem
 8    Else
 9      buf = buf & "," & elem
10    End If
11    cnt = cnt + 1
12  Next
13
14 ' 入力規則を設定
15  With Range("E3").Validation
16    .Delete
17    .Add Type:=xlValidateList, _
18       Operator:=xlEqual, _
19       Formula1:=buf
20  End With
21
22End Sub

このプログラムを実行して（まずい）入力規則を生成後、ブックを保存して開くと「一部の内容に問題が見つかりました」のエラーとなり、書式が崩れた状態となる。 さらには、なんと作成したプログラムが消えてしまう！！
つまり、まともに使えない状態になっていて、 こうなると原因特定は非常に困難となる （私の場合は、偶然、他PCで不具合が出る前の同じブックを開いていたから原因追及できたが）。 そして、最初からプログラムを打ち直し、おかしな入力規則を生成して再度プログラムが消え、またプログラムを打ち直し、・・・というループに陥るかもしれない。

図２　書式が崩れた状態

さて、次の sample2 は sample1 の改良版だ。 sample2 を使えば入力規則は正常に作成され、リオープンしても全く問題ない。コードも短くなるしリストの文字数も気にする必要がなくなる。 sample2は名前付き定義を使っているが、代わりに "=$B$3:$B$19" のようにセル範囲を指定しても、もちろん問題ない。

 1Sub sample2()
 2
 3  ' 入力規則を設定
 4  With Range("E3").Validation
 5    .Delete
 6    .Add Type:=xlValidateList, _
 7       Operator:=xlEqual, _
 8       Formula1:="=カテゴリーリスト"
 9  End With
10
11End Sub
次のページ