エクセルVBAで複数シートにマクロ実行

質問

エクセル2000です。

Sub 行列非表示()
For i = 2 To 120
If Cells(i, "A").Interior.ColorIndex = 3 Then
Cells(i, "A").EntireRow.Hidden = True
End If
Next i
For n = 1 To 50
If Cells(1, n).Interior.ColorIndex = 3 Then
Cells(1, n).EntireColumn.Hidden = True
End If
Next n
End Sub

上記マクロを、シートAAAとCCCとEEEに実行する場合、

Sub test()
Sheets("AAA").Activate
Call 行列非表示
Sheets("CCC").Activate
Call 行列非表示
Sheets("EEE").Activate
Call 行列非表示
End Sub

と書くよりももっとすっきり実行する方法は無いでしょうか？
各シートの非表示対象の行や列はそれぞれことなります。

また　Sub 行列非表示 自体も、もっと効率的にやる方法はないでしょうか？

回答No.5

KenKen_SP · ベストアンサー

こんにちは。KenKen_SP です。

> Call 行列非表示("AAA")なら、シートAAAにだけ働かせるという理解
> でいいのでしょうか？

Call 行列非表示("AAA")

でプロシージャ「行列非表示」にシート名「AAA」を文字列で渡して
います。

ここで、呼び出されるプロシージャ「行列非表示」の宣言部分をみると、

Private Sub 行列非表示(strSheetName As String)

とあり、文字列型変数「strSheetName」でシート名を受け取っていま
す。この変数はそのプロシージャ内で再利用することができます。

#4のコードでは　　With Sheets(strSheetName)　～という形でパラ
メータとして受け取った文字列のシート名を再利用しています。

プロシージャ「行列非表示」のなかでシート名を指定している以上
事前にシートの Activate する必要はありません。

考え方としては、「変数の内容を別のプロシージャに引き渡す」と
思っていただいて結構です。変数の内容を引き渡すことで、サブルー
チンがより機能的に連携していくのです。

細かな処理単位でサブルーチン化されたマクロ群を連携させ、大きな
一つの処理を行うコーディングはコードの可読性、デバッグや修正・
カスタマイズの容易さなどの点で優れます。高度な処理を行うマクロ
は通常このようにコーディングされているはずです。

例えば、今回はセル背景色で列や行を非表示にする処理ですが、その
背景色をパラメータとして渡せるようにコーディングすると、変更が
あった場合、メインのプロシージャを少し修正するだけで済みます。

例）シート毎に異なるセル背景色で列や行を非表示にする

Sub test()
　　Call 行列非表示("AAA",3)
　　Call 行列非表示("BBB",5)
　　Call 行列非表示("CCC",7)
End Sub

Private Sub 行列非表示(strSheetName As String,ColorIdx as Long)
　　With Sheets(strSheetName)
　　　　For i = 2 To 120
　　　　　　If .Cells(i, "A").Interior.ColorIndex = ColorIdx Then
　　　　　　　　.Rows(i).Hidden = True
　　　　　　End If
　　　　Next i
　　　　For n = 1 To 50
　　　　　　If .Cells(1, n).Interior.ColorIndex = ColorIdx Then
　　　　　　　　.Columns(n).Hidden = True
　　　　　　End If
　　　　Next n
　　End With
End Sub

もちろん、無闇にサブルーチン化する必要はありません。数行程度の
処理であれば、ひとつのプロシージャにまとめた方がスッキリとした
コードになるからです。

しかし、「処理内容が大規模（高度）になってきたらサブルーチン化」
という考え方は覚えて損はありません。

長文すみませんでした。ご参考までに。

お礼

なんどもありがとうございます。

まだ、教えていただいたことの半分も理解できてないと思いますが、新しい「技」を覚えたような気がします。
ありがとうございました。

回答No.4

KenKen_SP

こんにちは。KenKen_SP です。

サブルーチン化、、つまり処理内容単位でプロシージャを書く手法
はコードの可読性、デバッグや修正・カスタマイズの容易さなどの
点で優れます。処理内容によっては細かくサブルーチン化しない方
がベターな場合もありますが、方向性としては良いと思います。

ひとつひとつ独立した処理内容のサブルーチンを連携させる場合、
パラメータ（引数）をもたせたプロシージャの書き方を考えてみて
下さい。

本ご質問の本質はこの点にあるように感じました。

> もっとすっきり実行する方法は無いでしょうか？

多少すっきりした、、かな？

Sub test()
　　Call 行列非表示("AAA")
　　Call 行列非表示("BBB")
　　Call 行列非表示("CCC")
End Sub

Private Sub 行列非表示(strSheetName As String)
　　With Sheets(strSheetName)
　　　　For i = 2 To 120
　　　　　　If .Cells(i, "A").Interior.ColorIndex = 3 Then
　　　　　　　　.Rows(i).Hidden = True
　　　　　　End If
　　　　Next i
　　　　For n = 1 To 50
　　　　　　If .Cells(1, n).Interior.ColorIndex = 3 Then
　　　　　　　　.Columns(n).Hidden = True
　　　　　　End If
　　　　Next n
　　End With
End Sub

メインの test プロシージャでシートを指定する方法は上記のよ
うに書いてもいいし、For ループで回すこともできます。

Sub test()
　　Dim sh As Worksheet
　　Application.ScreenUpdating = False
　　For Each sh In Worksheets(Array("AAA", "BBB", "CCC"))
　　　　Call 行列非表示(sh.Name)
　　Next sh
End Sub

> 1列目のセルが赤いものをいっぺんに非表示にするやり方はないでしょうか？

「いっぺんに」が見かけ上であれば、画面の更新を停止すれば良い
だけですが、最低一回はセルの背景色をループ処理で調べなくては
なりませんので、どんな書き方をしてもそんなに変わらないでしょう。

お礼

いつもありがとうございます。

パラメータ？付きのプロシージャって、いままでやったことがありませんでした。
Call 行列非表示("AAA")なら、シートAAAにだけ働かせるという理解でいいのでしょうか？

回答No.3

Wendy02

こんばんは。
＞もっとすっきり実行する方法は無いでしょうか？

だいたい、シートごとの処理に関して、Activate せずに、コンテナ単位で行うのが一番、すっきりとした方法だと思います。結果的には、ループで単独で完結したほうがよいのではないかと思います。

Activate して、サブルーチンにするという方法も、悪くはありませんが、画面を切り替えないで行うほうが速いです。

Sub test1()
　Dim sh As Worksheet
　Dim i As Integer, n As Integer
　Application.ScreenUpdating = False
　For Each sh In Worksheets(Array("AAA", "BBB", "CCC"))
　　With sh
　　　For i = 2 To 120
　　　　If .Cells(i, 1).Interior.ColorIndex = 3 Then
　　　　　.Cells(i, 1).EntireRow.Hidden = True
　　　　End If
　　　Next i
　　　For n = 1 To 50
　　　　If .Cells(1, n).Interior.ColorIndex = 3 Then
　　　　　.Cells(1, n).EntireColumn.Hidden = True
　　　　End If
　　　Next
　　End With
　Next sh
　Application.ScreenUpdating = True
End Sub

お礼

こんばんは。
お世話様です。
いつもすばらしい助言をありがとうございます。

画面を切り替えないで行うほうがいいですね。
で、行を非表示にする場合、2行目から120行目をFor Nextで一行ずつ見ていくのではなく、2行目から120行目のうち、1列目のセルが赤いものをいっぺんに非表示にするやり方はないでしょうか？

回答No.2

imogasi

あまりすっきりしませんが
Sub test01()
Dim sh As Worksheet
For Each sh In Worksheets(Array("sheet1", "sheet2"))
sh.Activate
For i = 1 To 10
For n = 1 To 50
If Cells(i, n).Interior.ColorIndex = 3 Then
Cells(i, n).EntireColumn.Hidden = True
End If
Next n
Next i
Next
End Sub
でＳｈｅｅｔ１，ｓｈｅｅｔ２の場合動きました。10シートぐらいならＡｒｒａｙ内に書く気が起こるでしょう。シートのＩＮＤＥＸなら表現は簡単ですが、左に対照シートを寄せておく必要があります。
sh.ActivateはＨｉｄｄｅｎがＡｃｔｉｖｅシートに対して行われるもので
必須のようです。

お礼

> sh.ActivateはＨｉｄｄｅｎがＡｃｔｉｖｅシートに対して行われるもので
> 必須のようです。

なるほどよく分かりました。
For Each sh In Worksheets(Array("sheet1", "sheet2"))も勉強になりました。ありがとうございます。

回答No.1

haukappu

シート３枚をまとめて選択して、行列非表示にしてやればよいかと。

シート３枚の選択は、コントロールを押しながらシート選択すれば実行したいシートを選べます。マクロの記録を行って、確認してみてください。

補足

最初に下記のようにやってみましたが、一枚目しか非表示になりませんでした。だからここで質問したのです。

Sub 行列非表示()

Sheets(Array("AAA", "CCC", "EEE")).Select

For i = 2 To 120
If .Cells(i, "A").Interior.ColorIndex = 3 Then
Cells(i, "A").EntireRow.Hidden = True
End If
Next i

For n = 1 To 50
If Cells(1, n).Interior.ColorIndex = 3 Then
Cells(1, n).EntireColumn.Hidden = True
End If
Next n

End Sub