photo credit: Rolling-Coca-Blue-Cola via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルにおいて「全ての行について何かしらの処理をしたい」というニーズがあったとき、エクセルVBAでFor～Next文を使うのが基本中の基本です。
ですが、ワークシートの運用上、行の挿入や行の追加が発生すると
- 開始行が変わってしまったり
- 行数（最終行）が変わってしまったり
して、うまくFor～Next文を使ったプログラムが動かなくなることがあります。
今回は、その回避方法をいくつかお伝えしたいと思います。
エクセルVBAでワークシートの開始行や行数の変更があっても繰り返し処理がちゃんと動くプログラムの作り方です。
特にセルに名前をつける方法は、なぜかあまり知られていないような気がしますがオススメです。
では、行ってみましょう。
お題：For～Nextの開始と終了の変更を不要にしたい
今回はお題としてこちらのシートを使いたいと思います。
ホームページに記載する会社概要についてまとめた表です。
こちらの記事で、このシートの情報をもとにHTML文を作成する方法をお伝えしています。
少し省略していますが以下のようなプログラムです。
Function createAboutSection(ByVal ws As Object) As String
'初期処理
Dim i As Long, j As Long
For i = 5 To 9 '5～9行目まで繰り返し
'5～9行目までのそれぞれの行に行う処理
Next i
'後処理
End Function
ですが、例えばこのようにシートに変更があったとすると
処理をする対象が6～11行目になりますから、For～Next文の開始と終了の値を変更しなくてはならなくなります。
いちいちVBEを開いて修正するのは面倒ですし、現実的ではありませんので、その修正をしなくても済むように元のプログラムを直していきたいと思います。
セルに名前をつけて開始行を都度求める
ではまず開始行に変更があっても対応できるようにしていきます。
セルに名前をつけるという方法を使います。
「項目名」と入力されているA4セルに「目印」としての名前をつけたいと思います。
以下画像で示した入力欄に「A4」と入力されていますが「項目名」と上書き入力して Enter をします。
この入力欄は名前ボックスと言いまして、ここに任意の文字を入力して Enter をするとセルに名前をつけることができます。
このセルが他の行列の挿入や削除により移動することがあったとしても、つけた名前も引き連られます。
そしてエクセルVBAでは、以下のように名前を指定してセルを取得することができます。
Worksheetオブジェクト.Range(セルの名前)
ですから、今回の場合は
Function createAboutSection(ByVal ws As Object) As String
'初期処理
Dim startRow as Long
startRow = ws.Range("項目名").Row + 1
Dim i As Long, j As Long
For i = startRow To 9 '「項目名」の次の行～9行目まで繰り返し
'それぞれの行に行う処理
Next i
'後処理
End Function
などとすることで、セルの名前からセルの行数を求め、それにより最終行を特定することができます。
最終行の位置を都度求める
では次に行数に変化があった場合でも対応できるようにしていきたいと思います。
この方法として、二つほど方法があります。
まず一つ目は、都度最終行の行数を取得する方法です。
こちらの記事でも紹介していますが
Worksheetオブジェクト.Cells(Rows.Count, 列数).End(xlUp).Row
という書き方です。
今回の場合は
Function createAboutSection(ByVal ws As Object) As String
'初期処理
Dim startRow as Long
startRow = ws.Range("項目名").Row + 1
Dim endRow as Long
endRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Dim i As Long, j As Long
For i = startRow To endRow '「項目名」の次の行～最終行目まで繰り返し
'それぞれの行に行う処理
Next i
'後処理
End Function
などとすればOKです。
Do While文で行数に関わらず繰り返すようにする
もう一つの方法は、For～Next文ではなくて、Do While～Loopを使う方法です。
書き方はこちらです。
Do While 条件文
（繰り返したい処理）
Loop
今回の場合、条件として「1列目のセルに値が入力されている間」とすることで、最終行まで処理を繰り返して、最終行を超えたら繰り返しを抜けてくれるようになります。
Function createAboutSection(ByVal ws As Object) As String
'初期処理
Dim startRow as Long
startRow = ws.Range("項目名").Row + 1
Dim i As Long, j As Long
i = startRow
Do While ws.Cells(i, 1).Value <> "" '「項目名」の次の行～最終行目まで繰り返し
'それぞれの行に行う処理
i=i+1
Loop
'後処理
End Function
Do Whileを使う場合は二つほど重要な注意点があります。
一つ目は、繰り返しの判定で使う列のセルに空欄を入れないということです。空欄があるとその時点で繰り返しから抜けて二度と戻って来ません。
二つ目は、13行目のi=i+1を必ず入れるということです。While Doはイテレータ（今回でいうと変数i）を使わない繰り返しですので、イテレータの増加は別途命令しないといけません。これを忘れると永久に繰り返しから抜け出さなくなります。
以上、二点に気を付けて頂ければと思います。
詳細はこちらの記事もご覧ください。
個人的にはDo While～Loopのほうが変数も少なくて済みますので好きです。
まとめ
以上、エクセルVBAでワークシートの開始行や行数の変更があっても繰り返し処理がちゃんと動くプログラムの作り方についてお伝えしました。
エクセルVBAは多くの場合、業務の変更に合わせてプログラムを修正をする必要がありますね。
逆に言えば、業務フローの設計や最適化といった面も学べるという側面もあります。そのような部分も気にしながら学習されると、より一層の広さ・深さでのスキルアップが図れると思いますよ。
次回は、今回お題にしたHTML作成のVBAを進めていきたいと思います。
どうぞお楽しみに！
