シート名の変更を検知する(Excel)

各所から集めた大量のExcelブックを処理するとき、配布した様式ブックに保護をかけ忘れると、それはもう、好き放題にシート名をいじくられるｗ

マクロで処理しようにも、シート名とかシートの順序を変えられると、ちょっと対応がむつかしいので、シート名が本来のあるべき姿から変わり果ててしまったブックを検出するメソッドを作ってみた。

コーディング

Worksheetsコレクションのインデックスに、ブックに存在しないシート名を指定すると、実行時エラーが出るので、その性質を利用した。

リスト１　標準モジュール

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)からの３行

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というブックを置き、シート名を

f:id:akashi_keirin:20190509073949j:plain

このように設定しておく。

で、次のコードを実行。

リスト２　標準モジュール

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を２回呼んでいるが、１回目は「空気」というTest.xlsxには存在しないシート名を配列にぶち込んでいる。

２回目は、全てTest.xlsxに存在するシート名を配列にぶち込んだ。