今回はOffsetの使用例をざっくり紹介したいと思います。
マクロを作る上でほぼ必須と言えるくらい使うので、是非参考にしていただければと思います。
そもそもOffsetとは何ぞや
そもそも
![](https://masashi-blog418.com/wp-content/uploads/2022/09/b0261.png)
Offsetってなんのこっちゃ?
ってなる方もいると思います。
先にOffsetについてざっくり説明すると「指定した分だけセルを移動する」になります。
以下、サンプルになります。実際にコピペして確認してみてください。
※実行すると現在開いているシートのA1~A6セルにデータが入力されるので、実行しても問題ないシートを開いた状態で実行するのをお勧めします!
Sub haribona_offset()
With ThisWorkbook.ActiveSheet
.Range("A1").Offset(0, 0) = "このように"
.Range("A1").Offset(1, 0) = "Offsetを使うと"
.Range("A1").Offset(2, 0) = "指定したセルに"
.Range("A1").Offset(3, 0) = "データを入れることが"
.Range("A1").Offset(4, 0) = "可能です"
.Range("A1").Offset(5, 0) = "はりぼなブログ"
End With
End Sub
実行するとA1~A6セルに文字列が順に入力されます。
![そもそもOffsetとは何ぞや_1](https://masashi-blog418.com/wp-content/uploads/2023/01/image.png)
対象のセル(RangeやCellsなど)の後ろに.Offsetと書きカッコ内に移動したい分の数字を指定すればOKです。
以下数字の指定例になります。実際に実行してみるとイメージが掴みやすいと思います。
'A1セルから行方向に1つ移動(A2セル)
Range("A1").Offset(1, 0).Select
'A1セルから列方向に1つ移動(B1セル)
Range("A1").Offset(0, 1).Select
'A1セルから行方向に1つ、列方向に1つ移動(B2セル)
Range("A1").Offset(1, 1).Select
'C3セルから行方向に1つ戻る(C2セル)
Range("C3").Offset(-1, 0).Select
'C3セルから列方向に1つ戻る(B3セル)
Range("C3").Offset(0, -1).Select
'C3セルから行方向に1つ、列方向に1つ戻る(B2セル)
Range("C3").Offset(-1, -1).Select
コードとコメントを読むと何となくわかる方もいると思うのですが、
引数1(Offset(【ここ!】,#))が行方向(上 or 下方向)で、
Range("A1").Offset(1, 0).Select '行方向(1になってるところ)
引数2(Offset(#,【ここ!】))が列方向(右 or 左方向)に移動します。
Range("A1").Offset(0, 1).Select '列方向(1になってるところ)
表にまとめると以下の通りです。
引数 | プラス値(1~) | マイナス値(-1~) |
---|---|---|
引数1 | 下方向 | 上方向 |
引数2 | 右方向 | 左方向 |
このようにOffsetを使用する際は、
- セルを下方向もしくは右方向に移動させたい→引数1をプラス値で指定
- セルを上方向もしくは左方向に移動させたい→引数2をマイナス値で指定
このポイントを押さえておけばOKです。
Offsetの使用例
続いてOffsetの簡単な使用例を2つ紹介します。マクロを作成する上でよく使う方法なので、是非参考にしていただければと思います。
繰り返し処理と合わせる方法
1つ目は繰り返し処理と合わせる方法です。個人的に一番よく使う方法で、データの一括入力やチェック等幅広く活用することができ、主にFor文やDo Loop(While,Until)文と合わせて使用します。
For文を使用する場合の書き方
まずFor文を使った書き方になります。サンプルコードは以下の通りです。
Sub offset_For()
Dim i As Long
Application.ScreenUpdating = False
For i = 0 To 10
Range("A1").Offset(i, 0) = DateAdd("d", i, Now)
Next i
Application.ScreenUpdating = True
End Sub
実行すると、A1~A10セルに実行した日付から一日刻みにセットされていきます。
![](https://masashi-blog418.com/wp-content/uploads/2023/02/image-2.png)
こんな感じでOffsetの引数の値をカウンタにしてあげると繰り返し処理と同期させることができるわけです。
また、サンプルコードだとiの値を0~10としている為日付が11回入力されていますが、Toの後ろの数字(繰り返し回数)を変えれば、その回数分繰り返し処理を行うことができます。
For i = 0 To 20 '21回繰り返す
Do Loop文を使用する場合の書き方
次にDo Loop文を使った書き方になります。
動作自体に差はありませんが、Do Loop文を使用するので若干書き方が変わります。サンプルコードは以下の通りです。
Sub offset_DoLoop()
Dim i As Long
Application.ScreenUpdating = False
i = 0
Do Until i > 10
Range("A1").Offset(i, 0) = DateAdd("d", i, Now)
i = i + 1
Loop
Application.ScreenUpdating = True
End Sub
実行結果はFor文を使用した時と同じです。
![](https://masashi-blog418.com/wp-content/uploads/2023/02/image-2.png)
Do Loop文を使う上で注意しておきたいことが、無限ループにならないようにすることです。今回のサンプルコードで言うとカウンタ変数のiの値に加算する処理を追加することで処理回数を有限にし、無限ループにならないようにしてあります。
i = i + 1
これを書き忘れると一生A1セルに日付が入り続けることになってしまうので気を付けましょう。
なお、Do Loopについては過去に紹介・解説していますのでこちらも併せてご覧ください!
ダブルクリックイベントと合わせる方法
2つ目はダブルクリックイベントと合わせる方法です。イメージとしては、
- セルをダブルクリックする
- ダブルクリックしたセルを基準にOffsetを使って他のセルを取得する
といった感じです。
私の場合、台帳やリストにおいてダブルクリックした行のセルに対して何かしらの処理を行う。という時によく使っています。
サンプルコードは以下の通りです。
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
Cancel = True
If Not (Intersect(target, Range("A:A")) Is Nothing) And _
target.Value = "未達" Then
target.Value = "完了"
target.Offset(0, 1) = Now
target.Offset(0, 2).Font.Strikethrough = True
End If
End Sub
以下のようなリストを使用して動作説明をします。
![Offsetの使用例_ダブルクリックイベントと合わせる方法_1](https://masashi-blog418.com/wp-content/uploads/2023/02/image-1024x279.png)
動作はいたってシンプルで、A列の進捗が「未達」になっているセルをダブルクリックします。
![Offsetの使用例_ダブルクリックイベントと合わせる方法_2](https://masashi-blog418.com/wp-content/uploads/2023/02/image-1-1024x304.png)
すると、ダブルクリックしたセル(A列)の値が「完了」に、B列にダブルクリックした時の日時が入り、C列のやること欄に取り消し線が引かれます。いずれもダブルクリックしたセル(変数target)が基準になって処理が行われています。
![Offsetの使用例_ダブルクリックイベントと合わせる方法_3](https://masashi-blog418.com/wp-content/uploads/2023/02/image-3-1024x139.png)
このようにダブルクリックしたセルを基準に処理を行いたい場合は、引数であるtargetを使うとできるようになります。是非覚えておきましょう!
また、ダブルクリックのシートイベントに関する記事を過去に投稿しておりますので、こちらも併せてご覧ください!
まとめ
今回はOffsetの使い方についてざっくり紹介しました。
Offsetを使用する時は、
- セルを下方向もしくは右方向に移動させたい→引数1をプラス値で指定
- セルを上方向もしくは左方向に移動させたい→引数2をマイナス値で指定
- 繰り返し処理と同期させたい時はカウンタを引数にする
- ダブルクリックイベントを使う時は引数targetを基準にする
上記4点に注意しましょう!
コメント