3日がかりのその仕事、3分で終わらせる方法教えます!
パソコンスキルの心技体

For Each構文で複数シートに連続処理をするときのハマり例 – Excelマクロ・VBA

2012年2月29日
  • このエントリーをはてなブックマークに追加
  • follow us in feedly

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

2月19日の発展コースを受講されたとある方から、一昨日のお昼ごろ、こんなメール相談を受けました。

小川様
お世話になっております。

今実務でマクロを書いておりまして、うまくいかなかった点について質問させてください。

<やりたいこと>
特定のエクセルブックのすべてのシートについて、グレーの行については非表示としたい

<作成結果>
まず、1シートずつに対してのマクロは以下の通りできました。

Sub kakushi()
    Dim cnt
    Dim mn As String
    mn = Range(“B65536”).End(xlUp).Row
    For cnt = 3 To mn
        If Range(“B” & cnt).Interior.ColorIndex = 16 Or Range(“B” & cnt).Interior.ColorIndex = 48 Or Range(“B” & cnt).Interior.ColorIndex = 54 Then
            Rows(cnt).EntireRow.Hidden = True
        End If
    Next
End Sub

次に、ブック内の全シートに展開するマクロを作成しようとしたところ、こちらがうまくいきませんでした。。
例えば、For Each構文でできるのかな?と思い、以下のようなマクロを書いてみたのですが、うまくいきませんでした。

Sub kakushizenbu()
    Dim ichiran As Worksheet
    For Each ichiran In Worksheets
        Dim cnt
        Dim mn As String
        mn = Range(“B65536”).End(xlUp).Row
        For cnt = 3 To mn
            If Range(“B” & cnt).Interior.ColorIndex = 16 Or Range(“B” & cnt).Interior.ColorIndex = 48 Or Range(“B” & cnt).Interior.ColorIndex = 54 Then
                Rows(cnt).EntireRow.Hidden = True
            End If
        Next
    Next
End Sub

ブック内シート串刺しで、各シートに処理をかけるにはどのように書けばよいのでしょうか?
ご教示いただけると助かります。

  1. すべてのシートについて
  2. 最終行がどこかを調べ
  3. 1行目から最終行まで、すべての行について調る
  4. もしB列がグレーだったら
  5. 非表示とする

ということ。

彼は、この課題の、「すべてのシートについて」という部分でひっかかっていたらしい。

For Each構文で複数シートを処理する構文を作るとき、よくやる失敗。
僕も、今でも、マクロを書く過程で、こういうミスはしょっちゅうやる。ただ、違いは、見た瞬間「またやっちまった。まー、すぐなおすか ヾ(´ー`)ノ」で済むか、慣れが足りなくて、すぐには気づかないか。

以下、添削例。

まずは、他がゴチャゴチャしていて肝心の話題にフォーカスしにくいので、「すべてのシートについて」、確実にくり返し処理をできるようにするマクロを作ってみよう。
さらに参考になるよう、NG例も示す。

ということで、以下の3つのマクロを作成した。
シートが10枚くらいあるエクセルファイルを用意して、順番に実行してみて欲しい。

Sub hoge1()
    Dim ichiran As Worksheet
    Dim c As Long
    c = 1
    For Each ichiran In Worksheets
        Range(“A” & c).Value = c
        c = c + 1
    Next
End Sub
Sub hoge2()
    Dim ichiran As Worksheet
    Dim c As Long
    c = 1
    For Each ichiran In Worksheets
        ichiran.Select
        Range(“B” & c).Value = c
        c = c + 1
    Next
End Sub
Sub hoge3()
    Dim ichiran As Worksheet
    Dim c As Long
    c = 1
    For Each ichiran In Worksheets
        ichiran.Range(“C” & c).Value = c
        c = c + 1
    Next
End Sub

結果は…。以下のようになったはず。

  • hoge1 では、表示されているシートのA列に数値が入っていく
  • hoge2 では、ループの中でシートが選択され、選択されたシートのB列に数値が入っていく
  • hoge3 では、見た目にはほとんど何も起こらない。
    (が、マクロを実行し終えてから各シートを見に行くと、各シート一ヶ所だけ、C列に数値が入っているのを確認できる)

という感じ。

いただいたマクロでは、hoge1のようになっていた。対処法としては、hoge2かhoge3のようにすればよい、というわけ。

というわけで。

前提はともかく、以下、いただいたマクロを添削。
まず、If 文がゴチャゴチャと長いのが問題。このおかげで、可読性が低くなっている。

なので、Select Caseで書き直してみた。
ついでに、灰色でない場合は表示するよう、明示的に書きなおしてみた。

Sub kakushi_ogawa1() ‘select caseのほうがシンプルにまとまる
    Dim cnt
    Dim mn As String
    mn = Range(“B65536”).End(xlUp).Row
    
    For cnt = 3 To mn
        Select Case Range(“B” & cnt).Interior.ColorIndex
            Case 16, 48, 54
                Rows(cnt).EntireRow.Hidden = True
            Case Else
                Rows(cnt).EntireRow.Hidden = False
        End Select
    Next

End Sub

これで、準備完了。
以下、hoge2, hoge3 のやり方になるよう、書き直してみよう。

Sub kakushi_ogawa2_1() ‘「すべてのシートで」としてみる。その1
    Dim cnt
    Dim mn As String
    
    Dim w As Worksheet
    For
Each w In Worksheets
        w.Select ‘←いちばん簡単な回避策は、このように、すべてのシートで、都度「select」してから作業すること。
        mn = Range(“B65536”).End(xlUp).Row
        
        For cnt = 3 To mn
            Select Case Range(“B” & cnt).Interior.ColorIndex
                Case 16, 48, 54
                    Rows(cnt).EntireRow.Hidden = True
                Case Else
                    Rows(cnt).EntireRow.Hidden = False
            End Select
        Next
    Next
End Sub
Sub kakushi_ogawa2_2() ‘「すべてのシートで」としてみる。その2。すべてのセルをセルを指定している箇所で、「どのシートの」ということが分かる言葉を付け加える。
    Dim cnt
    Dim mn As String
    
    Dim w As Worksheet
    For Each w In Worksheets
        mn = w.Range(“B65536”).End(xlUp).Row
        
        For cnt = 3 To mn
            Select Case w.Range(“B” & cnt).Interior.ColorIndex
                Case 16, 48, 54
                    w.Rows(cnt).EntireRow.Hidden = True
                Case Else
                    w.Rows(cnt).EntireRow.Hidden = False
            End Select
        Next
    Next
End Sub

ということで、ちょうど僕がPCの前で仕事をしていたタイミングだったということもあり、一時間もしないうちに、無事に返信できた。

もともと勘のいい人だということは分かっていたので、上記の3つのサンプルを書いたテキストファイルを作り、「これを参考にしてください!」と書き添えて送っただけ。
(ソース中でコメントは入れましたが)

夕方になって、返信があった。

早速ありがとうございます!

いただいた内容で理解できました。
ありがとうございました。助かりました。

とのことでした。

確実にスキルがある、と思しき人への対応は、そっけなくていい加減な手抜きで済む簡潔でも、的を抑えればいいので楽ですね ヾ(´ー`)ノ

今回のポイントは…。

  • If文の条件節が冗長なときは、 Select Case を使って簡潔に書けないか検討してみる
  • For Each構文でワークシートに対して連続処理をするときには、ループの中で、処理対象のシートを明示的に指定することを意識

という感じでしょうか。

人のお役に立てると、気分がよいですね。ではでは☆

キーワード

コメント

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

最新の記事

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド