Home » エクセルマクロ・Excel VBAの使い方 » ユーザー定義関数 » 表示されているシート枚数を取得するユーザー定義関数

表示されているシート枚数を取得するユーザー定義関数

対象:Excel2007, Excel2010, Excel2013, Windows版Excel2016

「vba 非表示のシート カウントしない sheets.count」
という検索キーワードで、このサイト・インストラクターのネタ帳へのアクセスがありました。

Excel VBA(Visual Basic for Applications)では、Sheets.Countプロパティで、全シートの数を取得できますが、すべてのシートの枚数ではなく、非表示シートを除外した、表示されているシートの枚数だけを取得する方法を探していた方による検索です。

表示されているシートの枚数を取得するプロパティは存在しない

オブジェクトブラウザーで確認しても、Sheetsコレクションオブジェクトに、表示されているシートの枚数を取得するらしきプロパティは見当たりませんから、

表示されているシート枚数を取得するユーザー定義関数

地道にカウントするしかありません。

Visibleなどというプロパティも存在するので、使えないかなと期待したのですが、何だか妙なプロパティなのです、SheetsのVisibleは。。
[スポンサードリンク]

表示されているシートの枚数をカウントするExcelマクロ

以下のようなマクロで、表示されているシートの枚数を取得できます。

Sub 表示されているシートの数をカウントする()

 Dim n As Long: n = 0 ' 表示されているシートの数
 Dim sh As Object ' WorksheetまたはChart

 For Each sh In Sheets
  If sh.Visible Then n = n + 1
 Next sh

 MsgBox _
   "全シート数「" & Sheets.Count & "」のうち" & vbCrLf & _
   "表示されているシートは「" & n & "」です。"

End Sub

全シートに対するFor Each~Nextループの中で、
 For Each sh In Sheets

各シートのVisibleプロパティを調べて、表示されていたときに変数・nをインクリメントして、
  If sh.Visible Then n = n + 1

最後にメッセージを表示しています。

表示されているシートの枚数を取得するユーザー定義関数

表示されているシートの枚数を取得する処理が頻繁にあるのなら、ユーザー定義関数を作っておくのも一案でしょう。

Function VisbleSheetsCount(Optional ブック名 As String) As Variant

 Dim n As Long: n = 0 ' 表示されているシートの数
 Dim sh As Object ' WorksheetまたはChart
 Dim bk As Workbook: Set bk = ActiveWorkbook

 On Error GoTo ERR_HNDL

 If ブック名 <> "" Then
  Set bk = Workbooks(ブック名)
 End If

 For Each sh In bk.Sheets
  If sh.Visible Then n = n + 1
 Next sh

 VisbleSheetsCount = n
 Exit Function

ERR_HNDL:
 Err.Clear
 VisbleSheetsCount = CVErr(xlErrNA)

End Function

ここでは、シートの枚数をカウントするブック名を、引数で指定するユーザー定義関数にしてみました。
 Function VisbleSheetsCount(Optional ブック名 As String) As Variant

引数が指定されなかった場合には、アクティブなブックで、表示されているシートだけをカウントします。
 Dim bk As Workbook: Set bk = ActiveWorkbook
 If ブック名 <> "" Then
  Set bk = Workbooks(ブック名)
 End If

ブック名を引数で指定する関係で、For Each~Nextループで処理する対象をしているするオブジェクト式が、先ほどのマクロとは少しだけ異なります。
 For Each sh In bk.Sheets

存在しないブック名が引数に指定された場合、エラー値を返すようにしています。
  On Error GoTo ERR_HNDL
 ERR_HNDL:
  Err.Clear
  VisbleSheetsCount = CVErr(xlErrNA)

エラー値を返す可能性があるため、このFunctionプロシージャの戻り値はバリアント型になっています。
 Function VisbleSheetsCount(Optional ブック名 As String) As Variant

[スポンサードリンク]

Home » エクセルマクロ・Excel VBAの使い方 » ユーザー定義関数 » 表示されているシート枚数を取得するユーザー定義関数

「ユーザー定義関数」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.