Excelマクロ：行・列を挿入してもプログラム修正不要のちょっとしたテクニック

Excelマクロでよくやってしまうのが、プログラミングした後に入力セルの位置を変えてしまうという行為です。プログラムが完成した後に、表のデザインを変えるなどで行挿入や削除などをしてしまうパターンはよくある話です。

例えば、こんなプログラムを組んだとして、下の図のように最初はうまく動きます。

Sub 入力()
  Cells(3, 3) = 98
  Cells(4, 3) = 88
  Cells(5, 3) = 92
  Cells(6, 3) = 85
End Sub
 

 

 

ところが、その後こんな風に行を挿入してしまった場合、

 

そのまま実行してしまうと、入力箇所がズレてしまいカオスな状態になった経験は結構あるんじゃないでしょうか。

 

上の例は簡単な表ですが、何行も何列もあるような会社で使う資料でこんなことをやってしまうと、目もあてられない状況になり泣きたくなりますね。

今回は、こんな状態にならないようあらかじめプログラムを工夫しておきましょうというお話です。

[ad#top-1]

 

まず基準となるセルに任意の名前を付ける

プログラムの前にセルに好きな名前を付けます。通常、セルには「C3」のようなアドレスと同じ名前が付与されています。

C3セルを選択すると、「注目」とある個所に「C3」と現れますがここがそれです。

 

これを好きな名前に変えることができます。例えば「開始」という名前に変えておきましょう。

 

そうすると、「C3」セルは以下のような方法で参照や入力が可能になります。これがまず最初のテクニック。

Range("開始") = 98

 

プログラムで基準セルから行列Noを読み取る

このままだと、入力すべきセルにすべて名前を変える必要があるのですが、それはさすがに大変なので、名前を変えたセルを「基準」に行Noと列Noを読み取ります。

で、そこから計算で各セルを特定することができます。

以下のようにRowプロパティとColumnプロパティから行列番号を読み取って、そこを基準に計算で入力すべきセルを特定しています。

Sub 入力()
  r = Range("開始").Row
  c = Range("開始").Column

  Cells(r + 0, c) = 98
  Cells(r + 1, c) = 88
  Cells(r + 2, c) = 92
  Cells(r + 3, c) = 85
End Sub
 

 

これであれば、セル位置変更があってもプログラムを修正する必要はありません。

 

Excelマクロ特有の問題ですが、プログラムを少し工夫するだけで後々の保守工数を大きく減らすことができるのでこの方法はおすすめです。

 

[ad#ad-1]

 - Excel, VBA