エクセルで色々なものを作っていると必ずと言っていいほど問題になるのが、セルを挿入したり削除した時のファイルやプログラムが正常に動かなくなる事象です。
これはVBA側でセルの指定を
Range(“B2”)やCells(2,2)などのように指定していると、例えば1列目に列を挿入した場合にずれてしまいます。
↓B列を選択して挿入すると「開始」のセルがB2⇒C2になってしまいます。
この場合、「開始」のセルを参照しようとしてVBAでRange(“B2”)やCells(2,2)として参照しているとずれたあとのB2を参照してしまいます。
これでは正常に動きません。
そこでこれらを回避する方法を紹介します。
セルの名前を指定する
セルにはデフォルトで座標名が入っています。
先ほどのB2などがそうです。
これは座標を持っているのだけなのですが、セルの挿入によってそのセルの座標名が変わるという仕様があります。
セル同士の参照などの時は、セルの挿入によって変わった座標名はその分変化してくれて便利なのですがVBAからの参照は自動でずれてくれません。
VBAからの参照ではセル挿入による座標名の変動がネックになるということです。
そこでセルの名称を指定するという方法を取ればセルの挿入によるずれを解消することが出来ます。
↓上記の赤枠は座標名が入っていますが、クリックすることによって変更が可能です。
「start」と入力してenterを押しましょう。（enterを押さないと反映されないので注意）
先ほどのようにB列に列の挿入を行うと
セルがずれたのにセル名は変わっていません。
これをVBAでRange(“start”)として参照すればセルの挿入でずれた場合でも参照対象が変わりません。
ただこちらの難点はセル全てにセル名を設定するのは大変ということです。
あくまでポイントとなるセルだけにセル名を指定して、そこを基準とした座標で作成していくこととなります。
例えば
row = Range("start").row
col = Range("start").Column
area = Range(Cells(row,col),Cells(row + 3,col + 3)のようにして基準からいくつのような指定方法なら基準セルさえ削除されなければセルの挿入でずれた場合も問題ありません。
また、セル名を指定したセルを削除してしまうと使えなくなってしまいます。
この辺りに注意して作成できる場合は有用な方法です。
絶対セルの挿入をしないシートを１つ作る
これはセルを挿入した時に自動で座標名が変わるという仕様を逆に利用する方法です。
先ほどのブックに新たにシートを作成します。
そこに「=Sheet1!B2」と参照をするセルを作成します。
そして、sheet1のB列に列挿入を行うと
計算式が「=Sheet1!C2」に変わります。
そして、sheet1を参照するのではなくsheet2のA１を参照すればいいということです。
ただこちらも対象のセルを消してしまった場合は＃REFになってエラーとなります。
また、シートが１枚増えるというデメリットもあります。
入力シートとデータシートを分ける仕様にする場合には、あとから追加や削除をしたいことが多くなりますので向いています。
まとめ
どちらにしてもセルのずれはプログラム的に問題になることがあります。
また、特に削除に関しては極力行わないようにするしかありません。プログラム的には非表示などで対応するのがベターです。
出来ればそもそも挿入や削除が必要ないシステムにするのがお勧めです。
コメント