« 2016年9月 | トップページ | 2017年1月 »

Excel でワークシート名を一覧にする VBA のコード

この記事は自分用の覚書です。

K.N さん
エクセルで、ブックのなかのワークシート名を一覧にするマクロのコードの件です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

ネットで見つけたものが役に立ってよかったですね。
まずはよしとして、「よくわからないけど、やりたいことはできた」ままでは応用ができません。

少しでも役立てられるようにコメントをつけてみました。わかりづらいところがあったら、今度会った時に聞いてください。
もっとも、私にわかる範囲でしか話せませんが。悲しいかな私も「VBAなんて、おちゃのこさいさい」というわけではないので。


Sub ワークシート名を一覧にする()
    Dim i As Long
    Dim Sheet_Name As Variant

    Worksheets("ワークシート名の一覧").Select
    For i = 1 To Worksheets.Count - 1
        Sheet_Name = Worksheets(i).Name
        Cells(i, "A").Offset(1, 0).Value = Sheet_Name
    Next i
'
'あらかじめシート「ワークシート名の一覧」を一番右端に作っておく。
'
'変数 Sheet_Name は Variant (バリアント)型で宣言。
'バリアント型にしておけば、ワークシート名が文字でも数字だけでもどんな値であっても格納できる。
'なお、変数の型をバリアント型にしたい場合は、型の指定を省略できる。
’    Dim Sheet_Name
'と書くと、宣言した変数をバリアント型で指定したことになる。
'
'左端のワークシートを 1 で表す。
'変数 i に 1 が代入されると、
'Sheet_Name = Worksheets(i).Name
'は、
'Sheet_Name = Worksheets(1).Name
'となって、これは一番左端のワークシートの名前を、変数 Sheet_Name に代入するということ。
'右端のワークシートまでの枚数を Worksheets.Count で数えている。
'-1 しているのは、ワークシート名の一覧を載せるシートの一つ手前までを指定するため。
'
'変数 Sheet_Name をA列のセルに代入していく。
'OffsetプロパティでA列のセルを一行ずつ下にずらしていく指定をしている。
'Offsetプロパティは、Offset(行, 列)、Cellsプロパティも Cells(行,列)という書式なので、
'
'Cells(i, "A").Offset(1, 0).Value
'
'のコードで、変数 i が 1 のときは、
'
'Cells(1, "A").Offset(1, 0).Value = Sheet_Name
'
'ということになって、A1セルの一行下のA2セルを、変数 Sheet_Name の代入先に指定したことになる。
'変数 Sheet_Name の代入先が、A2セルからなのは1行目に列見出しを入れるため。
'
End Sub


難しいのは、 Cells(i, "A").Offset(1, 0).Value のところでしょうか。
VBA を学びだした際に Offsetプロパティの動きをイメージするのは難しいかと思います。私もまだイメージしづらいです。

基準となるセルやセル範囲を、指定した行数や列数あるいは両方ともずらす・移動する――というものなんですが。

Offset(行,列) ということになっています。 Offset(1, 0) なら「(列はそのままで)1行下にずらす」ということです。
なので、変数 i が 1 の場合は、

Cells(1, "A").Offset(1, 0).Value

となって、 Cellsプロパティも Cells(行,列)なので、これは――A1のセルから1行下のセル―― A2 のセルの値を指していることになります。
なお、 Cells(i, "A") は、 Cells(i, 1) と書いてもどちらも同じ意味です。

Offsetプロパティって文字で書くとよけいにイメージしづらいですよね。

なので、ちょっと考え方を変えてみます。

Cells(i, "A") の変数 i が 1 のときは 、OffsetプロパティによってA2セルを、 i が2 のときはA3セルを、3 のときはA4セルを意味します。

Cells(1, "A").Offset(1, 0).Value なら、セルA2の値。
Cells(2, "A").Offset(1, 0).Value なら、セルA3の値。
Cells(3, "A").Offset(1, 0).Value なら、セルA4の値。

これって変数 i が「一つ増え」て1行下を示しているのと同じことですよね。
ならば、この場合は変数 i に 1 を足してやれば、Offsetプロパティを使わなくてもよくなるといえます。

Cells(i + 1, "A").Value = Sheet_Name

というように。

こちらのほうが「1行ずらしていく」イメージがしやすくないですか?
もちろん、「Offsetプロパティのほうがイメージがしやすい」ということでしたら、ぜんぜんそれで構わないと思います。

ようは、わかりやすいほうがいいよねという話ですから。
先のコードを変えてみました。


Sub ワークシート名を一覧にする()
    Dim NewWorkSheet As Worksheet
    Dim i As Long
    Dim Sheet_Name As Variant
   
    Set NewWorkSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    NewWorkSheet.Name = "ワークシート名の一覧"
        For i = 1 To Worksheets.Count - 1
            Sheet_Name = Worksheets(i).Name
            Cells(i + 1, "A").Value = Sheet_Name
        Next i
'
'変数 NewWorkSheet を ワークシート型で宣言。
'Addメソッドは、ワークシートを正常に挿入したあとWorksheetオブジェクトを返す。
'挿入したシートの名前を文字列で返すのではなく、挿入したシート自体を返す。
'したがって、返り値を受け取る変数はワークシート型かバリアント型を指定しておかなければならない。
'
'Set NewWorkSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
'のコードで、今あるワークシートの一番右(後ろ)に新しくワークシートを追加し、
'変数 NewWorkSheet でそれを受け取る。
'
'Addメソッドのオプションを () で囲っているのは、VBAのルール。
'このコードのように返り値を変数に代入するなど、返り値を利用する場合は、括弧を付けなければならない。
'たとえば、
'
'Sub Sample01()
'    Worksheets.Add after:=Worksheets(Worksheets.Count)
'    ActiveSheet.Name = "新しいワークシート"
'End Sub
'
'のコードでも、現在あるワークシートの最後尾にワークシートを追加できる。
'この場合は、Add メソッドの返り値を利用していないので、Add メソッドの後ろに あるオプションを () で囲むことは不要。
'
'また、
'Worksheets.Add(after:=Worksheets(Worksheets.Count))
'の部分を
'Worksheets.Add(Before:=Worksheets(1))
'のように書くと、
'現在あるワークシートの一番左(先頭)に新しいワークシートを追加できる。
'
'変数 Sheet_Name は Variant (バリアント)型で宣言。
'バリアント型にしておけば、ワークシート名が文字でも数字だけでもどんな値であっても格納できる。
'なお、変数の型をバリアント型にしたい場合は、型の指定を省略できる。
’    Dim Sheet_Name
'と書くと、宣言した変数をバリアント型で指定したことになる。
'
'左端のワークシートを 1 で表す。
'変数 i に 1 が代入されると、
'Sheet_Name = Worksheets(i).Name
'は、
'Sheet_Name = Worksheets(1).Name
'となって、これは一番左端のワークシートの名前を、変数 Sheet_Name に代入するということ。
'右端のワークシートまでの枚数を Worksheets.Count で数えている。
'-1 しているのは、ワークシート名の一覧を載せるシートの一つ手前までを指定するため。
'
'変数 Sheet_Name をA列のセルに入れていく。
'変数 i に 1 を足すことでA列のセルを一行ずつ下にずらしていく指定をしている。
'Cellsプロパティは Cells(行,列)という書式なので、
'
'Cells(i + 1, "A").Value
'
'のコードで、変数 i が 1 のときは、
'
'Cells(2, "A").Value = Sheet_Name
'
'ということになって、A2セルを変数 Sheet_Name の代入先に指定したことになる。
'変数 Sheet_Name の代入先が、A2セルからなのは1行目に列見出しを入れるため。
'
End Sub


せっかくなので、新しいワークシートを追加するようにしてみました。
これで、ワークシートを最初に右端に作る必要がありません。

また、今回の場合は

        Sheet_Name = Worksheets(i).Name
        Cells(i + 1, "A").Value = Sheet_Name

のところは、

        Cells(i + 1, "A").Value = Worksheets(i).Name

のように変数を使わないで1行で書いてもいいかなとも思います。
Worksheets(i).Name からなんとなく「ワークシート名を取得しているんだな」と VBA に不慣れな人でも想像がついて、何をしているかを理解しやすいと思うので。

変数を使ったのは私のクセです。
変数を使ったほうが何かと便利な場合のほうが多いので、日ごろから変数を使うクセをつけているというか、心がけているというかです。

でも、このコードの場合、1行あたりも短いし、全体的な行数も多くないので全体として何をやっているかを理解しやすいと思いますし、どこかで間違えてデバッグするのもそう大変ではないと思います。

そういう理由で今回は変数を使わないで1行に書くのもありかもと思ったしだい。
どちらがいい・悪いという話ではないです。自分や周りの人たちが見やすく、理解しやすいことが大事です。

それから変数を使う場合は、

    Dim i As Long
    Dim Sheet_Name As Variant

のように Dim を使って必ず変数を宣言してください。
「変数は宣言しなくてもいい」「宣言しなくてもちゃんとコードが実行できた」といった話を見たり聞いたりするかもしれませんが、「宣言しなくていい」を信じないでください。

変数を宣言をしたからといって害になることは何もありませんが、宣言していないと害を被ることがあります。だったら、宣言しておくのが賢い選択です。

ちなみに、変数の宣言は、

    Dim i As Long, Sheet_Name As Variant

というように , で区切って1行で書くこともできます。

なぜ変数は宣言しなければならないか、宣言しないとどんな目に遭うのか――。それについては、下記のサイトでとてもわかりやすく説明してくれています。
日本におけるExcelの第一人者とされている「田中亨」という方のサイトです。

変数って宣言しなくちゃいけないの?

| | コメント (0) | トラックバック (0)

« 2016年9月 | トップページ | 2017年1月 »