Home » エクセルマクロ・Excel VBAの使い方 » 配列 » VBAで全シート名を配列変数に代入する

VBAで全シート名を配列変数に代入する

対象:Excel2007, Excel2010, Excel2013

「excel シート名を配列に vba」
という検索で、このサイト・インストラクターのネタ帳へのアクセスがありました。

VBA(Visual Basic for Applications)で、シート名を配列変数に格納・代入するにはどのようなコードを書けばいいのかを探している方による検索です。

そもそも、Sheetsコレクションオブジェクトを利用すれば、

全シート名を配列変数に代入する

以下のようなカウンター変数を使ったFor~Nextループや、
 Dim i As Long
 For i = 1 To Sheets.Count
  Debug.Print Sheets(i).Name
 Next i
以下のようなオブジェクト変数を使ったFor Each~Nextループで、
 Dim sh As Object
 For Each sh In Sheets
  Debug.Print sh.Name
 Next sh
全シートの名前を取得できますから、本当に配列変数に格納する必要があるのか、という部分は考えていただきたいところです。

その上で、他の処理との関連で、配列にしておくほうが確かに便利なこともあります。ここでは2つのマクロをご紹介しておきます。

[スポンサードリンク]

全シート名を配列変数に格納するサンプルマクロ

以下のようなマクロで、アクティブなブックのすべてのシートの名前を、配列に格納できます。


Sub シート名を配列に格納する()

 Dim sh_name() As String
 Dim cnt As Long
 Dim i As Long

 cnt = Sheets.Count
 ReDim sh_name(1 To cnt)

 For i = 1 To cnt
  sh_name(i) = Sheets(i).Name
 Next i

 Stop

End Sub

上記のマクロを実行すると、Stop文の箇所でステップ実行モードになりますから、その状態で、メニュー[表示]-[ローカルウィンドウ]をクリックして、ローカルウィンドウを表示すれば、配列変数・sh_nameに、全シート名が代入されていることを確認できます。

全シート名を配列変数に代入する

コードの意味は以下のとおりです。

プロシージャを実行してみないとシートの枚数はわかりませんから、とりあえず要素数が決まっていない動的配列の形で、配列変数を宣言しておいてから、
 Dim sh_name() As String
シートの枚数がわかったら、要素数を再定義します。
 cnt = Sheets.Count
 ReDim sh_name(1 To cnt)

その後、カウンター変数を使ったFor~Nextループで、シート名を配列変数に代入しています。
 For i = 1 To cnt
  sh_name(i) = Sheets(i).Name

Excelマクロの場合、配列変数の添え字は「1」から始まっているほうが便利なことが多いので、ここでは、
 ReDim sh_name(1 To cnt)
としましたが、「0」始まりにしたいという場合は、
 cnt = Sheets.Count
 ReDim sh_name(cnt - 1)
 For i = 0 To cnt - 1
  sh_name(i) = Sheets(i + 1).Name
 Next i
としてください。

オブジェクト変数を使って全シート名を配列変数に格納するサンプルマクロ

全シートに対して処理を行うときに便利なFor Each~Nextループは、For~Nextループでも書くことができます。

For Each~NextループとFor~Nextループを比べると、For Each~Nextループのほうが、実行スピードが速いのですが、今回のケースではその差がわかるほどシートの枚数が多いということは考えにくいので、上述したFor~Nextループを使うコードのほうが、可読性の面からよさそうですが、以下のようなFor Each~Nextループを使ったマクロでも、すべてのシートの名前を、配列に格納できます。参考までにご確認ください。


Sub シート名を配列に格納する_オブジェクト変数()

 Dim sh_name() As String
 Dim sh As Object
 Dim n As Long

 ReDim sh_name(1 To Sheets.Count)

 n = 1
 For Each sh In Sheets
  sh_name(n) = sh.Name
  n = n + 1
 Next sh

 Stop

End Sub

先のFor~Nextループを使ったマクロと同じく、とりあえず配列変数の宣言をしておいて、
 Dim sh_name() As String

あとからサイズを決めます。
 ReDim sh_name(1 To Sheets.Count)

配列の添え字用変数を初期化して、
 n = 1

For Each~Nextループの中で、
 For Each sh In Sheets

シート名を格納し、
  sh_name(n) = sh.Name

添え字用変数をインクリメントします。
  n = n + 1
For~NextループとFor Each~Nextループの区別が曖昧な、マクロ初心者の方の場合、この変数のインクリメントを忘れがちです。

[スポンサードリンク]

Home » エクセルマクロ・Excel VBAの使い方 » 配列 » VBAで全シート名を配列変数に代入する

TrackBack:0

TrackBack URL

Home » エクセルマクロ・Excel VBAの使い方 » 配列 » VBAで全シート名を配列変数に代入する

「配列」の記事一覧

検索


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

.