Excel VBAを活用する上で欠かせないのが、繰り返し処理です。データの大量処理やループ処理など、作業を自動化や省力化するには、繰り返し処理のつかいかたをマスターすることが重要です。本記事では、VBAの繰り返し処理について基礎から解説し、実際のコード例を交えながらマクロのつくりかたをわかりやすく紹介します。
繰り返し処理の内、以前の文字入力を制限する方法として紹介した記事でも触れたDo While文やDo Until文については重なる部分はありますが、以前の記事では、使用用途を限定した紹介であったため、こちらでは繰り返し処理文の基本的な書き方を用いて紹介しています。
【結論】繰り返し処理はFor文またはDoLoop文をつかう
エクセルVBAで繰り返し処理をするマクロを作るために必要なステートメントは4つあります。
それぞれの特徴は以下のとおりです。
●For Nextステートメント
・指定した回数だけ処理を繰り返すことができる。
・指定した変数に対して、指定した増分で値を更新しながら繰り返し処理を行うことができる。
・ループ変数を外部から参照することができないため、処理の安全性が高い。
・ネスト（入れ子）されたFor Nextステートメントを使用することで、複雑な処理を実行することができる。
VBAで繰り返しと言えば、ForNextステートメントと言えるくらいよく使われる。
●For Eachステートメント
・指定したオブジェクトの全ての要素に対して処理を繰り返すことができる。
・オブジェクト内の要素をループ変数に自動的に代入するため、要素の数を数える必要がない。
・配列やコレクション、セル範囲などのデータ構造に対して簡単にループ処理を実行することができる。
・ループ変数を外部から参照することができないため、処理の安全性が高い。
シートなどのオブジェクトに対する繰り返し処理に使いやすい。
●Do Whileステートメント
・指定した条件がTrueの場合に処理を繰り返すことができる。
・条件式を最初に評価するため、条件式がFalseの場合は一度も処理を実行しない。
・ループ内でループ変数を自動的に更新することができないため、手動で値を更新する必要がある。
・Exit Doステートメントを使用することで、ループ処理を途中で終了することができる。
条件が満たされている間は繰り返すといった状況で使う。
●Do Untilステートメント
・指定した条件がFalseの場合に処理を繰り返すことができる。
・条件式を最初に評価するため、条件式がTrueの場合は一度も処理を実行しない。
・ループ内でループ変数を自動的に更新することができないため、手動で値を更新する必要がある。
・Exit Doステートメントを使用することで、ループ処理を途中で終了することができる。
条件が満たされない間は繰り返すといった状況で使う。
独学の学習効率でお悩みの人必見！
＜動画学習見放題サービス＞
初心者にやさしいチューターなら今すぐにはじめられる
■オンラインで学習したい
■プログラミングで副業をはじめたい
■パソコン・エクセルの学習をしたい
エクセルVBAでForやDoLoop文で繰り返し処理をするマクロ
本記事の概要
この記事では、エクセルVBAで繰り返し処理を実行するマクロのつくりかたを紹介します。
VBAには、For Nextステートメント、For Eachステートメント、Do Whileステートメント、Do Untilステートメントなど、複数の繰り返し処理の書きかたが用意されています。
それぞれのステートメントの特徴や使い方を詳しく解説し、実際のコード例を交えて具体的な使い方を紹介します。
また、繰り返し処理のネストや、ループ内での条件分岐など、より複雑な処理の実現方法についても触れます。この記事を読むことで、VBAで繰り返し処理を実行する方法を理解し、効率的なプログラミングを行うことができます。
※ここでは、コンピュータープログラムの繰り返し処理を構成するための手続きや命令における文やブロックを、ステートメントと表現していますが、これはプログラミングという分野の学習を進める上で必ず出会う単語であり、恐らく本ブログを訪れた人は、他のウェブサイトを閲覧する機会があることを想定した表現としています。横文字表現が苦手な人も、表現を難しくとらえずに単なる作法(書きかた)と理解してもらえれば幸いです。
VBAにおける繰り返し(ループ)処理についての基礎知識
繰り返し処理とは、同じ操作や条件にしたがった操作を何度も繰り返して実行することです。
Excel VBAでは、ループ処理を使って繰り返し処理を実装できます。
ループ処理をするマクロを書く方法としては、大きく分けて以下の2つとなります。
- For Next ステートメント
- 指定回数や、あるかたまりの個数に応じた繰り返し回数である場合につかう
例：10回繰り返す ／ シート数分繰り返す など。
- 指定回数や、あるかたまりの個数に応じた繰り返し回数である場合につかう
- Do Loop ステートメント
- 特定の条件の間や、特定の条件までといった、条件に応じた繰り返しにつかう
例：変数が10以上になるまで繰り返す ／ セルの値が空白になるまで繰り返す など。
- 特定の条件の間や、特定の条件までといった、条件に応じた繰り返しにつかう
Forの繰り返しは『回数指定』、Doでの繰り返しは『条件指定』とおぼえてね。
For Nextステートメントの構文とサンプルコード
For Nextステートメントは、指定した回数だけ同じ処理を繰り返します。For Nextステートメントは、次のような構文を持ちます。
For 変数 = 初期値 To 終了値 Step 増分
処理
Next 変数
サンプルコード
以下のサンプルコードでは、イミディエイトウィンドウに1から10の数値を表示します。
Option Explicit
Sub sample()
Dim i As Long
For i = 1 To 10
Debug.Print i'イミディエイトウィンドウに1～10を表示する
Next i
End Subサンプルコード2
以下のサンプルコードでは、シート1の(A1)セルから(I9)セルの範囲に九九の表を作成します。
Option Explicit
Sub 繰り返しで九九を計算する()
Dim i As Long, j As Long '繰り返し用の変数を宣言する
For i = 1 To 9 'ForNext構文で変数 i が1～9まで繰り返す
For j = 1 To 9 'ForNext構文で変数 j が1～9まで繰り返す
ThisWorkbook.Sheets(1).Cells(i, j) = i * j 'i*jで掛け算の結果をセルに入力する
Next j
Next i
End Sub
変数 i が 1 の間に変数 j は、1から9回まで変化します。この変数 i と j はセルの座標を指定する役割と、掛け算の式の役割もしています。
1巡目では変数 i に1が代入されますが、その間に内側の繰り返し変数 j が1から9まで繰り返されていきますので、セルの座標が(1,1)、座標(1,2)、座標(1,3)…と右にひとつずつズレていきます。これと同様に入力する掛け算の式も1×1、1×2、1×3…と繰り返す回数に応じて結果がかわってきます。
変数 j が9まで繰り返されると、内側の繰り返しを抜けて、外側の繰り返しである変数 i が1から2に代入され、再び変数 j の繰り返しの処理が開始されます。これが変数 i に9が代入されるまで実行されることで九九の表を作成しています。
For Eachステートメントの構文とサンプルコード
VBAのFor Eachステートメントは、配列やコレクションなどの要素を反復処理するために使用されます。For Eachステートメントは、次のような構文を持ちます。
For Each 要素 In コレクション
処理
Next 要素
コレクションがオブジェクトなら要素はオブジェクト型の変数で宣言、配列であればバリアント型の変数で宣言することに注意が必要です。
サンプルコード
Option Explicit
Sub sample()
Dim arr() As Variant'バリアント型で宣言する
arr = Array(1, 2, 3, 4, 5)'Array関数で配列を作成し、arrに代入する
Dim num As Variant
For Each num In arr
MsgBox num'メッセージボックスで1～5を表示する
Next num
End SubDo Whileステートメントの構文とサンプルコード
Do Whileステートメントは、条件がTrueの間だけ同じ処理を繰り返します。以下のように書きます。
Do While 条件式
処理
Loop
サンプルコード
Option Explicit
Sub sample()
Dim i As Long
i = 1
Do While i <= 10
Debug.Print i'イミディエイトウィンドウに1～10を表示する
i = i + 1
Loop
End SubDo Untilステートメントの構文とサンプルコード
Do Untilステートメントは、条件がFalseの間だけ同じ処理を繰り返します。以下のように書きます。
Do Until 条件式
処理
Loop
サンプルコード
Option Explicit
Sub sample()
Dim i As Long
i = 1
Do Until i > 10
Debug.Print i'イミディエイトウィンドウに1～10を表示する
i = i + 1
Loop
End Sub
繰り返し処理を使うことで、同じ処理を手作業で何度も繰り返す負担を軽くすることができます。また、繰り返し処理は、大量のデータを扱う場合や、複雑な計算を行う場合にも有効です。
ただし、無限ループにおちいることがないよう、ループの条件を正確に設定することが大切です。
ExcelVBAで繰り返し処理を行うことの重要性
Excel VBAで繰り返し処理を行うことは、プログラムの効率性を高めるために非常に重要です。
例えば、同じような処理を何度も行う場合、繰り返し処理を使えばコードを簡潔にまとめることができます。また、大量のデータを扱う場合には、繰り返し処理を使用することで、手動で1つずつ処理を行うよりも効率的にデータを処理できます。
さらに、繰り返し処理を使えば、複雑な処理も実現できます。例えば、Excel VBAでは、繰り返し処理の中で条件分岐ができます。これにより、複雑な条件に応じて、処理の流れの制御ができます。
繰り返し処理を使うことで、プログラムの作成時間やメモリ使用量を削減できます。より効率的なコードを書くことができれば、プログラムの実行速度も向上し、大規模な処理でも高速に処理ができます。
ExcelVBAにおける繰り返し処理の種類とそれぞれの使いどころ
繰り返し(ループ)処理の種類について
既に紹介していますが、繰り返し(ループ)処理の書き方については、以下の４種類があります。
それぞれの特徴については、本記事の冒頭で紹介しましたのでこちらでは最低限のみで記載します。
| ステートメントの種類 | 特徴(概要) |
|---|---|
| For Nextステートメント | 繰り返す回数を指定することができる |
| For Eachステートメント | オブジェクトや配列を網羅的に処理することができる |
| Do Whileステートメント | 条件判定でTrueの間は繰り返すことができる |
| Do Untilステートメント | 条件判定でFalseの間は繰り返すことができる |
各繰り返し(ループ処理)ステートメントの使いどころ
For Nextステートメントのつかいどころ
For Nextステートメントは、処理を繰り返す回数が事前に分かっている場合に有効です。例えば5回繰り返したい、10行分だけ繰り返したいなど具体的な回数を指定する場合に使います。
また、エクセルワークシート上の表では、行数分や、列数分を繰りかえすといったコードをよく使います。このコードを使うために、表の最終行や、最終列を数値で取得し、For Next文の指定回数として利用します。
ワークシートに上の表の最終行や、最終列の取得方法については、別の記事で紹介しているため、ここでは詳しく紹介しませんが、詳しく知りたい人は以下の記事をご覧ください。
For Eachステートメントのつかいどころ
For Eachステートメントは、配列やオブジェクトなどに対して全ての要素に対して繰り返し処理するときに便利です。例えばエクセルを新規作成時、ワークシートの数は1つだけですが、複数のワークシート分の処理が必要な場合や、特定のワークシートのセル範囲を網羅的に処理したい場合につかえます。
コレクション(複数の集合体)から、1つずつ取り出して順番に処理を繰り返していくようなイメージをすると分かりやすいかもしれません。
For eachステートメントの繰り返し(ループ)処理のイメージ
Do Whileステートメントのつかいどころ
Do Whileステートメントは条件式を指定して、この式に一致している間は処理を繰り返します。
使いどころとして、繰り返し(ループ)の実行回数が不明な場合に使います。
具体的に言えば、ユーザーに入力ボックスを表示して、許容されない文字が入力されている間は処理を繰り返す。他にも、表の各行に対して処理を繰り返すマクロでは、セルに入力されている値が空欄でない間は繰り返す。など、実行するタイミングや、状況で条件を判定しながら繰り返す回数に対応した処理が可能です。
Do Untilステートメントのつかいどころ
Do Untilステートメントは条件式を指定して、この式に一致するまでの間は処理を繰り返します。
UntilとWhileは逆説の条件ですが、こちらもWhileと同じく繰り返し(ループ)の実行回数が不明な場合に使います。
例えば、ある変数の値が10になるまで処理を繰り返す。他にも、表の各行に対して処理を繰り返すマクロでは、セルに入力されている値が空欄になるまで繰り返す。など、実行するタイミングや、状況で条件を判定しながら繰り返す回数に対応した処理が可能です。
独学の学習効率でお悩みの人必見！
＜動画学習見放題サービス＞
初心者にやさしいチューターなら今すぐにはじめられる
■オンラインで学習したい
■プログラミングで副業をはじめたい
■パソコン・エクセルの学習をしたい
ExcelVBAで繰り返し処理の実装方法
ExcelVBAで繰り返し処理を実装方法について
ここでは、それぞれの繰り返し文の実装方法を紹介していきます。
サンプルコードでは、内容を理解しやすくするために、For Nextステートメント、For Eachステートメント、Do Whileステートメント、Do Untilステートメントの4種類で同じ実行結果(イミディエイトウィンドウに1から10の数値の出力)となるコードを書いています。
実行結果は同じなので、先に実行結果をお見せした上で、それぞれのステートメントをつかったコードを紹介していきます。
実行結果(イミディエイトウィンドウには以下のように表示されます。)
1
2
3
4
5
6
7
8
9
10
数字の1から10までを表示するマクロだよ。
For Nextステートメントの実装方法
Option Explicit
Sub ForNextステートメント()
'カウンタ変数を宣言する
Dim count As Integer
'For Nextステートメントで変数countは1から10まで繰り返す
For count = 1 To 10
Debug.Print (count) '繰り返す処理を記述する
Next count
End Sub変数[count]が1のときから10まで繰り返す。数値で回数を指定しているところがポイントです。
For Eachステートメントの実装方法
Option Explicit
Sub ForEachステートメント()
'配列の宣言と初期化
Dim myArray As Variant
'配列にArray関数つかって1から10の数値を代入する
myArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
'要素を代入する変数itemを宣言する(バリアント型)
Dim item As Variant
'For Eachステートメントをつかって配列内の要素数分の処理を繰り返す
For Each item In myArray
Debug.Print item '繰り返す処理を記述する
Next item
End Sub配列[myArray]の要素数の数だけ繰り返すところがポイントです。
Do Whileステートメントの実装方法
Option Explicit
Sub DoWhileステートメント()
'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1
'Do Whileステートメントをつかってcountが10になるまでの間繰り返す
Do While count <= 10
Debug.Print (count) '繰り返す処理を記述する
count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop
End Sub条件式で、変数[count]が10以下の条件である間は繰り返すといった指定方法がポイントです。
Do Untilステートメントの実装方法
Option Explicit
Sub DoUntilステートメント()
'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1
'Do Untilステートメントをつかってcountが10になるまで繰り返す
Do Until count > 10
Debug.Print (count) '繰り返す処理を記述する
count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop
End Sub条件式で、変数[count]が10よりも大きくなるまでは繰り返すといった指定方法がポイントです。
繰り返し(ループ)処理のコードの制御構文の比較
ここでは、前に紹介した繰り返し処理のステートメントの制御構文を比較していきます。
For Nexrステートメント
Option Explicit
Sub ForNextステートメント()
'カウンタ変数を宣言する
Dim count As Integer
'For Nextステートメントで変数countは1から10まで繰り返す
For count = 1 To 10
Debug.Print (count) '繰り返す処理を記述する
Next count
End SubFor Eachステートメント
Option Explicit
Sub ForEachステートメント()
'配列の宣言と初期化
Dim myArray As Variant
'配列にArray関数つかって1から10の数値を代入する
myArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
'要素を代入する変数itemを宣言する(バリアント型)
Dim item As Variant
'For Eachステートメントをつかって配列内の要素分の回数繰り返す
For Each item In myArray
Debug.Print item '繰り返す処理を記述する
Next item
End SubFor NextとFor Eachステートメントの比較で注目しておきたい点は、宣言する変数のデータ型です。
※変数のデータ型については、こちらの記事で紹介しています。
For Nextは繰り返す回数を指定できるステートメントなので、宣言した変数countは整数型に指定し、数値の1～10が処理の度に1ずつカウントしていきます。
なお、この場合、別途 count = 1 の変数に値を代入するコードは不要で、代わりに For count = 1 To 10 のコードが変数countに1～10を代入しています。さらに言えば、For Nextよりも前に変数countに値の代入をしていても、繰り返し処理の中では、変数countは1～10の値に書き変わってしまう点はおぼえておきましょう。
For Nextステートメントの末尾にあるNext countの部分で変数countに代入された数値が1加算されます。For Nextでは、ループごとにステップする数の指定もできます。Stepを指定した場合は、加算される数値に応じた数値が加算されます。
一方、For Eachステートメントでは、繰り返す回数を指定するのではなく、集合体となる配列やオブジェクトの要素の数だけ繰り返します。ここでは、バリアント型の配列myArrayを宣言し、Array関数で1～10の数値を代入。これにより、10コの要素(数値)の集合体となったmyArrayに対する繰り返す回数は10回です。
そして、For eachステートメントでは、要素を代入するための変数をつかいます。サンプルコードでは、itemという名称で変数を宣言していますが、要素を代入する変数はバリアント(Variant)型でなければエラーが生じる点です。
※集合体がオブジェクトなら、object型でも実行が可能です。例えば、セル範囲を集合体として、一つのセルを要素として全セル分繰り返し処理するのであれば、宣言する変数は、object(Range)型でも実行が可能です。
集合体や、要素のデータ型について分かっているのであればそれに合わせた型を利用できますが、データ型についての理解が不安であれば、バリアント型を利用しましょう。
Do Whileステートメント
Option Explicit
Sub DoWhileステートメント()
'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1
'Do Whileステートメントをつかってcountが10になるまでの間繰り返す
Do While count <= 10
Debug.Print (count) '繰り返す処理を記述する
count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop
End SubDo Untilステートメント
Option Explicit
Sub DoWhileステートメント()
'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1
'Do Whileステートメントをつかってcountが10になるまでの間繰り返す
Do While count <= 10
Debug.Print (count) '繰り返す処理を記述する
count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop
End SubDo WhileとDo Untilステートメントでは、Forステートメントのように回数や集合体の要素数分を繰り返すのではなく、条件を指定します。
Whileは、条件判定の結果がTrueの間は処理を継続し、判定結果がFalseになった時点で終了し、逆にUntilは、Falseの間は処理を継続。Tureになったら終了します。
サンプルコードのコメントでも分かるとおり、Do WhileやUntilステートメントでは、Forステートメントのように末尾にNextがないので、無限ループを回避するための対策として、繰り返しのたびに変数countに＋1の処理を実行しています。
繰り返し(ループ)変数の使い方や、変数のスコープについて
ここまでで、VBAで繰り返し処理を行う場合、ForステートメントやDo While・Untilステートメントを使用することが一般的であることは理解できたかと思います。
これらのステートメントでは、繰り返し(ループ)変数を使用する手段が多くつかわれます。繰り返し(ループ)変数とは、繰り返しの処理内で変化する変数ですが、ここでは繰り返し(ループ)変数のつかいかたのポイントと変数のスコープについて説明します。
繰り返し(ループ)変数のつかいかたのポイント
繰り返し(ループ)変数の初期値を設定する
繰り返し(ループ)変数を使用する場合、最初に変数の初期値を設定する必要があります。初期値を設定しない場合、繰り返しが正しく動作しない可能性があります。
繰り返し(ループ)変数の値を更新する
繰り返し(ループ)内で、繰り返し(ループ)変数の値を更新することが必要です。更新しない場合、繰り返し(ループ)が正しく動作しない可能性があります。
繰り返し(ループ)変数の値を使用する
繰り返し(ループ)内で、繰り返し(ループ)変数の値を使用することができます。例えば、繰り返し(ループ)変数の値に応じて処理を分岐するなど、様々な用途があります。
変数のスコープについて
変数のスコープとは、変数が有効な範囲のことです。
ここではVBAにおける変数の有効範囲(スコープ)について紹介します。スコープについて詳しく説明するためには、VBAにおけるモジュールやプロシージャなどの単位・範囲の事前説明が必要だと考えているため、別の記事で詳しい内容を書く予定です。
ここでは変数の宣言のしかたによって、その変数が使える範囲が異なるといった理解で大丈夫です。
モジュールレベルの変数[Public／Private]
モジュールの宣言セクションに記入ができます。
モジュールレベルの変数において、Publicで宣言したものは、他のモジュールからも使用ができます。
例えば、Module1で宣言した変数をModule2をつかって値の代入ができます。これはグローバル変数と呼ばれたりもしますが、広い範囲で変数を利用できるメリットの反面、プログラム全体の可読性が悪くなるデメリットを発生させることを理解しておきましょう。
一方、Privateで宣言したモジュールのスコープは、同じモジュール内で使用ができます。同じモジュール内の他プロシージャからでも使用ができます。
プロシージャレベルの変数
プロシージャレベルの変数は、変数を宣言したプロシージャ内でのみ使用ができます。プロシージャレベルの変数は、プロシージャの先頭でDimステートメントを使用して宣言できます。こちらはローカル変数と呼ばれ、プロシージャ実行中のみの有効期間となる変数です。
ExcelVBAの繰り返し処理の例題
実際の問題を解決するために、繰り返し処理を使ったコード例
For Nextステートメントで行または列に対する計算
(1)合計列の緑色のセルに入力されている点数を合計する
(2)6行目(小林みどりさん)の青色のセルを合計する
入力内容
(1)合計列の緑色のセルに入力されている点数を合計する
Option Explicit
Sub For_Next_sample1()
Dim i As Long
Dim value As Long
For i = 2 To 11 Step 3
value = value + ThisWorkbook.Worksheets("Sheet5").Range("K" & i).value
Next i
MsgBox ("K列の緑色のセルの合計は" & value)
End Subポイントは、Step 3の部分です。増量の指定により、変数iの値の変化は以下のとおり。.Range(“K” & i) でK列の行数を変数iに指定しているため、Ｋ列の2行目・5行目・8行目・11行目に記入された値を変数valueに足していきます。
| 状態 | 変数iの値 | 増量(Step) |
|---|---|---|
| 繰り返し処理開始 | 2 | |
| 2回目の繰り返し処理開始 | 5 | 3 |
| 3回目の繰り返し処理開始 | 8 | 3 |
| 4回目の繰り返し処理開始 | 11 | 3 |
実行結果
緑色に着色したセルの合計を算出することができました。
(2)6行目(小林みどりさん)の青色のセルを合計する
入力内容
Option Explicit
Sub For_Next_sample2()
Dim i As Long
Dim value As Long
For i = 6 To 12 Step 2
value = value + ThisWorkbook.Worksheets("Sheet5").Cells(6, i).value
Next i
MsgBox ("6行目の青色のセルの合計は" & value)
End Subこちらのプログラムでは、変数iの値は6～12となり、増量は Step 2で指定しています。今度は行を固定して、該当する列に入力された値の加算を繰り返しています。
| 状態 | 変数iの値 | 増量(Step) |
|---|---|---|
| 繰り返し処理開始 | 6 | |
| 2回目の繰り返し処理開始 | 8 | 2 |
| 3回目の繰り返し処理開始 | 10 | 2 |
| 4回目の繰り返し処理開始 | 12 | 2 |
実行結果
青色に着色したセルの合計を算出することができました。
For Nextステートメントで条件に合わせたセルの操作
今度は、5教科の点数が入力されているF列～J列の中で、30点以下のセルを赤色にするプログラムを作成してみます。
入力内容
Option Explicit
Sub For_Next_sample3()
Dim i As Long, j As Long
For i = 2 To 11
For j = 6 To 10
If ThisWorkbook.Worksheets("Sheet5").Cells(i, j).value <= 30 Then
ThisWorkbook.Worksheets("Sheet5").Cells(i, j).Interior.Color = RGB(255, 0, 0)
End If
Next j
Next i
MsgBox ("30点以下を赤色にしました。")
End SubFor Nextステートメントで繰り返し処理を入れ子(ネスト)して、行の繰り返しと、列の繰り返しを実現しています。処理部分はIfステートメントで、条件判定により30点以下のセルを赤色に着色します。
変数iが2の間に、内側のループで変数jが6～10と更新されていき、変数jが10まで到達すると、外側のループが一回りし、変数iは3に更新されます。これを繰り返し変数iが11、変数jが10になるまで処理を継続します。
変数iとjは、セルの座標として利用しているため、繰り返し処理の最初は、Cells(2,6)です。
つまり、2行目のF列が30点以下か判定し、31点のため処理は何もしない。
次にG列[Cells(2,7)]→H列[Cells(2,8)]→I列[Cells(2,9)]と進み、I列は16点なので、判定の結果がTrueでセルを赤色に変更します。2行目のJ列[Cells(2,10)]まで到達すると、次は3行目のF列[Cells(3,6)]といったながれで処理します。
実行結果
For Eachステートメントで範囲内の全てのセルに数値を入力する
以下のセル範囲をオブジェクト型の変数(area)に代入し、繰り返し処理で全てのセルに1つずつ数値を入力するプログラムを作成します。
入力内容
Option Explicit
Sub ForEachステートメント_Image()
Dim ws As Worksheet 'Worksheet型の変数を宣言
Dim area As Range 'Range型の変数を宣言
Set ws = ThisWorkbook.Sheets("テストシート") '作業対象シートを代入
Set area = ws.Range("A1", "J5") '作業対象範囲を代入
Dim cell As Variant '繰り返し用の要素を代入する変数を宣言(オブジェクト)
Dim i As Integer: i = 1 '入力する数値を代入する変数を宣言する(オブジェクト)
'作業対象範囲から全てのセルに数値を入力する繰り返し処理
For Each cell In area
cell.value = i
i = i + 1
Next cell
End Sub実行結果
A1からJ5セルの範囲に1セルずつに数値を記入することができました。このサンプルコードでは、変数iの値を範囲内のセルに入力しますが、入力後に変数iに1が足されていくので、次のセルに入力される数値は i + 1です。
入力された数値に着目すると、For Eachステートメントで繰り返し処理を実行した場合、値を入力する順番がA1→B1→C1…J1と列を移動しながら処理を進め、最終列のJ列まで完了した後で、行を移動し、A2セルを処理しています。それを繰り返し、最後のJ5セルまで処理されたことが分かります。
For Eachステートメントでオブジェクトの色を変更する
For Eachステートメントでは、セルを対象にするだけでなく、ワークシートに挿入した図形(オブジェクト)の色を変えるプログラムも作成できます。
入力内容
Option Explicit
Sub ChangeShapeLineColor()
Dim shp As Shape 'Shape型の変数を宣言する(objectでも良い)
'カラーという名前のシート上にある図形の数だけ繰り返す
For Each shp In Sheets("color").Shapes
'Select Caseステートメントで現在の色を判定して処理を実行
Select Case shp.Fill.ForeColor.RGB
Case RGB(255, 0, 0) '元の色が赤色なら緑色に変更
shp.Line.ForeColor.RGB = RGB(0, 255, 0)
shp.Fill.ForeColor.RGB = RGB(0, 255, 0)
shp.Fill.BackColor.RGB = RGB(0, 255, 0)
Case RGB(0, 255, 0) '元の色が緑色なら青色に変更
shp.Line.ForeColor.RGB = RGB(0, 0, 255)
shp.Fill.ForeColor.RGB = RGB(0, 0, 255)
shp.Fill.BackColor.RGB = RGB(0, 0, 255)
Case RGB(0, 0, 255) '元の色が青色なら赤色に変更
shp.Line.ForeColor.RGB = RGB(255, 0, 0)
shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
shp.Fill.BackColor.RGB = RGB(255, 0, 0)
End Select
Next shp
End Sub実行結果
ワークシート上にある全てのオブジェクトの色を変化するプログラムの実行結果は以下のとおり。
Select Caseステートメントで元の色を判定して、赤なら緑、緑なら青、青なら赤へと元の色に応じて他の色に塗りかえます。
以下の画像は、サンプルプログラムを実行している様子の画像です。
実行される度に図形の色を変えるプログラムですが、For Eachステートメントで、ワークシート上の3つのオブジェクト(図形)を集合体として、全てのオブジェクト(図形)を繰り返し処理で1つずつ色をかえています。
Do Whileステートメントでデータのフィルタリング
Do Whileステートメントをつかって、配列から指定条件に該当する値をフィルタリング(抽出)するサンプルコードを書いてみます。
入力内容
Option Explicit
Sub Do_While_sample()
Dim Arr1 As Variant
Dim i As Integer, j As Integer
Dim Arr2() As String
' テストデータを設定
Arr1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
' フィルタリングを実行
i = 0
Do While i <= UBound(Arr1)
If Arr1(i) Mod 3 = 0 Then
ReDim Preserve Arr2(j)
Arr2(UBound(Arr2)) = Arr1(i)
j = j + 1
End If
i = i + 1
Loop
' 結果を表示
Debug.Print "フィルタリングされた値は" & Join(Arr2, ",") & "です。"
End Sub変数の宣言
まずは、テストデータを代入する入れる変数Arr1を宣言します。その他に繰り返し用の変数iと配列のインデックスとなる変数jを宣言します。最後に該当した値を代入する配列Arr2を宣言します。
Array関数をつかって変数Arr１に配列を作成する
次に、変数Arr1にArray関数をつかって1～10の値で配列を作成します。これによりArr1は以下になります。
| 変数Arr1のインデックス | 値 |
|---|---|
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 7 | 8 |
| 8 | 9 |
| 9 | 10 |
Arr1要素の数だけを繰り返す条件式とIfステートメントの条件判定による処理
Do Whileステートメントにて繰り返し処理ですが、Arr1の全ての要素分を繰り返す条件にしたいため、Ubound関数で要素数を取得します(サンプルコードでは9となる)。変数iが9以下の間は繰り返し処理を実行するため、結果としてArr1の要素の分だけ繰り返します。
続いて処理の部分ですが、IfステートメントにてArr1の要素の全ての値を3で割って余りが0かどうかを判定しています。判定の結果がTrueであれば、動的配列Arr2の要素数を変更して、該当したArr1の値をArr2に代入していきます。Arr1の値の内、3で割ったときに余りが0となる数値は、3、6、9ですのでArr2の配列にはこの3つの数値が代入されます。
Join関数をつかって結果を表示する
さいごに以下のコードで、配列Arr2の値を、Join関数でつなげてイミディエイトウィンドウに表示します。
Debug.Print “フィルタリングされた値は” & Join(Arr2, “,”) & “です。”
実行結果
フィルタリングされた値は3,6,9です。
Do Untilステートメントでデータのフィルタリング
Do Untilステートメントで、特定の範囲内のセルの内、指定の文字列が入力されているセルがいくつ該当したのか、また、どこのセルに該当文字が入力されているかを返すプログラムを作成します。
上記のリストから、”ABC”の文字列が該当するセルがいくつあるか。また、そのセルの座標(位置)を返します。
入力内容
Option Explicit
Sub Do_Until_sample()
Dim rng As Range
Dim cell As Range
Dim hit As Integer
Dim address() As Variant
' 対象の範囲を指定
Set rng = ThisWorkbook.Worksheets("Sheet6").Range("A1:A21")
' フィルタ条件
Dim filterValue As String
filterValue = "ABC"
' フィルタ処理
Set cell = rng.Columns(1).Cells(1)
Do Until cell.Value = ""
If cell.Value = filterValue Then
ReDim Preserve address(hit)
address(hit) = cell.address
hit = hit + 1
End If
Set cell = cell.Offset(1, 0)
Loop
Debug.Print ("ABCがヒットした数：" & hit)
Debug.Print ("セル番地は以下のとおり" & vbCrLf & Join(address, vbCrLf))
End Sub
変数の宣言
作業対象範囲を代入するためのオブジェクト(Range)型変数として、変数rngを準備します。また対象範囲の内の1つのセルを代入する変数cellとヒット数を代入する目的で変数hit・該当セルのアドレス格納するための配列としてaddressを宣言しています。
また、検索キーワードを代入するための変数は文字列(String)型にしてfilterValueという名称にしています。
変数に範囲や値を代入する
作業対象である.Range(“A1:A21”)を変数rngに、検索キーワードとして文字列を変数filterValueに”ABC“を代入します。さらに、変数cellにはrng.Columns(1).Cells(1)を代入するコードがありますが、これはA1からA21セルの1列目のひとつめのセルという意味です。
rngに代入されているセル範囲は、A1：A21であり1列しかありませんので、変数cellの最初の参照先は、A1セルです。
セルの値が空白になるまで繰り返す
Do Untilステートメントで、変数cellの値が空白になるまで繰り返す条件式です。
具体的には、A1から順番に処理を繰り返していくとA22セル以降は値が入っていないため、A21セルまで処理が繰り返されます。
そしてポイントとなるコードは「Set cell = cell.Offset(1, 0)」です。
これは、現在のセル座標を(0, 0)として、Offset(行数, 列数)と表記することで、行数や列数に記載した数値分シフトします。サンプルコードでは、1行下にシフトする処理をしています。これによって、A1→A2→A3…と処理を1セルずつシフトしていく仕組みを実装しています。
処理の内容
Ifステートメントをつかって条件判定をしています。cellが参照したセルに記入された値がfilterValueに代入された”ABC”と同じであるかを判定し、Trueであれば配列addressに該当したセルのアドレスを代入していきます。また、変数hitにも1を加算することで該当する件数を数えています。
Join関数をつかって結果を表示する
変数hitによって、文字列”ABC”と同じ文字列が入力されたセルの個数を返すことができます。さらに配列address内の値を、Join関数をつかってイミディエイトウィンドウに表示します。
実行結果
ABCがヒットした数：3
セル番地は以下のとおり
$A$3
$A$12
$A$20
A1セルからA21までの範囲で、文字列”ABC”が入力されているものは3件見つかり、該当のセル番地はA3セル、A12セル、A20セルとなることが結果として返ってきました。
VBAで繰り返し(ループ)マクロをつくる方法まとめ
今回は、VBAの繰り返し(ループ)処理について紹介しました。
こちらでは、これまで紹介した内容をまとめます。
VBAの繰り返し処理をするマクロを作成するためには、以下の方法がある
- For Nextステートメント
繰り返す回数を指定する書きかたで、繰り返す回数が明確である場合に利用する。 - For Eachステートメント
配列やオブジェクトなどの集合体のすべての要素に処理を繰り返す書きかた。 - Do Whileステートメント
条件を満たす間は処理を繰り返す書きかた。繰り返しをする回数が不明確な場合に利用する。 - Do Untilステートメント
条件を満たさない間は処理を繰り返す書きかた。繰り返しをする回数が不明確な場合に利用する。
繰り返し処理の重要性
- 繰り返し処理ができると実務で大きなメリットとなる
- 人力とは比べられないほどの大量のデータ処理が可能になる
- 条件分岐によりさらに複雑な処理もできるようになる
それぞれのステートメントのつかいどころ
1.For Nextステートメント
繰り返す回数が明確な場合に有効
2.For Eachステートメント
繰り返す対象がオブジェクトや配列である場合に有効
3.Do Whileステートメント
繰り返す回数が不明確で、条件による指定をしたい場合に有効。条件判定でTrueの間は繰り返しを継続します。Untilステートメントとは逆の関係です。
4.Do Untilステートメント
繰り返す回数が不明確で条件による指定をしたい場合に有効。条件判定でFalseの間は繰り返しを継続します。Whileステートメントとは逆の関係です。
変数のスコープ(有効範囲)について
繰り返し処理で利用する変数においてスコープ(有効範囲)は、宣言のしかたによってローカル変数やグローバル変数となるが、グローバル変数をあつかう場合は、他モジュールからの影響で変数に意図しない値が代入されないことや、可読性が低下することについて考慮する必要がある。
今回は、VBAの繰り返し(ループ)処理について紹介しました。繰り返し処理は、条件分岐と並んでプログラムにおける重要なポイントだと言えます。また、エクセル関数では実行できないものとなるので、VBAの真骨頂とも言える部分だと言えます。
繰り返し処理がつかえると、実務でもVBAを活用できる幅は多くなってきます。そのため、ぜひここで紹介した内容を文字通り”繰り返し”見ながら自身のスキルとして身につけていって頂けたら幸いです。
今回はここまで、さいごまでお読みいただきありがとうございます。
コダマのもりブログはにほんブログ村に登録しています。
今回の記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント