こんにちは、最近はVBA書いてたりするtoyohamaです。
しばらくWindowsでコーディングしていたので、Macの日本語切り替えで1を押してしまうクセに悩まされています……
やりたかったこと
- 数万行、数十列のExcelシート(以下、
対象Excel)がある - 事前に発見したい文字列パターンが用意されている
- 対象Excelを上の行から順にチェックする
- マッチしたセルは背景色をつける
- マッチしたセルの該当文字列だけに色を付ける
パターンマッチ例
例えばなんだけどここがマッチさせたい文字列で、なんだけどはあってもなくてもいい、みたいなパターン
期待する結果
サンプルマクロ
Sub tempMacro()
'正規表現
Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")
With reg
.Pattern = "例えば(なんだけど)*ここ"
.IgnoreCase = True
.Global = True
End With
Dim r As Integer, c As Integer
c = 2 ' 今回はB列固定で
For r = 2 To 10 '2行目から
Dim cellStr As String
cellStr = CStr(Cells(r, c))
Dim sMatch, sMatches
Set sMatches = reg.Execute(cellStr)
For Each sMatch In sMatches
'セルに色付け
Cells(r, c).Interior.ColorIndex = 3
'マッチした文字列だけ色をつける
Dim sLen As Integer: sLen = Len(sMatch.Value)
Dim sStart As Integer: sStart = sMatch.FirstIndex + 1
Cells(r, c).Characters(Start:=sStart, Length:=sLen).Font.ColorIndex = 4
Next sMatch
Next r
End Sub
当然これで期待する成果は得られましたが……
マクロの動作が不安定になる
- うまくいく対象Excelとうまくいかない対象Excelがある(まあわかる)
- 同じ対象Excelでもエラーになったりならなかったりする(？)
- よくある、2回めの起動でうまくいかないというわけでもなく、1回目でも失敗したりしなかっりする(!?)
- 対象Excelを保存すると文字列データが壊れてしまう(!!?)
- PCのスペックに依存しているわけではない(2台で検証)
調査
Charactersは256文字までしか扱えないとか、文字色変更はとても負荷がかかる処理だからコードをわける、みたいなことをやってもダメで、最終的に「対象エクセルを10行ずつ増やして、どこで壊れるかを検証」したところ、以下のようなことがわかりました。
- パターンマッチに依存して起こっているわけではない
- 同じ文字列しかない対象エクセルでも起こった
- 何十パターンあっても壊れないものは壊れない
- 行数が多くて起こっているわけでもない
- 上記と同じく、サクッといくやつはいく
- 10行でもだめな対象Excelもある
うまくいかない対象Excelはフォントが混在していた
地道な検証の結果、フォントが混在している対象Excelはマクロの動作が遅く、終了したあとも不安定になり、保存すると壊れることが確認できました。
というわけで、対象Excelの全セルのフォントを揃えた結果、再起動せず何回マクロを起動しても安定して動作するようになりました。
Cells.Font.Name = "ＭＳ ゴシック"
Cells.Font.Size = 11
VBAでやりたければ、コードの冒頭にこんな感じで全セルのフォントを指定してやればOKです。
(フォントサイズは正直関係なさそうですが、見栄えのために)
まとめ
そんなところで動作が安定しなくなるのかよ、という思いはなくもないですが、とりあえず解決してよかったです。
めちゃくちゃネットを漁ったけど、この動作については全く情報がなかったです。もし別の解決策があればアドバイスいただけると嬉しいです。