マクロで特定文字を含むシートを選択|For Each ~ Next & Like

シートを選択のタイトル画像

お知らせ記事には広告が含まれておりますがExcelのスキルUPに繋がる様コンテンツ自体は手を抜かずに作成しております

For Each ~ Next & Like演算子で特定文字を含むシートを選択する

VBAにはいくつかの繰り返し処理が用意されています。皆様の仕事にあったコードを選んでいただく事が可能です。

バリエーションが豊富で色々な仕事に対応できる反面デメリットもあります。

実際はコードのバリエーションがあってもどんなケースにどの処理を使えば良いのか分からない方は多い様です。

EnjoyExcel
EnjoyExcel

ユーザーがひとりでマクロを自分の仕事に置き換えるのは難しいです。

加えて繰り返し処理となるとさらに考える事が増えて難しくなるとの事でした。

繰り返し処理の中でもFor Each ~ Nextステートメントが分かりにくいという方(質問)が非常に多いです。

本記事ではできるだけFor Each ~ Nextステートメントを気軽に使ってもらえる様に具体例を用意しました。

主に以下のようなケースで効果を発揮します。

  • ブック内の特定文字を含むシートを選択してセルに文字を記入する
  • ブック内の特定文字を含むシートがいくつあるのかを数える
  • ブック内の特定文字を含むシートのシート名を変更する

これらの事例に対応するには For Each ~ NextステートメントとLike演算子 を使いましょう。

(Like演算子はIfステートメントで用意する条件の一部として使用します)

3つの事例全てに具体例(サンプルコード)を用意して解説していきます。

関連記事

今回使用しているコードに関連した記事を用意しておきます。

IfステートメントVBA-11マクロ内でMsgBoxとIFステートメントを組み合わせる

Like演算子VBAで文字列を比較Like演算子とワイルドカードで完全/部分一致を確認【一覧で表示】

For~Nextステートメント等を使ってフォルダ内の複数ブックから全てのシート名を取得しています。

ループを組み合わせるVBAはフォルダ内の複数ブック×全部のシート名をセットで取得できる

For Each ~ Nextステートメントを動画で解説しています

こちらの記事ではFor Each ~ Nextステートメントを動画で解説しています。

動画で解説VBA|最後のシートまで処理を繰り返す|変数をうまく使いこなそう

シートを選択のアイキャッチ画像

For Each ~ NextステートメントとLike演算子について

ポイントとなる2つの要素について基本的な内容を説明しておきます。

For Each ~ Nextステートメント

私自身がFor Each ~ Nextステートメントをひとことで表現すると以下の様になります。

Q
For Each ~ Nextステートメントとは
A

ワークシートやセル等特定の集団内の要素に対しループで1つずつ処理する事ができる構文

続いてより公式な見解です。

Microsoftのリファレンスを確認したのですが理解するのが難しい為私が持っている本から引用します。

コレクションや配列などの集合に対して、その要素の数だけ反復処理を行いたい場合はFor Each ~ Nextステートメントを使う事ができます。

多くの場合、For~Nextステートメントであっても実現可能ですがFor Each ~ Nextステートメントは、対象となる集合の要素数が不明であっても、反復処理を実現できるという点が特徴となります。

パーフェクトExcel VBA 高橋宣成 著 104ページ

関連書籍

For Each ~ Nextステートメントについて丁寧に書かれている書籍です。この本なら1冊でVBAを習得できます。

実際に購入して使用してみると「買ってすぐ読み切るというより辞典の様に使うと効率が良い」と感じています。

分からない事が出てきた時に調べるために使うにはとても良い本です。

よって今回の記事でも引用させていただいた次第です。

¥3,608 (2024/07/01 16:22時点 | Amazon調べ)
\楽天ポイント4倍セール!/
楽天市場

電子書籍(Kindleストア)は書籍と比較してお得に購入できます。よって私は最近電子書籍での購入が多いです。

電子書籍をVBEと同じ画面で横に並べながら作業する事で作業効率が劇的にあがりますよ。

構文

構文についてもMicrosoftのリファレンスを参考にしましたが分かりやすい事例が無かったので自作しました。

Sub 構文()

Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets

  'ここにやりたい事を書く

Next sht

End Sub

まずは変数shtです。ワークシート(単数)を格納します。

続いてFor Each句です。この行の最後に 「In ActiveWorkbook.Worksheets」と記載されていますね。

これはブックが保持するワークシート群(複数)を指しています。この要素の集団をコレクションと呼びます。

コレクションについて間単に説明すると同じ要素の集まりです。Inの後にはコレクションを指定しましょう。

最後にループと変数shtの関係を解説します。

For~Next間をループする際ワークシートコレクションから1つずつワークシートを取出し変数shtに格納します。

構文のコードではブック内のシート全てを1回ずつ変数shtに格納できたらループを抜けるという建付けです。

メリット

引用文の中にはこのステートメントのメリットが書かれていました。

対象となる集合の要素数が不明であっても、反復処理を実現できる

対象のオブジェクト(セルやワークシート等)の数を事前に確認できなくても処理を進めたい時に使えます。

これは非常に大きなメリットです。

For Each ~ NextはFor ~ Nextではできない事ができる

For ~ Nextステートメントは「○○ to ××」という様に始点と終点を決めてからループに入ります。

代わってFor Each ~ Nextステートメントは以下のような使い方ができます。

  • フォルダ内にある個数不明のワークブック全部を周回する
  • ワークブック内にある枚数不明のワークシート全部を周回する

このように「不確定要素に対してループを組む」には非常に有効なステートメントです。

今回はブック内のシート全部を周回するという事例を使いFor Each ~ Nextステートメントを解説していきます。

Like演算子

Like演算子についてはこちらの記事をご覧ください。ワイルドカード含め具体例な事例を使って解説しています。

Like演算子がわかるVBAで文字列を比較Like演算子とワイルドカードで完全/部分一致を確認【一覧で表示】

特定文字列の検索ではFindメソッドを使わない

文字列の検索はFindメソッドよりLike演算子の方が明らかに「かんたん」かつ「高速」に処理ができます。

Findメソッドのデメリット、問題点をまとめた記事はこちらです。Findメソッドを使わなくて良い理由がわかります。

Findメソッドは不要VBAのFindメソッドは変数が難しい|文字列検索はLike演算子!

事例をもとに3つのケースを解説

3つのケースについてコードを用意しました。

  • Case1_ブック内の特定文字を含むシートを選択してセルに文字を記入する
  • Case_2ブック内の特定文字を含むシートがいくつあるのかを数える
  • Case_3ブック内の特定文字を含むシートのシート名を変更する

引数を伴うメソッドなど難しくなるような操作は省きました。できるだけ簡単に理解出来る様にしてあります。

ワークシート

3つのケースに共通するワークシートを先に紹介しておきます。

1つのワークブック内にワークシートを3枚作成しました。Sheet1にはボタンを3つ用意しています。

各ボタンには今回用意したコードがそれぞれ関連付けられています。

ボタンの作り方についてはこちらの記事をご覧ください。

ワークシートの仕様

Sheet2、3はワークシートが用意されているだけです。セルには何も用意されていません。

Case1_ブック内の特定文字を含むシートを選択してセルに文字を記入する

まずはコードを紹介します。ワークシートに用意された1番のボタンと関連付けられたコードです。

Sub 特定文字を含むシートを選択_1()

Dim wbk As Workbook
Dim sht As Worksheet

Set wbk = ActiveWorkbook

For Each sht In wbk.Worksheets
  
  With sht
    If .Name Like "*1" Then
      .Range("A1").Interior.ColorIndex = 4
      .Range("A1").Value = "みどり"
    End If
  End With
  
Next

End Sub

Case1_解説

やっている事はワークブック内のシートを全て周回しシート名に「1」を含むシートを探します。

見つけた際は(条件Trueの時)セルA1の背景色を緑に変えた後「みどり」と文字を記入します。

結論としては「Sheet1」のセルA1の背景が緑に変化しセルには「みどり」という文字が入力されます。

続いてコードのポイントを紹介します。大きく分けて3つです。

  • ワークブックをオブジェクト変数にセット
  • For Each ~ Nextステートメントでワークブックが保持しているワークシートをループ
  • Ifステートメント内でLike演算子を使って条件に合うのか(合致するのか)を確認
オブジェクト変数

こちらの記事で画像を使って解説しています。

オブジェクト変数についてVBAで転記|ブック間で文字列をコピペするマクロの作り方を教えます

For Each ~ Nextステートメント

オブジェクト変数wbkに格納されたブック内のワークシートをループするというステートメントです。

最初のFor Each句を通過すると変数shtにワークシートが1つ格納されます。

最初はSheet1が格納されます。少し話がそれますが選択されるワークシートの順番を変えることもできます。

ワークシートが表示された画面で見た時に左のワークシートから選択されます。

ワークシートを選択する順番

Nextに到達するまでは最初に掴んだワークシートを変数shtに保持したままコードを通過していきます。

Nextを通過してFor Each句に戻った時に次のワークシートを掴み変数shtに格納するという流れになります。

このループ構造であればワークブック内にどれだけワークシートが増えても全てのシートを周回する事ができます

Ifステートメント内でLike演算子を使う

Like演算子の使い方は冒頭でも紹介したこちらの記事にてご確認ください。

Case2_ブック内の特定文字を含むシートがいくつあるのかを数える

コードを紹介します。ワークシートに用意された2番のボタンと関連付けられたコードです。

Sub 特定文字を含むシートを選択_2()

Dim wbk As Workbook
Dim sht As Worksheet
Dim cnt As Long

Set wbk = ActiveWorkbook

For Each sht In wbk.Worksheets
  
  With sht
    If .Name Like "Sheet*" Then
      cnt = cnt + 1
    End If
  End With
  
Next

MsgBox "特定文字を含むシートは" & cnt & "枚です", vbInformation, "お知らせ"

End Sub

Case2_解説

やっている事はワークブック内のシートを全て周回しシート名に「Sheet」を含むシートを探します。

見つけた際は変数cntに1を足していきます。つまりシートの枚数を数えています。(チェックしています)

結論としてはメッセージボックスを使ってシート枚数を表示させています。今回のケースでは「3」となります。

続いてCase1のコードでは解説していない点をポイントに挙げてリンクを用意しておきます。

ここでは変数メッセージボックスになります。各リンクには解説記事を用意しています。

Case3_ブック内の特定文字を含むシートのシート名を変更する

コードを紹介します。ワークシートに用意された3番のボタンと関連付けられたコードです。

Sub 特定文字を含むシートを選択_3()

Dim wbk As Workbook
Dim sht As Worksheet

Set wbk = ActiveWorkbook

For Each sht In wbk.Worksheets
  
  With sht
    If .Name Like "*2" Then
      .Name = "2番目"
    ElseIf .Name Like "*3" Then
      .Name = "No.3"
    End If
  End With
  
Next

End Sub

Case3_解説

やっている事はワークブック内のシートを全て周回しシート名に「2」or「3」を含むシートを探します。

見つけた際は対象シートの名前を変えています。

結論としては「Sheet2」は「2番目」、「Sheet3」は「No.3」というシート名に変わります。

この様にFor Each ~ Nextステートメントでは変数に格納された要素のプロパティを変更する事も可能です。

Case3だけで使われている要素としてはElseIFが挙げられます。解説記事のリンクを用意しておきました。

参考:今回のコードでサブルーチンを勉強できます

プロシージャの分割(サブルーチン)に関する記事内で本記事のコードを使いました。

流れでサブルーチンを勉強したい方は関連記事をご覧ください。

まとめ

特定文字を含むシートを選択する方法をFor Each ~ NextステートメントとLike演算子を使って解説しました。

ステートメント内でどのオブジェクトが変数に格納されているのかが分かると理解が進みます。

そのためにはローカルウインドウをしっかり見る事ができる様にトレーニングしましょう。

いちいちローカルウインドウを確認するのは面倒になりますがその地道な作業がスキルアップにつながります。

まずはご自身のエクセル(仕事)の中で積極的に使ってみてください。必要なのは自分で手を動かす事です。

参考ですが同じ繰り返し処理でDo Loopについても動画を使って解説しています。以下リンク記事をご覧ください。

EnjoyExcel

タイトルとURLをコピーしました