講師コラム

VBAコラム-ワークシートの一覧取得

  • このエントリーをはてなブックマークに追加

表計算ソフトのExcelは非常に便利なソフトですね。
数字の計算からグラフ・抽出まで簡単な操作で出来ます。
こんな便利なソフトですが、複雑な計算をさせたり、多くの条件で分けて計算をする際には、さすがに標準の数式では限界があります。
その限界を突破することができるのが、VBA(Visual Basic for Application)です。
平たく言ってしまうとExcelで行うプログラミングです。
そう言ってしまうと構えてしまう方も多いかもしれませんが、実際にやってみるとコツをつかめば心強い機能になります。

このコラムでは、よく使うコードを紹介していきたいと思います。

紹介するコードは下記環境で動作確認をしておりますので、あらかじめご了承ください。
OS:Windows7 および Windows10
Officeバージョン:MicrosoftOffice2013 および 2016

今回紹介するのは前回に続き「ワークシート名の一覧の取得」です。
まずはコード全体を見てみましょう。

コード
Sub Get_SheetsName()

 ‘①変数宣言
 Dim Mysheet As Worksheet
 Dim NameList, NameStr As String

 ‘②変数の初期化
 NameStr = “”

 ‘③ワークシート数分だけ繰り返す
 For Each Mysheet In Worksheets
 ‘④初回判定とワークシート名の取得
  If NameStr = “” Then
   NameStr = Mysheet.Name
  Else
   NameStr = NameStr & “,” & Mysheet.Name
  End If
 Next

 ‘⑤結果情報の整理
 NameList = Split(NameStr, “,”)

End Sub

今回は今までより少しさっぱり目となりました。
早速順番に見ていきましょう。

①は変数の宣言です。今回はワークシートのオブジェクト変数と結果保存用の変数が2つです。宣言方法はいつもと変わりません。

②は変数に初期値として空白を代入しています。
この空白データを判定用として、④で利用します。

③では実行しているブックにあるワークシートの数だけ繰り返し、中のコードを実行します。今回はFor Eachの構文を利用しています。構文の構成を詳しく見ていきます。

構文
 For Each element In group
  [statements]
 Next

For構文と似ていますが、少しだけ利用方法が変わります。

項目 説明
element groupから情報を受け取るためのオブジェクト変数を指定します。
例)下記のように宣言された変数
Dim オブジェクト変数名 As Object
Dim ワークシートオブジェクト変数 As Worksheet
Dim レンジオブジェクト変数 As Range
group コレクション等のオブジェクトや配列を指定します。
statements 繰り返し実行されるコードを記述します。

これをふまえて今回のコードを見てみましょう。

 For Each Mysheet In Worksheets
  ‘④初回判定とワークシート名の取得
  [実行コード]
 Next

statementsは実行コードが記述される部分ですので、④で説明します。
element」に「Mysheet」の①で宣言したオブジェクト変数を指定しています。
group」に「Worksheets」のワークシートオブジェクトを指定しています。

具体的な動作を見てみましょう。

 A.「Worksheets」のデータからワークシートがあるか
  B.ある場合
   B-1ワークシートの情報を「Mysheet」へ代入
    ↓
   B-2 [実行コード]を実行
    ↓   
   B-3 Aに戻り、次のワークシートがあるか確認する

  C.ない場合
   C-1 Forを終了する

今回の場合には、「group」に「Worksheets」を指定しましたので、ブックに設定されているワークシートの数だけ[実行コード]が実行されることになります。

では実際に実行される④の実行コードを見てみます。

 ‘④初回判定とワークシート名の取得
 If NameStr = “” Then
  NameStr = Mysheet.Name
 Else
  NameStr = NameStr & “,” & Mysheet.Name
 End If

ここで②で「NameStr」に代入した空白を利用して、
1回目の繰り返しか (If NameStr = “” Then)
2回目以降の繰り返しか (Else ・・・それ以外)
を判定し、動作を振り分けました。

それぞれの動作を先に確認します。
1回目:NameStr = Mysheet.Name
2回目:NameStr = NameStr & “,” & Mysheet.Name

補足:オブジェクト.Name
オブジェクト変数の場合には、紐づけられるプロパティ情報を得ることができます。
(厳密に言えば少し語弊がありますが・・・話が長くなるので割愛)
対象となるオブジェクトによって利用できるかどうかが変わりますが、利用できる一般的なプロパティは下記のとおりです。

プロパティ 内容
BackColor 背景色
Bold 太文字
Cells セル番地
Count オブジェクト数を数える
Color 文字色
Italic 斜体にする
Name 名前を指定する
Value 文字列を入力または取得する

今回はワークシート名を取得しますので、「Name」を利用します。
1回目は「NameStr」にワークシート名を代入します。
2回目以降は「NameStr」にすでに代入されているデータに「,(カンマ)」を挟んでワークシート名を追加します。

1回以上実行されれば、「NameStr」にはワークシートの名前が入っているはずですので、空白なら1回目、空白でなければ2回目以降という判定ができます。

④のコードはワークシートの数だけ実行されることになり、仮に3つのワークシートがある場合には、「NameStr」には下記のようなデータが入っていることになります。

1つ目のワークシート名, 2つ目のワークシート名, 3つ目のワークシート名

「,(カンマ)」で区切られたワークシート名の羅列ですね。

1つにつながったままでは使いづらい状態ですので、このデータを活用するために、それでは⑤のコードで利用しやすいデータに加工します。

 ‘⑤結果情報の整理
 NameList = Split(NameStr, “,”)

ここで「Split」を利用します。
「Split」は指定した文字をキーとして、元の文字を分割し、配列としてデータを返します。

構文
 Split (Expression [, Delimiter] [, Limit] [, Compare])

分かりにくいと思いますので、⑤のコードを例に考えてみます。

「NameStr」に「1つ目のワークシート名, 2つ目のワークシート名, 3つ目のワークシート名」が代入されている場合、Splitを実行すると受け側の「NameList」に下記のような配列が返されます。

NameList(0) ・・・ 1つ目のワークシート名
NameList(1) ・・・ 2つ目のワークシート名
NameList(2) ・・・ 3つ目のワークシート名

指定した「,(カンマ)」は削除され、「,(カンマ)」毎に分割されます。

なんとなく理解していただいたところで、先ほどのSplit構文について補足します。
それぞれの項目の説明は以下の通りです。

引数名 省略 説明
Expression × 任意の文字列式を指定します。 空白の文字列 (“”)の場合、空の配列を返します。
Delimiter 文字列の区切りとする文字を指定します。 省略した場合、スペース (” “) が適用されます。
Limit 結果として返す配列の要素数(分割数)が設定できます。
(規定値は-1)
Compare 文字列の比較のモードを設定できます。

通常利用する時には、分割元の文字列となる「Expression」と区切り文字とする「Delimiter」のみ設定して利用する場合が多いかと思います。

サンプルコードでは触れませんでしたが、ワークシート名を利用する場合には、「NameList」を配列(1次元)として利用すると、情報を取得できます。

今回はSplitを利用したワークシート名の取得を紹介しましたが、あらかじめ配列を用意し、④の段階で配列に代入していく事もできます。(こちらの方が実用的かな?)

次回以降もサンプルコードを交えて紹介をしていきます。

  • このエントリーをはてなブックマークに追加

話題をチェック!

2017年9月8日

Excel VBA小技コラム – セルの連続移動

表計算ソフトのExcelは非常に便利なソフトですね。数字の計算からグラフ・抽出まで簡単な操作で出来ます。こんな便利なソフトですが、複雑な計算をさせたり、多くの条件で分けて計算をする際には、さすがに標準の数式では限界があり …続きを読む