マクロ作成後に表位置がずれた場合の対処

公開日：2013年5月以前　最終更新日：2020-03-28

マクロ作成後に表位置がずれた場合の対処

マクロVBAの最も不便なところは、シートでセルをずらしてもセル参照が自動で変更されない事です。

どういう事かと言うと、ワークシート関数なら、セル位置をずらすと計算式が自動で変更されます。
しかし、マクロVBAの記述は自動で変更されません。
B2セルをシートでずらしても、Range("B2")もCells(2, 2)も変わりません。

もちろん、その可能性がある事が最初から解っていれば、そのように記述することも可能です。
要所位置を名前定義しておいたりテーブル化しておくことで、相対位置だけでVBAを記述し自動で対応することはできます。

しかし、なかなか全てのマクロVBAでそのような対処をすることも難しく、そのような対処をしていないマクロVBAで、
一旦完成した後に、１行目は余白行にしたいとか、A列は空いていた方が見やすいとか・・・
そうなると、マクロの修正が大変なことがあります。

Cells(i, j)

このiやjに関する記述を全て修正しますか？
列数がConstやEnumにしてあったとしても、その部分の修正は必要です。

と言う事で、単純に表全体がずれた場合の、ちょっとずるい対処方法です。

表位置がずれた場合の対処

A1から作成されている表に対する処理として、


  
    
      
      Dim i As Long

      Dim LastRow As Long

      With Worksheets("Sheet1")

      　　LastRow = .Cells(Rows.Count, 1).End(xlUp).Row

      　　For i = 2 To LastRow

      　　　　.Cells(i, 3) = .Cells(i, 1) + .Cells(i, 2)

      　　　　'・・・いろいろな処理・・・

      　　Next

      End With

まあ、このようなVBAコードがあったとして、
１行目を空けたい、ついでに、A列も空けたい。
そんな場合は、どうしますか？

普通なら、


  
    
      
      Dim i As Long

      Dim LastRow As Long

      With Worksheets("Sheet1")

      　　LastRow = .Cells(Rows.Count, 2).End(xlUp).Row

      　　For i = 3 To LastRow

      　　　　.Cells(i, 4) = .Cells(i, 2) + .Cells(i, 3)

      　　　　'・・・いろいろな処理・・・

      　　Next

      End With

上の赤太字が、修正対象部分になりますね。

たったこれだけのコードでも、4箇所も修正する必要があります。
もちろん、「・・・いろいろな処理・・・」この中が大量に修正が必要になります。

そこで、以下のような修正方法をとります。


  
    
      
      Dim i As Long

      Dim LastRow As Long

      With Worksheets("Sheet1").Cells(2, 2)

      　　LastRow = .Cells(Rows.Count - 1, 2).End(xlUp).Row

      　　For i = 2 To LastRow - 1

      　　　　.Cells(i, 3) = .Cells(i, 1) + .Cells(i, 2)

      　　　　'・・・いろいろな処理・・・

      　　Next

      End With

※.Cells(2, 2)これはRange("B2")でも良い。

上の赤太字だけ修正することで、その他の部分、、
「・・・いろいろな処理・・・」
本来多くの変更が必要なこの部分については手を付けなくても良くなります。
もちろん、当然コード内容によりますが、ほとんど修正は必要なくなります。

かなり無理やりではありますが、なぜ、こんな修正方法で良いのか・・・
理解できますか？

With Worksheets("Sheet1")

このWithの中での、
.Cells(行,列)
この行列は、A1セルを起点とした相対位置の指定になります。
.Cells(2, 2)は、2行2列目のB2になります。

Worksheets("Sheet1").Cells(2, 2)

このWithの中での、
.Cells(行,列)
この行列は、Cells(2, 2)を起点とした相対位置の指定になります。
.
CellsはRangeオブジェクトであり、RangeオブジェクトにはCellsプロパティがあります。
このCellsプロパティの引数は、元のRangeオブジェクトの先頭（左上）セルからの相対位置指定になっています。
つまり、
Cells(2, 2).Cells(2, 2)
RAnge("B2").Cells(2, 2)
これらは、B2セルを起点、つまりB2を(1,1)としての相対位置なのでC3セルになります。

完成したVBAコードは、あまり良いVBAとは言えませんが、修正ミスによる誤動作で悩むよりはマシでしょうか。
もちろん、最初から表位置が変更になる事を考慮して、コーディングしておけば問題ないのですが、
なかなか毎回そのようにはいかない事も多いでしょう。
いざと言う時の、裏ワザとして覚えておくとよいと思います。

表位置がずれることを想定したVBA


  
    
      Dim ws As Worksheet

Dim startCell As Range

Dim i As Long

      Dim LastRow As Long


      Set ws = Worksheets("Sheet1")

      Set startCell = ws.Range("C3")


With startCell

　　LastRow = ws.Cells(ws.Rows.Count, .Column).End(xlUp).Row

　　For i = 2 To LastRow - .Row + 1

      　　　　.Cells(i, 3) = .Cells(i, 1) + .Cells(i, 2)

　　　　'・・・いろいろな処理・・・

　　Next

End With


  
    
      Dim ws As Worksheet

Dim myRange As Range

Dim i As Long

      Dim LastRow As Long


      Set ws = Worksheets("Sheet1")

      Set myRange = ws.Range("C3").CurrentRegion


With myRange

　　LastRow = .Rows.Count

　　For i = 2 To LastRow

　　　　.Item(i, 3) = .Item(i, 1) + .Item(i, 2)

　　　　'・・・いろいろな処理・・・

　　Next

End With

C3セルを名前定義してあればなお良いでしょう。

この2つのVBAは、全く同じことをしています。
参考として、2通りの書き方をしてみました。
工夫をすれば、もっといろいろな書き方ができます。

このような書き方をしておけば起点セルを変更するだけで対処可能です。
.Cells(...)
.Item(...)
CellsもItemも基のRangeオブジェクトの先頭（左上）セルからの相対位置指定になります。
Offsetを使っても良いです。

・Offsetプロパティの構文・Offsetの使用例・Offsetの注意点・Offsetのまとめ

ですが、好みの問題ではありますが、OffsetよりCellsかItemの方が良いのではと思っています。

テーブルならずれても問題ありません


  
    
      Sub sample()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim tbl As ListObject

      　　Set tbl = ws.ListObjects("テーブル1")

　　Dim col As Long

　　col = tbl.ListColumns("列3").Index

      　　Dim myRange As Range

　　For Each myRange In tbl.DataBodyRange.Columns(col)

      　　　　myRange = "[@列1]+[@列2]"

　　Next

End Sub

テーブルは、表範囲全体に名前定義されている状態なので、表位置がずれてもVBAの変更は基本的に発生しません。

・ListObjects コレクション・ListObject オブジェクト・テーブル操作のVBAコード

テーブルにすることで、いくつかの制限事項は発生しますが、
表位置が度々ずれたりするのであれば、テーブルにしておくことでVBAの保守性はとても良くなります。

同じテーマ「マクロVBA技術解説」の記事

マクロ作成後に表位置がずれた場合の対処
ExecuteExcel4Macroについて
 「Excel 4.0 マクロ」の使い方
 再帰呼出しについて（再帰プロシージャー）
フィボナッチ数列（再帰呼び出し）
文字列でのセル参照と文字列の計算式について（Evaluate,INDIRECT）
リボンを非表示、2003以前ならメニューを非表示
 印刷ページ設定の余白をセンチで指定する（CentimetersToPoints）
文字列としてのプロシージャー名を起動する方法（Run,OnTime）
ドキュメントの作成者を取得（GetObject,BuiltinDocumentProperties）
画像サイズ（横x縦）の取得について

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
マクロ作成後に表位置がずれた場合の対処

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA技術解説
マクロ作成後に表位置がずれた場合の対処