シート名の変更を検知する(Excel)
シート名の変更を検知する(Excel)
各所から集めた大量のExcelブックを処理するとき、配布した様式ブックに保護をかけ忘れると、それはもう、好き放題にシート名をいじくられるw
マクロで処理しようにも、シート名とかシートの順序を変えられると、ちょっと対応がむつかしいので、シート名が本来のあるべき姿から変わり果ててしまったブックを検出するメソッドを作ってみた。
コーディング
Worksheets
コレクションのインデックスに、ブックに存在しないシート名を指定すると、実行時エラーが出るので、その性質を利用した。
リスト1 標準モジュール
Public Function hasAppropriateSheetNames( _ ByVal targetBook As Workbook, _ ByRef shNamesArray() As String) As Boolean '……(1)' On Error GoTo Finalizer '……(2)' hasAppropriateSheetNames = False '……(3)' Dim i As Long Dim stalkingHorse As String For i = LBound(shNamesArray) To UBound(shNamesArray) '……(4)' stalkingHorse = targetBook.Worksheets(shNamesArray(i)).Name Next hasAppropriateSheetNames = True '……(5)' Finalizer: Call Err.Clear End Function
(1)の
Public Function hasAppropriateSheetNames( _ ByVal targetBook As Workbook, _ ByRef shNamesArray() As String) As Boolean
で引数設定。
今回は、ブックオブジェクトと適切なシート名を格納したString
型配列を受け取るようにした。返り値はBoolean
型。適切なシート名のシートが完備されていればTrue
を返す。
(2)の
On Error GoTo Finalizer
で、エラーが出たら、一番下のラベルにワープするようにした。
必要ないけど、(3)の
hasAppropriateSheetNames = False
で初期値を明示的にFalse
にしているので、エラーが出た瞬間、Finalizer:
ラベルにワープしてそのまま抜ける。つまり、存在しないシート名にぶつかると、False
が変えるしくみ。
(4)からの3行
For i = LBound(shNamesArray) To UBound(shNamesArray) '……(4)' stalkingHorse = targetBook.Worksheets(shNamesArray(i)).Name Next
がメイン。
引数として受け取ったシート名を順にWorksheets
コレクションのインデックスに指定し、取得したWorksheet
オブジェクトのName
プロパティの値(要するにシート名の文字列)を変数stalkingHorse
にぶち込む。変数stalkingHorse
は、ただName
プロパティの返り値を受け取るだけ! まさにStalking Horse!!!!!!!!
存在しないシート名だったら、実行時エラーが出るので、Finalizer:
ラベルまで飛び、エラーをクリアしてFalse
を返す。
無事(4)のFor
ループを抜けたということは、配列shNamesArray
内のシート名は全て存在したということになるので、(5)の
hasAppropriateSheetNames = True
で返り値をTrue
にする。
使ってみる
同じフォルダ内に、Test.xlsx
というブックを置き、シート名を
このように設定しておく。
で、次のコードを実行。
リスト2 標準モジュール
Public Sub test01() Dim shNamesArray() As String shNamesArray = Split("アホ ボケ カス ラッパ 空気") Dim targetBook As Workbook Set targetBook = Workbooks.Open(ThisWorkbook.Path & "\Test.xlsx") Debug.Print hasAppropriateSheetNames(targetBook, shNamesArray) shNamesArray = Split("アホ ボケ カス ラッパ デコスケ") Debug.Print hasAppropriateSheetNames(targetBook, shNamesArray) Call targetBook.Close(False) End Sub
Split
の返り値をString
型の配列にぶち込むことができる、というのは初めて知ったかもしれない。
hasAppropriateSheetNames
を2回呼んでいるが、1回目は「空気
」というTest.xlsx
には存在しないシート名を配列にぶち込んでいる。
2回目は、全てTest.xlsx
に存在するシート名を配列にぶち込んだ。
実行結果
意図どおり。
おわりに
エラーを判定材料に使っているというのがイマイチかも。
他にどんなやり方があるかなあ。