非表示ブックの数を返すFunction(Excel)

非表示Bookの数を返すFunction

アドインとか、個人用マクロブックみたいな、ブックを開いたら裏で勝手に開いているブックの数と、オモテで正々堂々と開いているブックの数を別々に取得する必要があったので作ってみた。

考えかた

Application.WorkbooksコレクションのCountプロパティは、表示／非表示に関係なく、開いているブック全ての数を返すっぽいので、Workbooksコレクションの要素ひとつひとつを調べるしかないと思った。

非表示ブックの拡張子は、たぶん「xlsb」、「xlt」、「xltm」、「xla」、「xlam」の５つっぽいので、拡張子で判定できると思った。(実は「テンプレートファイル」ってのが何のことだか分かっていないので、「xlt」と「xltm」は入れておくべきなのかどうなのか分かっていない。←調べろよ！)

コード

リスト１　標準モジュール

Public Function getHiddenBooksCount( _
                   ByVal excelApp As Excel.Application) As Long
  Dim ret As Long
  ret = 0
  Dim Wb As Workbook
  For Each Wb In excelApp.Workbooks
    If isHiddenBook(getExtentionString(Wb.Name)) Then _
      ret = ret + 1
  Next
  getHiddenBooksCount = ret
End Function

Private Function getExtentionString( _
                   ByVal targetFileName As String) As String
  Dim positionOfDot As Long
  positionOfDot = InStrRev(targetFileName, ".")
  Dim ret As String
  ret = Right(targetFileName, Len(targetFileName) - positionOfDot)
  getExtentionString = ret
End Function
                   
Private Function isHiddenBook( _
                   ByVal targetExtention As String) As Boolean
  isHiddenBook = True
  Dim ar As Variant
  ar = Split("xlsb xlt xltm xla xlam")
  Dim i As Long
  For i = LBound(ar) To UBound(ar)
    If StrConv(ar(i), vbLowerCase) = _
       StrConv(targetExtention, vbLowerCase) Then _
      Exit Function    '……(*)'
  Next
  isHiddenBook = False
End Function

メインのgetHiddenBooksCountから、getExtentionStringとisHiddenBookを呼び出して、非表示ブックの数をカウントしている。

isHiddenBookの(*)のところでStrConv関数を用いているのがミソ。

単純に素の拡張子文字列で比較しようとすると、getExtentionStringで取得した拡張子が大文字だったら一致したと見なされないのである。

たとえば、個人用マクロブックの拡張子は「XLSB」だった。

使ってみた

プロジェクトエクスプローラーは

この状態。

で、イミディエイト・ウインドウに
?getHiddenBooksCount(Application)
と入力して[Enter]をポチッ。

とりあえず意図どおりの結果が出た。

おわりに

テストが不十分なので、これで良いのかどうかはよく分かっていない。

追記

id:imihito さんからコメントをいただいたので、それを参考に修正。

id:imihito さんによると、

ブックが非表示になるのは `Workbook.IsAddin`がTrueのとき、
そして`Window.Visible`がFalseのとき

との由。

つまり、上記リスト１のisHiddenBookメソッドを、

WorkbookオブジェクトのIsAddinプロパティがTrue

または

Workbookオブジェクトの全ての[Window].VisibleプロパティがFalse

だったらTrueを返すようなものにすればよい。

リスト２　標準モジュール

Private Function isHiddenBook( _
                   ByVal targetBook As Workbook) As Boolean    '……(1)'
  isHiddenBook = True    '……(2)'
  If targetBook.IsAddin Then Exit Function
  Dim i As Long
  For i = 1 To targetBook.Windows.Count    '……(3)'
    If targetBook.Windows(i).Visible Then _
      isHiddenBook = False: Exit Function
  Next
End Function

(1)の

Private Function isHiddenBook( _
                   ByVal targetBook As Workbook) As Boolea

もはや、引数からして変えた。別に拡張子で判定する必要はないし、ExcelVBAer (id:x1xy2xyz3) さんからいただいたコメントからしても、「.xlsb」のブックを非表示ブックとカウントしてしまうのはまずい。

よって、Workbookオブジェクトを引数として受け取るようにした。

(2)からの２行

isHiddenBook = True
If targetBook.IsAddin Then Exit Function

では、最初に返り値をTrueにしておき、IsAddinプロパティだったら即returnするようにした。

ここを通過すると、アドインではないことになるので、後はWorkbookオブジェクトが持つ全てのWindowオブジェクトを調べる段階へ進む。

(3)から４行（実質３行）がそれ。

For i = 1 To targetBook.Windows.Count
  If targetBook.Windows(i).Visible Then _
       isHiddenBook = False: Exit Function
Next

WindowオブジェクトのVisibleプロパティに１つでもTrueのものがあったら、即Falseをreturn、という仕組み。

再び、おわりに

これによって、getExtentionStringメソッドは用なし芳一と相成りました。