【エクセルVBA】シートの開始行や行数の変更があってもちゃんと動く繰り返し文の書き方


みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルにおいて「全ての行について何かしらの処理をしたい」というニーズがあったとき、エクセルVBAでFor~Next文を使うのが基本中の基本です。

ですが、ワークシートの運用上、行の挿入や行の追加が発生する

  • 開始行が変わってしまったり
  • 行数(最終行)が変わってしまったり

して、うまくFor~Next文を使ったプログラムが動かなくなることがあります。

今回は、その回避方法をいくつかお伝えしたいと思います。

エクセルVBAでワークシートの開始行や行数の変更があっても繰り返し処理がちゃんと動くプログラムの作り方です。

特にセルに名前をつける方法は、なぜかあまり知られていないような気がしますがオススメです。

では、行ってみましょう。

スポンサーリンク

お題:For~Nextの開始と終了の変更を不要にしたい

今回はお題としてこちらのシートを使いたいと思います。

エクセルに入力した会社概要のデータ

ホームページに記載する会社概要についてまとめた表です。

こちらの記事で、このシートの情報をもとにHTML文を作成する方法をお伝えしています。

エクセル入力情報からWebサイトの会社概要のHTML文を生成するVBAプログラム
エクセルの入力情報をもとに自動で企業サイト向けトップページのHTMLを生成していきます。今回は会社概要をテーブルで組んでいる箇所のHTMLを生成するVBAプログラムの作成を進めていきます。

少し省略していますが以下のようなプログラムです。

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

などとすることで、セルの名前からセルの行数を求め、それにより最終行を特定することができます。

最終行の位置を都度求める

では次に行数に変化があった場合でも対応できるようにしていきたいと思います。

この方法として、二つほど方法があります。

まず一つ目は、都度最終行の行数を取得する方法です。

こちらの記事でも紹介していますが

【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
今回は行数をカウントする、行を隠す、などの「行を取り扱うテクニック」を紹介しています。いずれもデータや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、知っておいて損はありませんよ。

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)を使わない繰り返しですので、イテレータの増加は別途命令しないといけません。これを忘れると永久に繰り返しから抜け出さなくなります。

以上、二点に気を付けて頂ければと思います。

詳細はこちらの記事もご覧ください。

【エクセルVBA入門】Do While~Loop文で条件を満たす間繰り返し
エクセルVBAを使ってバラバラの経費精算書のデータを集約するシリーズです。今回はDo While~Loop文を使って条件を満たす間、任意の処理を繰り返すプログラムの書き方をマスターしていきます。

個人的にはDo While~Loopのほうが変数も少なくて済みますので好きです。

まとめ

以上、エクセルVBAでワークシートの開始行や行数の変更があっても繰り返し処理がちゃんと動くプログラムの作り方についてお伝えしました。

エクセルVBAは多くの場合、業務の変更に合わせてプログラムを修正をする必要がありますね。

逆に言えば、業務フローの設計や最適化といった面も学べるという側面もあります。そのような部分も気にしながら学習されると、より一層の広さ・深さでのスキルアップが図れると思いますよ。

次回は、今回お題にしたHTML作成のVBAを進めていきたいと思います。

カラム数を指定してBootstrapのグリッドシステムを使ったHTMLを生成するエクセルVBA
Boostrapを使ったHTMLコードをエクセルVBAで自動生成していきます。今回はカラム数を指定することで、グリッドシステムを使った部分のHTMLソースを生成するVBAプログラムを作っていきます。

どうぞお楽しみに!

連載目次:エクセルの入力情報からVBAでHTML文を自動生成

企業向けのWebサイトを作る際、エクセルの入力情報を元に自動でHTMLコードを生成できれば楽ですよね。本シリーズではBootstrapを使ったシングルページを想定してコードを生成するVBAプログラムを作っていきます。
  1. エクセルVBAでWordPress投稿用HTMLソースを自動で生成する
  2. エクセルVBAでインデントや改行を入れながらHTML文を追加していく便利関数を作る
  3. エクセル入力情報からWebサイトの会社概要のHTML文を生成するVBAプログラム
  4. 【エクセルVBA】シートの開始行や行数の変更があってもちゃんと動く繰り返し文の書き方
  5. カラム数を指定してBootstrapのグリッドシステムを使ったHTMLを生成するエクセルVBA

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