適材適所

WindowsやPowerShellやネットワーク、IBMなどのシステム系の話やポイ活など気になったことも載せているブログです。

【Excel VBA】"ファイル名"の一部の内容に問題が見つかりました。可能な限り内容を回復しますか?の原因の1つは入力規則

f:id:shinmai_papa:20211105130629p:plain

なんでExcelすぐ死んでしまうん?

検証した環境

Windows10
Excel2019

VBAによってExcelが破壊される

VBAによってExcelが破壊されることがあります。

破壊されたExcelを立ち上げると、

f:id:shinmai_papa:20211105130851p:plain

と出てきます。

修復すると、シートやシート内の値などは復活しますが、セルの色などの外観はそぎ落とされ、不完全な状態になることが多々あります。

なんでExcelすぐ死んでしまうん?

【今回行きついた結論】Excel VBAのValidation.Addのバグが原因

VBAでセルの入力規則のリスト文字に255文字を超えて設定すると壊れます。

百聞は一見に如かず。

正常に動くコード

正常に入力規則のリストA1セルに設定されます。

f:id:shinmai_papa:20211105130633p:plain

 
Sub test255()
   Dim list255 As String
   list255 = WorksheetFunction.Rept("a", 255)
   Range("A1").Validation.Add xlValidateList, , , list255
End Sub

Excelが壊れるコード

これが問題のコード。A2セルにaを256文字設定します。

f:id:shinmai_papa:20211105130637p:plain

 
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を開き直すと・・・

f:id:shinmai_papa:20211105130918p:plain

壊れた・・・。

なんで?

Validate.Addの構文は次の通りです。

 
.Add (Type, AlertStyle, Operator, Formula1, Formula2)

この中の Formula1について、ドキュメント↓を読んでいると気になるところが。

Validation.Add メソッド (Excel) | Microsoft Docs

データ入力規則での条件式の最初の部分を指定します。 値は 255 文字を超えてはいけません。

これだわ。完全にこれだわ。

どうも255文字を超えては「いけない」みたいです。

超えるとどうなるかは、特に書いてありませんが、ここまで見て頂いた通り、Excelが壊れます(白目)。

これは書いておいてほしいところです。

小一時間ハマってしまいましたよ・・・。

遭遇する可能性がある例

「普通、入力規則に256文字以上も登録することないだろう」

と思われた方もいるかもしれませんが、よくある例として入力規則のリストにたくさん登録したいとき。

f:id:shinmai_papa:20211105130642p:plain

こんな感じ。

これを動的にリスト化しようとして次のようなコードでやってしまうと、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を開いてみると・・・

f:id:shinmai_papa:20211105130648p:plain

ああああ・・・・。もう手遅れだ・・・。

終わりに

Excel VBAでファイルの一部に問題が見つかったときの考えられる一つの原因について紹介してみました。

多分、これに限らず、いくつか壊れる原因あるんだろうな・・・。

早く修正されることを祈って・・・。

なんでExcelすぐ死んでしまうん?

というわけで、ここまでお読みいただきありがとうございました。