なんでExcelすぐ死んでしまうん?
検証した環境
Windows10
Excel2019
VBAによってExcelが破壊される
VBAによってExcelが破壊されることがあります。
破壊されたExcelを立ち上げると、
と出てきます。
修復すると、シートやシート内の値などは復活しますが、セルの色などの外観はそぎ落とされ、不完全な状態になることが多々あります。
なんでExcelすぐ死んでしまうん?
【今回行きついた結論】Excel VBAのValidation.Addのバグが原因
VBAでセルの入力規則のリスト文字に255文字を超えて設定すると壊れます。
百聞は一見に如かず。
正常に動くコード
正常に入力規則のリストA1セルに設定されます。
Sub test255() Dim list255 As String list255 = WorksheetFunction.Rept("a", 255) Range("A1").Validation.Add xlValidateList, , , list255 End Sub
Excelが壊れるコード
これが問題のコード。A2セルにaを256文字設定します。
Sub test256() Dim list256 As String list256 = WorksheetFunction.Rept("a", 256) Range("A2").Validation.Add xlValidateList, , , list256 ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & "validateAdd256.xlsm" End Sub
これを実行して、保存されたvalidateAdd256.xlsmを開き直すと・・・
壊れた・・・。
なんで?
Validate.Addの構文は次の通りです。
式.Add (Type, AlertStyle, Operator, Formula1, Formula2)
この中の Formula1について、ドキュメント↓を読んでいると気になるところが。
Validation.Add メソッド (Excel) | Microsoft Docs
データ入力規則での条件式の最初の部分を指定します。 値は 255 文字を超えてはいけません。
これだわ。完全にこれだわ。
どうも255文字を超えては「いけない」みたいです。
超えるとどうなるかは、特に書いてありませんが、ここまで見て頂いた通り、Excelが壊れます(白目)。
これは書いておいてほしいところです。
小一時間ハマってしまいましたよ・・・。
遭遇する可能性がある例
「普通、入力規則に256文字以上も登録することないだろう」
と思われた方もいるかもしれませんが、よくある例として入力規則のリストにたくさん登録したいとき。
こんな感じ。
これを動的にリスト化しようとして次のようなコードでやってしまうと、255文字の制限に引っ掛かってしまいます。
Sub testAddList() Dim list257 As String Dim i As Long For i = 1 To 129 list257 = list257 & "," & "a" Next i list257 = Right(list257, Len(list257) - 1) Range("A3").Validation.Add xlValidateList, , , list257 ThisWorkbook.SaveCopyAs ThisWorkbook.path & "\" & "validateAdd257.xlsm" End Sub
最終的に変数list257の長さは257になっているので、このコードを実行したExcelを開いてみると・・・
ああああ・・・・。もう手遅れだ・・・。
終わりに
Excel VBAでファイルの一部に問題が見つかったときの考えられる一つの原因について紹介してみました。
多分、これに限らず、いくつか壊れる原因あるんだろうな・・・。
早く修正されることを祈って・・・。
なんでExcelすぐ死んでしまうん?
というわけで、ここまでお読みいただきありがとうございました。