「一部の内容に問題が見つかりました」の原因は、入力規則リストの元の値のオーバーフローだった!
目次
ExcelVBAで作った自作ツールを開くと、以下画像のようなメッセージが出てきた。
結論を先に書けば、このメッセージが出た原因は、入力規則のリストの元の値に設定している文字列がオーバーフロー(255文字超)していたこと、そして対応としては、元の値としてセル範囲を指定したことで解決した。
以下はどのように原因を特定したか、そして対応策の説明である。
入力規則が設定されている列を削除するとエラーが出なくなった
エラーの子画面で「はい」を選ぶとファイルは開くものの、書式が崩れていたりボタンなどが消え去っている。そのまま保存して再度開くとエラーメッセージは出ないが、これだと何が原因か分からない。 「いいえ」を選ぶとファイルは開かない。 「どうやって原因を特定すればいいの?」と、しばし途方に暮れていた。
偶然、別のパソコンでまったく同じファイルが開かれていた。そこで、このファイルを調査することとした。
まずは、数個あったシートを1個ずつ削除して保存し、閉じて開きなおす(リオープンする)とどうなるかをチェック。最初はエラーが続いたが、あるシートを削除したところで、エラー無く開くことができた。これにて、このシートに問題有りと当たりを付ける。
次に、このシートの値が設定されている右端の列から、順に列を削除してリオープンするとどうなるかをチェック。すると、ある列を削除したところでエラーが出なくなった。これにて、問題のある列を特定できた。
その列は、入力規則でリストから選択するように設定されている。試しに、列の削除ではなく、表示されている選択値を削除して保存し、リオープンしてみた。結果はエラーとなった。 ということは、表示されている値ではなく、リストの内容に問題がありそうだ。
入力規則リストの元の値の長さが原因!
ネットで当該エラーメッセージを検索していると、シート名に日本語が使われているのが問題とか、VBAのモジュール名に日本語が使われているのが問題というのがヒットしたが、日本語をローマ字に変更しても解決しなかった。
また、Excel2016向けに配信された更新プログラムの障害で当該エラーが出る、バージョン(ビルド)を古いものに戻すと正常に開けるという記事もあったが、その対応をしても解決しなかった。
いろいろとネットで調べているうちに、リスト内の文字列長に制限あることが判明(考えれば当然)。私が使っているExcel2016では255文字が上限のようだ。
そこで、問題となっている列について、上のセルから順にリストの文字列長を確認したところ、255文字を超えているセルを発見!

このセルを含む行を削除して保存後にリオープンしたところ、エラーが出なくなった! 入力規則の元の値の長さが、問題の原因だ!
リストの元の値をセル範囲で設定して解決!
このエラーの原因となった列の入力規則は、プログラム(Excel VBA)で作成している。
プログラムでは、別シートに設定しているリスト表示用のセル範囲から1個ずつデータを取得し、カンマをデリミタとして結合して、リストの元の値としていた。 このリスト表示用のセル範囲の値が多すぎ、かつ、長すぎたために、リスト制限にひっかかってエラーになっていたわけだ。
そこで、セル範囲からデータを1個ずつ取得するのではなく、直接セル範囲をリストの元の値とするよう変更。コード的にもこの方が断然スマート。
リオープンしたところ、エラーが出ずに開いた!
今回の対応に2~3日ほどかかった。疲れたけど解決して良かった。
追記)エラーを起こすサンプルコードとその修正版
|
|
上図はsample1を実行した状態。sample1は、E3セルにB列のリストをベースとして入力規則を作成する機能。B3:B18に対して名前付き範囲 “カテゴリーリスト” が設定されており、その範囲のメンバーを一つずつ取得して、入力規則用の文字列を作成している。 リストの一番最後に「Mystery & Thrillers」が含まれてしまっている。手作業なら256以上はガードがかかっていて設定できないのだが、プログラム実行では設定できてしまう。
このブックを保存して開くと「一部の内容に問題が見つかりました」のエラーとなり、書式が崩れた状態となる。またマクロコードが消えたり実行できなくなったりしている。つまり、まともに使えない状態になっている。
sample2を使えば入力規則は正常に作成され、リオープンしても全く問題ない。コードも短くなるしリストの文字数も気にする必要がなくなる。
sample2は名前付き定義を使っているが、代わりに "=$B$3:$B$19"
のようにセル範囲を指定しても、もちろん問題ない。
|
|