【Excel VBA】よく使う覚えておきたいOffsetの使用例をざっくり紹介【Offset】

今回はOffsetの使用例をざっくり紹介したいと思います。

マクロを作る上でほぼ必須と言えるくらい使うので、是非参考にしていただければと思います。

そもそもOffsetとは何ぞや
Offsetの使用例
1. 繰り返し処理と合わせる方法
  1. For文を使用する場合の書き方
  2. Do Loop文を使用する場合の書き方
2. ダブルクリックイベントと合わせる方法
まとめ

そもそもOffsetとは何ぞや

そもそも

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セルに文字列が順に入力されます。

対象のセル（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セルに実行した日付から一日刻みにセットされていきます。

こんな感じで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文を使用した時と同じです。

Do Loop文を使う上で注意しておきたいことが、無限ループにならないようにすることです。今回のサンプルコードで言うとカウンタ変数のiの値に加算する処理を追加することで処理回数を有限にし、無限ループにならないようにしてあります。

i = i + 1

これを書き忘れると一生A1セルに日付が入り続けることになってしまうので気を付けましょう。

なお、Do Loopについては過去に紹介・解説していますのでこちらも併せてご覧ください！

【Excel VBA】忘れがちなDo WhileとDo Untilの使い分け【Do～Loop】

今回はDo WhileとDo Untilの使い分けについて紹介したいと思います。私自身よく「どっち...

ダブルクリックイベントと合わせる方法

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

以下のようなリストを使用して動作説明をします。