Guinguette Marais Poitevin

ExcelとVBAを最大限に活用するには、ループを効率的に使用する方法を知っておく必要があります。

VBAでは、ループを使用することで、一連のオブジェクト/値を1つずつ分析することができます。

ここでは、ExcelでVBAのループを使用する簡単な例を紹介します。

データセットがあり、偶数行のすべてのセルをハイライトしたいとします。 この場合、VBAのループを使って範囲内を通過し、各セルの行番号を分析します。

もちろん、これはExcel VBAのループの非常に単純なものです（条件付き書式を使用してこれを行うこともできます）。

実際には、タスクの自動化に役立つExcel VBAのループを使って、さらに多くのことができます。

ここでは、VBAのループが役立つ実用的な例をいくつか紹介します。

• セルの範囲をループして各セルを分析する（特定のテキストが入っているセルをハイライトする）。
• すべてのワークシートをループして、それぞれに何かをする (保護/保護解除など)。
• 開いているすべてのワークブックをループする (そして、各ワークブックを保存するか、アクティブなワークブック以外を閉じる)。
• セル内のすべての文字をループする（文字列から数値部分を抽出する）
• 配列のすべての値をループする
• すべてのチャート/オブジェクトをループする（境界線を付けたり、背景色を変更する）

さて、Excel VBAでループを上手に使うためには、さまざまな種類があり、それぞれの正しい構文について知っておく必要があります。

このチュートリアルでは、Excel VBAのループの種類を紹介し、それぞれのループについていくつかの例を説明します

VBAを簡単に学びたい方は、私のExcel VBAオンライントレーニングをご覧ください。

For Next Loop

「For Next」ループは、コードのブロックを指定された回数だけ実行することができます。

例えば、1から10までの整数を手動で足すように指示した場合、最初の2つの数字を足し、その結果に3つ目の数字を足し、その結果に4つ目の数字を足す、というようにしていきます。

VBAのFor Nextループでも同じロジックが使われています。

ループを何回実行するかを指定し、ループが実行されるたびにコードが何をするかを指定します。

以下にFor Nextループの構文を示します。

For Counter = Start To End Next 

For Nextループでは、ループを実行するのに使われるCounter（または任意の変数）を使用できます。

例えば、最初の 10 個の正の整数を加算したい場合、カウンタの値は 1 から 10 までとなります。

For Next ループの仕組みをよりよく理解するために、いくつかの例を見てみましょう。

例 1 – 最初の 10 個の正の整数の加算

以下は、For Next ループを使用して最初の 10 個の正の整数を加算するコードです。

そして、これらの数値の合計を示すメッセージ ボックスを表示します。

Sub AddNumbers()Dim Total As IntegerDim Count As IntegerTotal = 0For Count = 1 To 10Total = Total + CountNext CountMsgBox TotalEnd Sub

このコードでは、For Nextループに入る前にTotalの値を0に設定しています。

ループに入ると、ループごとにTotalの値を保持します。

そして最後に、ループが終了したとき、「Total」変数は最初の10個の正の整数の合計を持ちます。

MsgBoxは、結果を単純にメッセージボックスに表示します。

例 2 – 最初の 5 つの偶数の正整数の加算

最初の 5 つの偶数の正整数 (すなわち、2,4,6,8,10) を加算するには、偶数のみを考慮し、奇数を無視する条件を持つ同様のコードが必要です。

それを実現するコードは次のとおりです。

Sub AddEvenNumbers()Dim Total As IntegerDim Count As IntegerTotal = 0For Count = 2 To 10 Step 2Total = Total + CountNext CountMsgBox TotalEnd Sub

Count の値を 2 から開始し、「ステップ 2」を使用したことに注意してください。

従って、Count の値は 2 から始まり、ループが実行されるたびに 4、6、8、10 となっていきます。

注: 別の方法として、1 から 10 までのループを実行し、ループ内で数字が偶数か奇数かをチェックすることもできます。

Stepの値は負の値にすることもできます。

例 3 – 選択されたセルにシリアル番号を入力する

For Next ループを使用して、オブジェクトのコレクション (セルやワークシート、ワークブックなど) を通過することもできます。

Sub EnterSerialNumber()Dim Rng As RangeDim Counter As IntegerDim RowCount As IntegerSet Rng = SelectionRowCount = Rng.Rows.CountFor Counter = 1 To RowCountActiveCell.Offset(Counter - 1, 0).Value = CounterNext CounterEnd Sub

上記のコードでは、まず選択された行の数を数え、その値を変数RowCountに代入しています。

また、選択は任意の数の行にすることができるので、変数RngをSelectionに設定しています（「Set Rng = Selection」という行があります）。

例 4 – アクティブなワークブックのすべてのワークシートを保護する

「For Next」ループを使用して、アクティブなワークブックのすべてのワークシートを処理し、各ワークシートを保護 (または保護解除) することができます。

以下はそのためのコードです:

Sub ProtectWorksheets()Dim i As IntegerFor i = 1 To ActiveWorkbook.Worksheets.CountWorksheets(i).ProtectNext iEnd Sub

上記のコードでは、ActiveWorkbook.Worksheets.Countを使用してシートの数をカウントしています。

このコードは、ActiveWorkbook.Worksheets.Countを使ってシートの数を数え、ループを何回実行するかをVBAに伝えています。

毎回、I番目のワークブック（Worksheets(i)を使用）を参照し、保護しています。 ただ、Worksheets(i).Protectという行をWorksheets(i).UnProtectに変更するだけです。

Nested ‘For Next’ Loops

Excelでより複雑な自動化を行うには、入れ子になった’For Next’ループを使用することができます。

例を挙げて使い方を説明します。

システムに 5 つのワークブックがあり、これらすべてのワークブックのワークシートを保護したいとします。

以下がそのためのコードです。

Sub ProtectWorksheets()Dim i As IntegerDim j As IntegerFor i = 1 To Workbooks.CountFor j = 1 To Workbooks(i).Worksheets.CountWorkbooks(i).Worksheets(j).ProtectNext jNext iEnd Sub

上記は、1つのFor Nextループを別のFor Nextループの中で使用しているため、ネストされたFor Nextループになっています。

For Next ループにおける「EXIT For」ステートメント

「EXIT For」ステートメントは、「For Next」ループを完全に終了させることができます。

このステートメントは、特定の条件が満たされたときに For Next ループを終了させたい場合に使用できます。 この場合、各セルの値を分析し、それに応じてフォントの色を変更する必要があります。

しかし、コードをより効率的にするために、まず、リストに負の値があるかどうかをチェックします。

以下はこれを実行するコードです:

Sub HghlightNegative()Dim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))Counter = Rng.CountFor i = 1 To CounterIf WorksheetFunction.Min(Rng) >= 0 Then Exit ForIf Rng(i).Value < 0 Then Rng(i).Font.Color = vbRedNext iEnd Sub

ネストされた「For Next」ループ内で「Exit For」ステートメントを使用すると、実行されたループから抜け出し、For Nextループの後にあるコードの次の行を実行しに行きます。

例えば、以下のコードでは、’Exit For’ステートメントにより、内側のループから抜け出しますが、外側のループは引き続き動作します。

Sub SampleCode()For i = 1 To 10For j = 1 to 10Exit ForNext JNext iEnd Sub

Do Whileループ

「Do While」ループでは、ある条件をチェックし、その条件が満たされている（またはTRUEである）間、ループを実行することができます。

Do Whileループには、2種類の構文があります。

Do Loop

and

DoLoop 

この2つの違いは、1つ目の場合はコードブロックが実行される前にまずWhile条件がチェックされ、2つ目の場合はコードブロックが先に実行されてからWhile条件がチェックされるということです。

つまり、両方のケースで While 条件が False の場合でも、2 番目のケースではコードが少なくとも 1 回は実行されるということです (コードが 1 回実行された後に「While」条件がチェックされるため)。

では、VBAでDo Whileループを使う例を見てみましょう。

Example 1 – Add First 10 Positive Integers using VBA

VBAのDo Whileループを使って、最初の10個の正の整数を加算したいとします。

以下は、このDo Whileループを実行し、その結果をメッセージボックスに表示するVBAのコードです。

Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do While i <= 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub

上記のループは、’i’の値が11になるまで動作し続けます。

ループの中で、最終的な値を保持するResult変数を使用しています。 ループが完了すると、メッセージボックスに「Result」変数の値が表示されます。

Example 2 – Enter Dates For the Current Month

例えば、今月のすべての日付をワークシートの列に入力するとします。

次の Do While ループ コードを使用してこれを行うことができます。 変数「CMDate」の「Month」の値が現在の月の値と一致するまで、ループは続きます。

Exit Do Statement

ループを抜けるには、Exit Doステートメントを使用します。

例えば、最初の10個の日付だけを入力したい場合、最初の10個の日付が入力されるとすぐにループを終了させることができます。

以下のコードではこのようになります：

Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do While Month(CMDate) = Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub

上記のコードでは、IFステートメントを使用して、iの値が10より大きいかどうかをチェックしています。

Do Untilループ

「Do Until」ループは「Do While」ループとよく似ています。

「Do While」では指定された条件が満たされるまでループが実行されますが、「Do Until」では指定された条件が満たされるまでループします。

Do Loop

and

DoLoop 

この2つの違いは、1つ目の場合はコードブロックが実行される前にまずUntil条件がチェックされ、2つ目の場合はコードブロックが先に実行されてからUntil条件がチェックされるということです。

つまり、どちらのケースでもUntil条件がTRUEの場合、2番目のケースではコードが少なくとも1回は実行されるということです（コードが1回実行された後にUntil条件がチェックされるため）。

では、VBAでDo Untilループを使用する例をいくつか見てみましょう。

注：Do Untilの例はすべてDo Whileと同じです。

Example 1 – Add First 10 Positive Integers using VBA

VBAのDo Untilループを使って、最初の10個の正の整数を加算したいとします。

以下は、このループを実行し、結果をメッセージボックスに表示するVBAのコードです。

Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do Until i > 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub

上記のループは、’i’の値が11になるまで動作し続けます。

Example 2 – Enter Dates For the Current Month

例えば、今月の日付をすべてワークシートの列に入力したいとします。

そのためには、次のような Do Until ループ コードを使用します：

Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1CMDate = CMDate + 1LoopEnd Sub

上記のコードは、ワークシートの最初の列 (A1 から始まる) にすべての日付を入力します。

Exit Do Statement

ループから抜け出すには、「Exit Do」ステートメントを使用します。

コードが「Exit Do」行を実行するとすぐに、Do Untilループから抜け出し、ループの直後の次の行に制御を渡します。

例えば、最初の10個の日付だけを入力したい場合は、最初の10個の日付が入力されたらすぐにループを終了します。

以下のコードはこのようになります:

Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub

上記のコードでは、’i’の値が10になるとすぐにExit Do文が実行され、ループが終了します。

For Each

VBAでは、’For Each’ループを使ってコレクションの集合をループさせることができます。

以下はExcel VBAにおけるコレクションの例です：

• 開いているすべてのワークブックのコレクション
• ワークブック内のすべてのワークシートのコレクション
• 選択されたセルの範囲内のすべてのセルのコレクション
• 選択されたセルの範囲内のすべてのセルのコレクション。
• ワークブック内のすべてのグラフまたは図形のコレクション

「For Each」ループを使用すると、コレクション内の各オブジェクトを調べて、それに対して何らかのアクションを実行することができます。

たとえば、ワークブックのすべてのワークシートを調べて保護したり、選択範囲のすべてのセルを調べて書式を変更したりすることができます。

「For Each」ループ（「For Each-Next」ループとも呼ばれます）では、コレクション内のオブジェクトの数を知る必要はありません。

以下にExcel VBAのFor Each-Nextループの構文を示します。

For Each element In collectionNext 

それでは、ExcelでFor Eachループを使用する例をいくつか見てみましょう。

Example 1 – Go through all the Worksheets in a Workbook (and Protect it)

すべてのワークシートを保護したいワークブックがあるとします。

以下のFor Each-Nextループを使うと簡単にできます。

上記のコードでは、変数「ws」をワークシートオブジェクトとして定義していますが、これはVBAに「ws」をワークシートオブジェクトとして解釈するように指示するものです。

ワークブック内のすべてのワークシートを保護しようとした他のループとは異なり、ここでは、ワークブック内にあるワークシートの数を気にする必要がないことに注意してください。

例 2 – 開いているすべてのワークブックを調べる (そしてすべてを保存する)

同時に複数のワークブックを扱う場合、これらすべてのワークブックを一度に保存できると便利なことがあります。

以下のVBAコードでこれを行うことができます:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In Workbookswb.SaveNext wbEnd Sub

このコードでは、ワークブックを特定の場所に保存するように求めるプロンプトは表示されないことに注意してください (初めて保存する場合)。 このコードは、これらのファイルがすでに保存されていて、変更を加え、すべてのワークブックをすばやく保存したい場合に最適です。

例 3 – 選択範囲のすべてのセルを通過させる (負の値をハイライトする)

「For Each」ループを使用すると、特定の範囲または選択した範囲のすべてのセルをループで通過させることができます。

これは、各セルを分析し、それに基づいてアクションを実行したい場合に役立ちます。

例えば、以下のコードは、選択範囲内のすべてのセルを調べ、負の値を持つセルの色を赤に変更します。

上記のコードでは、For Each-Nextループが選択範囲内のセルのコレクションを通過します。 IF文は、セルの値が負であるかどうかを識別するために使用されます。 負の値の場合、そのセルには赤の内部色が与えられ、そうでない場合は次のセルに進みます。

選択範囲がなく、特定のセルから始まる列のすべての塗りつぶしたセルをVBAで選択したい場合 (塗りつぶしたセルをすべて選択するためにControl + Shift + Down arrowキーを使用するように)、以下のコードを使用することができます:

Sub HighlightNegativeCells()Dim Cll As RangeDim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))For Each Cll In RngIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub

上記の例では、塗りつぶしたセルの数は問題ではありません。 セル A1 から開始し、列内の連続したすべての塗りつぶしセルを分析します。

また、セル A1 を選択する必要はありません。

「Exit For」ステートメント

For Each-Next ループ内で「Exit For」ステートメントを使用すると、ループから抜け出すことができます。 これは通常、特定の条件を満たす場合に行われます。

例えば、例3では、一連のセルを通過する際に、負の値があるかどうかをチェックすると、より効率的になります。

以下は、これを行うVBAコードです:

Sub HighlightNegativeCells()Dim Cll As RangeFor Each Cll In SelectionIf WorksheetFunction.Min(Selection) >= 0 Then Exit ForIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub

VBAコードをどこに置くか

ExcelワークブックのどこにVBAコードを置くかについて考えてみましょう

Excelには、VBAエディターと呼ばれるVBAバックエンドがあります。

1. 「開発者」タブに移動します。
2. 「Visual Basic」オプションをクリックします。
3. VBエディタの「プロジェクトエクスプローラー」ペインで、コードを挿入したいワークブックの任意のオブジェクトを右クリックします。
4. [挿入]に移動し、[モジュール]をクリックします。
5. モジュールウィンドウにコードをコピー＆ペーストします。

You May Also Like the following Excel Tutorials:

• How to record a macro in Excel.
• Creating User-defined functions in Excel.
• Excel VBA Msgbox
• How to Run a Macro in Excel.
• Excelでアドインを作成・使用する方法
• Excel VBA イベント – 簡単（完全）ガイド
• ExcelでVBAを使ってデータをソートする方法（ステップバイステップガイド）
• VBA初心者のための24の便利なExcelマクロ例（すぐに使える）

i

• Excel VBA InStr関数の使い方（実践的なEXAMPLES付き）.
• Excelパーソナルマクロワークブック｜保存 & すべてのワークブックでマクロを使う.
• Excel VBAでSelect Caseを使う.