コピペからのVBA　その１～シート一覧の作成～

皆さん、こんにちは。JMIRIの独身貴族「ひろきち」です。

Excelで作業をしているときに『特定の操作を繰り返す』や、『毎月同じ作業をする』といったケースはありませんか？
こういった作業で楽をしたいと思ってネットで調べると、たいてい「VBA（VisualBasic for Application）を使えばできる」という結論に行き着くことが多いと思います。

ただ、「プログラミングって難しそう」と敷居が高いのも事実です。
なので、今回は

とりあえず貼り付ければ動いて、仕事でも使い道がありそう

なVBAプログラムを解説します。

まずは下準備

ExcelでVBAを作るには、標準で無効化されている「開発」機能を有効にする必要があります。

この手順は、当Blogのこのページに書いてありますので、この手順に沿って

VBAの画面が表示される

ところまで進めてください。（参照設定はとりあえず不要です）

本日のお題

本日のお題は

開いているExcelファイルのシート一覧を作成しよう

です。

開いているExcelのシート一覧を作成しよう：要件定義

まずは要件定義です。これから作るVBAプログラムに、どのような機能を作るのかを事前にリストアップします。

今回は

1. 機能の名前は「シート一覧を作成する」とする
2. 開いているExcelファイルのシート名を一覧にする
3. シート名一覧は「【シート一覧】」という名前のシートを新規で追加し、そこに表示する
4. 開いているExcelファイルに、すでに「【シート一覧】」がある場合、そのシートを削除してから作成する

としましょう。

作っている最中に「○○な機能もほしいなぁ」と思ったら、上記の要件一覧に追加してください。
その都度追加していくと本当にほしい機能の完成が遅くなってしまいますので、要件一覧に追加する際は「この機能はVer２で追加予定」のように書いておくのもありかと思います。

開いているExcelのシート一覧を作成しよう：設計

小さいプログラムを作る場合や開発に慣れてきたら、この作業をすっ飛ばしていきなりプログラムをTry&Errorで書き進めるのもアリですが、慣れないうちは大げさにならない程度に事前に全体をイメージしてから作る、という点を意識してみましょう。

と言いつつ、いきなりひな形となるプログラムを作ることから始めます。

まずは適当にExcelのファイルをコピーし、壊れてもよい『実験用ファイル』を作成し、それを開いてください。作るプログラムの性質上、複数のシートがあるファイルがお勧めです。

次に、そのファイルを開いた状態でメニューのリボンから「開発」→「Visual Basic」を選択すると「VisualBasic for Application」の画面が開きます。

「VisualBasic for Application」で、画面左の「プロジェクト」一覧の中に今開いているファイルが太文字で表示されていると思いますので

右クリック→「挿入」→「標準モジュール」

と選択してください。

今回はこの「標準モジュール」の中にプログラムを書いていきますので、さっそく、

この「標準モジュール」の中に下のプログラムを張り付けてください。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 開いているExcelファイルのシート一覧を作成する
' Ver0.01
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub シート一覧を作成する()

'ここに処理を書く

End Sub

これは何かというと、VBAで作る機能のテンプレートになります。
1行目～4行目は、行の先頭が「'」で始まっていますので「コメント行」となりExcelはこの行を無視して動きます。

機能をどんどん追加していくと、何がどういう処理をするプログラムかわからなくなるので、機能を説明するためのコメントは、忘れずに作りましょう。

次に5行目の「Sub シート一覧を作成する()」という部分ですが、これは何を意味しているかというと

ここから1つの機能（Sub）が始まり、その機能の名前は「シート一覧を作成する」という名前です。

と宣言（定義）している部分となります。この行から「End Sub」までが1機能となり、実際にこれだけでも「シート一覧を作成する」を実行することは可能です。
（『何もしない』という意味のない機能を実行することになります）

それでは、このプログラムのひな形を使って設計（のようなもの）をしていきましょう。

先ほど「『'』で始まる行は無視して動く」というコメント行の話をしましたのでこれを活用します。

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 開いているExcelファイルのシート一覧を作成する
' Ver0.02
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub シート一覧を作成する()

'TODO:１．機能の名前は「シート一覧を作成する」とする

'TODO:２．開いているExcelファイルのシート名を一覧にする

'TODO:３．シート名一覧は「【シート一覧】」という名前のシートを新規で追加し、そこに表示する

'TODO:４．開いているExcelファイルに、すでに「【シート一覧】」がある場合、そのシートを
' 　　　　削除してから作成する

End Sub

とりあえず、要件定義でリストアップした要件をコメントにして張り付けて、この順番で処理が流れていくと仮定しましょう。

ちなみに先頭に「TODO:」とあるのは、後から取りこぼしがないかを検索するときのキーワードとして使います。

あらためて見直してみますと、まず要件1は機能の名前についてなのでプログラミングは不要ですので、消してしまいましょう。

次に要件2～4を見ていきます。
要件3の「シート追加処理」の後に要件4の「重複シート削除」というのは順番が逆な気がしますね。
そもそも「重複シート削除」は同じ名前のシートがあった場合にシート追加できなくなるのを避ける為の処理なので、むしろ要件4は一番最初に処理してもいいくらいです。

さらに言うと、要件4も一覧を表示するための準備になるので、要件3より前になるはずです。

上記の件を加味したのが下のプログラムです。

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 開いているExcelファイルのシート一覧を作成する
' Ver0.03
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub シート一覧を作成する()

'TODO:４．開いているExcelファイルに、すでに「【シート一覧】」がある場合、そのシートを
' 　　　　削除してから作成する

'TODO:３．シート名一覧は「【シート一覧】」という名前のシートを新規で追加し、そこに表示する

'TODO:２．開いているExcelファイルのシート名を一覧にする

End Sub

これならなんとなく動きそうな気がします。

設計というともっと細かいイメージもありますが、本職のエンジニアではないので、まずは

1. 「TODO」コメントで処理をリストアップ
2. 処理の並び替えを行い、なんとなく動きそうな順番に変える
3. TODOコメントに沿ってプログラムを書く
4. 書いたプログラムで動作確認ができたものは「TODO」を消す

というサイクルで深掘りしていくのがお手軽かもしれません。

開いているExcelのシート一覧を作成しよう：開発

それでは、先ほどのひな形の要件ごとにプログラミングをしていきましょう。
基本的に、各要件コメントの次の行から、その機能をプログラムする方針で進めます。

で、「コピペでVBA」という趣旨なので、いきなり完成版です。

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 開いているExcelファイルのシート一覧を作成する
' Ver0.04
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub シート一覧を作成する()

'変数の宣言
Dim row_num As Long         'シート名を張り付けるセルの行番号
Dim col_num As Long         'シート名を張り付けるセルの列番号
Dim ws As Worksheet         '一覧シートを探すためのシート（WorkSheetの略）
Dim ws2 As Variant           '対象となるシート（WorkSheetの略）
Dim sheetName As String     'シート一覧を出力するシート名

'シート一覧のシート名を決める
sheetName = "【シート一覧】"

'各シートを順番に見ていく際に、その都度、再計算や再表示等が行われると
'処理が重くなるので、一時的にオフにする
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'４．開いているExcelファイルに、すでに「【シート一覧】」がある場合、そのシートを
'    削除してから一覧を作成する

'「For Each」を使い、全シートをぐるぐる回す
For Each ws In Worksheets

'シートをぐるぐる回す中で、対象となるシートの名前が
'「【シート一覧】」かどうかを判定
If ws.Name = sheetName Then

'シート名が「【シート一覧】」の場合、重複することになるので
'以下のシート削除処理を実行

'「削除しますか」と聞かれると処理が止まるので、一時的にオフにする
Application.DisplayAlerts = False

'シートの削除
ws.Delete

'「削除しますか」の表示をオンにする
Application.DisplayAlerts = True

'同じ名前のシートはないはずなので、残りのシートは確認する必要がないため
'「For Each」を抜ける。
Exit For

End If

Next ws

'３．シート名一覧は「【シート一覧】」という名前のシートを新規で追加し、そこに表示する

'先頭に空白のシートを追加する
Worksheets.Add before:=Worksheets(1)

'追加したシートの名前を「【シート一覧】」に変更する
ActiveSheet.Name = sheetName

'せっかくなので、わかりやすいよう「【シート一覧】」のタブの色を黄色にする
ActiveSheet.Tab.ColorIndex = 36

'２．開いているExcelファイルのシート名を一覧にする。

'貼り付けを開始するセルの位置を決める。
row_num = 1
col_num = 1

'「【シート一覧】」を削除した時と同じように「For Each」を使い、全シートをぐるぐる回す
For Each ws2 In Sheets

'シートをぐるぐる回す中で、対象となるシートの名前を取り出して、セルに張り付ける
Cells(row_num, col_num).Value = ws2.Name

'貼り付け先を位置を次の行（下のセル）に移動させる
row_num = row_num + 1

Next ws2

'処理の最初でオフにした再計算等の設定をオンに戻しておく
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

'終わったことがわかるように、画面にメッセージを出す
MsgBox "終了しました"

End Sub

なお、VBAプログラムが付随したExcelファイルは、「Excelブック（*.xlsx）」で保存すると、VBAプログラムの部分が破棄されてしまいますので、必ず「Excelマクロ有効ブック（*.xlsm）」という形式で保存してください。

開いているExcelのシート一覧を作成しよう：動作確認・テスト

保存が完了したら、いよいよ実行してみましょう。
VBAのプログラムを実行する方法はいろいろありますが、

Alt＋F8

を押すと、実行することができるプログラム一覧が表示されますので、先ほど作った「シート一覧を作成する」を実行してみましょう。

うまくいけば、左端に「【シート一覧】」という黄色いタブのシートが追加され、そこにシート一覧が表示されるはずです。

このプログラムはどのような構成になっている？

今回、普段は書かないくらい、1行1行の処理にコメントで説明を付けました。
なので、説明はあえて次回にしようと思います。

コメントを丁寧に書くことで、処理の内容は結構伝わりますし、ネットで調べる際のキーワードになるということを実感していただければと思います。

今回のプログラムは、ネットで探したいろいろなプログラムをつぎはぎして作成しています。

なので、無事に動作をしたら、このプログラムをベースに

プログラムをいじる→動作確認→機能の理解

というサイクルを繰り返すことが上達につながりますので、いろいろ試してはどうでしょうか。

（特に各シートをぐるぐる回している中の処理を加工することから始めるのがおすすめです。）

このプログラムは何に使える？？

今回、このプログラムをお題にした理由ですが

• 各シートを順に処理していく
• シートの追加と削除
• セルの移動と値の書き込み
• 処理の最初と最後で、再計算等の有無を切り替えて、高速化

といった、今後VBAで開発するうえで流用しやすい要素が含まれているからです。

私がこのVBAを仕事で使うシチュエーションは、目次作成や作成済みシートをチェックするためのリストにも使いますが、一番多いのは次回解説予定の別プログラムと連携させるケースですので、詳細はその時にでも。

それではこの辺でお開きに。