オブジェクトに対するくり返し処理は、For Each構文で

エクセルマクロ・VBA達人養成塾 小川です。

キューバ旅行記、その26です。

ハバナでの、2日目の早朝。

というわけで、そろそろ朝の散歩は終わり。
宿に戻る前に撮った写真。9月中旬のハバナは、天気が良い日の朝日がまぶしい。

宿近くで見たポスター。フィデルカストロの写真付。

宿の居室にて。
僕がお世話になった Casa Particular （民宿）は、キッチン、リビング、寝室があって、一晩 25 Peso。

お湯の出は悪かったが、それはキューバ全体で言えること。元バックパッカーの僕としては、特に不満らしい不満もなかった。

塾長のキューバ旅行記、最初から読みたい方はこちらから

オブジェクトに対するくり返しは、For Each構文で

今日は、ちょうど昨日、以前の受講生からいただいた質問から。

  Sub hoge()
    Dim i As Long
    Dim j As Long
    Dim cnt As Long
    
    i = Worksheets(“Sheet1″).Index ‘[1]
    j = Worksheets(“Sheet5″).Index ‘[2]
    For cnt = i To j – 1
        Worksheets(cnt).Delete ‘[3]
    Next
End Sub

（補足） ↑について。

例えば、5枚のシートがあって、左から順に、「Sheet1」、「Sheet2」、「Sheet3」、「Sheet4」、「Sheet5」という名前だったとしよう。

すると、[1]で、i = 1, [2] で j = 4 が入る。

さてさて、ということで。

解説。

まずは、Worksheet(“Sheet1”) という言葉の意味について。

そもそも、「Worksheets」が何を指すのかというと、「今アクティブなファイルにある、すべてのワークシートの集合体（コレクション）」だ。

「コレクション」とは、「同じタイプのオブジェクトの集合体」のこと。

リアルの世界なら、

○切手のコレクション （「切手」の集合体ですね）
○みかん一山 （「みかん」の集合体ですね）
○ももやまようちえんさくら組のみんな （「幼稚園生」の集合体ですね）

とか、そういう感じ。

で、「Worksheets」とは、「ワークシートの集合体」だ。

そして、

Worksheet(“Sheet1”)

と書くと、「今アクティブなファイルにある、すべてのワークシートの集合体」の中の、「Sheet1」という名前のシート。

ややこしいが、実は、一枚のエクセルシートを扱うとき、こういう手続きがされている。

このとき。

今の例では、名前で指定したが、インデックス番号で指定することもできる。
インデックス番号の最小値は1で、ワークシートの枚数が最大値。

通常、左にあるものから、順番に、1番、2番、…と番号が割り当てられている。

例えば、

Worksheets(1)

と書くと、「今アクティブなファイルにある、すべてのワークシートの集合体」の中の、「出席番号1番」のシート。
すなわち、今回の例で言えば、「Sheet1」という名前のシートを指す。

ということで、受講生が送ってきた上記のマクロでは、

[1]、[2]でインデックス番号の「最小値」と「最大値 – 1」の値を調べ。
[3]のところで、インデックス番号i番からj番までのすべてについて、シートを削除する

ということをしようとしているようだ。

だが、このマクロは、動作に失敗する。

何故かというと…。

前述のとおり、ワークシートに割り当てられるインデックス番号は、
通常、左にあるものから、順番に、1番、2番、…と番号が割り当てられている。

そして、この番号は、シートの枚数や配置が変更になる都度、再度割り当てが行われるから。

具体的に、みて見よう。

For Nextループがはじまる前。

各シートのシート名と、インデックス番号は以下のとおり。（カッコ内は、インデックス番号）

「Sheet1 (1)」 「Sheet2 (2)」 「Sheet3 (3)」 「Sheet4 (4)」 「Sheet5 (5)」

cnt の値は1。そこで、インデックス番号1番のシートが削除される。

すると、以下の状態になる。

「Sheet2 (1)」 「Sheet3 (2)」 「Sheet4 (3)」 「Sheet5 (4)」

次、cntの値は2。すると、インデックス番号2番のシートが削除される。

そして、以下の状態になる。

「Sheet2 (1)」 「Sheet4 (2)」 「Sheet5 (3)」

次、cntの値は3。すると、インデックス番号3番のシートが削除される。

そして、以下の状態になる。

「Sheet2 (1)」 「Sheet4 (2)」

次、cntの値は4。しかし、インデックス番号4番のシートなんて存在しないから、ここでエラーが出て止まる。

まあ、何というか…。このくらいのマクロの問題は、自力で解決したいものだ。

解決策だが。

例えば、一枚だけ残して削除したいなら、前から順番に削除していくのではなく、後ろから順に削除する。

Sub kaiketsu1()
    Dim i As Long
    Dim j As Long
    Dim cnt As Long
    
    i = Worksheets(“Sheet1″).Index ‘[1]
    j = Worksheets(“Sheet5″).Index ‘[2]
    For cnt = j To i + 1 Step -1
        Worksheets(cnt).Delete ‘[3]
    Next
End Sub

というか、本来は、For Each構文を使いたい。

以下では、「Sheet1」以外は皆削除している。

Sub kaiketsu2()
    Dim w As Worksheet
    For Each w In Worksheets
        If w.Name <> “Sheet1″ Then
            w.Delete
        End If
    Next
End Sub

For Each構文のほうが格段にシンプル。
For Next構文よりとっつきにくいが、エレガントに決めたいなら、このくらいは1分以内に書けるようになりたいものだ。

実は、一昨日、金曜日の晩から、大阪に来ています。

昨日は、某所にて、怪しいセミナーに参加していました。

今日は、僕のセラピー系スキルの師匠、棚田克彦先生のオープンカウンセリングがあるのですが、それのお手伝いが済んだら東京に帰ります。

エクセルマクロ・VBA達人養成塾、12月末～3月までの日程を追加しました。

年末特訓も用意しています。