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

2019年5月18日 VBA excel
目次

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

Sub sample1()

  ' 入力規則のリストに設定する文字列を作成
  cnt = 1
  For Each elem In Range("カテゴリーリスト")
    If cnt = 1 Then
      buf = elem
    Else
      buf = buf & "," & elem
    End If
    cnt = cnt + 1
  Next

  ' 入力規則を設定
  With Range("E3").Validation
    .Delete
    .Add Type:=xlValidateList, _
       Operator:=xlEqual, _
       Formula1:=buf
  End With

End Sub

上図はsample1を実行した状態。sample1は、E3セルにB列のリストをベースとして入力規則を作成する機能。B3:B18に対して名前付き範囲 “カテゴリーリスト” が設定されており、その範囲のメンバーを一つずつ取得して、入力規則用の文字列を作成している。 リストの一番最後に「Mystery & Thrillers」が含まれてしまっている。手作業なら256以上はガードがかかっていて設定できないのだが、プログラム実行では設定できてしまう。

このブックを保存して開くと「一部の内容に問題が見つかりました」のエラーとなり、書式が崩れた状態となる。またマクロコードが消えたり実行できなくなったりしている。つまり、まともに使えない状態になっている。

図２　書式が崩れた状態

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Sub sample2()

  ' 入力規則を設定
  With Range("E3").Validation
    .Delete
    .Add Type:=xlValidateList, _
       Operator:=xlEqual, _
       Formula1:="=カテゴリーリスト"
  End With

End Sub
次のページ