【Excel VBA】Rangeを変数に入れてコードを効率化する 〜オブジェクト変数〜

オブジェクト変数を使えるようになると、短く・見やすく・変更しやすいVBAコードを書くことができます。

マクロ初心者のうちにぜひ習得しておきたいテクニックです。

今回はセルの範囲をRangeオブジェクト変数に格納して、VBAコードの効率化を目指しましょう。

オブジェクト変数とは？

オブジェクト変数は、Excelを構成するさまざまなモノ(オブジェクト)を格納できる変数です。

通常の「変数」には文字列や数字を格納しますが、オブジェクト変数にはワークブック・ワークシート・セルの範囲などを格納できます。

たとえば、頻繁に登場するセルの範囲をオブジェクト変数に入れておけば、何度も呼び出して使えるので、VBAコードの効率化につながります。

Rangeオブジェクトの変数宣言と格納方法

Rangeオブジェクト（セルの範囲）をオブジェクト変数で扱う場合のVBAコード例です。

Rangeオブジェクトの変数宣言

   'オブジェクト変数の宣言
    Dim Rng As Range

通常の変数宣言に似ていますが、As 以降にはセル範囲を示すオブジェクト「Range」を指定します。

Rangeオブジェクト変数への格納方法

   'オブジェクト変数にセル範囲を格納
    Set Rng = ThisWorkbook.Worksheets("契約月数").Range("C2:C22")

オブジェクト変数にセル範囲を格納するときは、Set 変数名 ＝ を使います。

Rangeオブジェクトの変数格納時の注意

Rangeオブジェクトを変数に格納する際、ワークブック名・ワークシート名を指定しましょう！

構文には誤りがないのに、コピーやペーストでエラーが発生する原因になります。

   'オブジェクト変数にセル範囲を格納
   '↓ワークブック名・ワークシート名が不明で、セル範囲の指定が曖昧
    Set Rng = Range("C2:C22")

マクロ内でRangeを使う場合は、どのワークブックの、どのワークシートのRangeなのかを明確に指定するクセをつけましょう。

2つ以上のExcelファイルや、2枚以上のExcelワークシートを処理に使うようになったときに、陥りがちです。

Rangeオブジェクトのメソッド

Rangeオブジェクト変数は、Rangeのメソッドを利用することができます。

• Copyメソッド
• PasteSpecialメソッド

コピー・ペーストに関わる、この2つのメソッドは頻繁に使います。

VBAコーディング実践例｜Rangeオブジェクトを使って数式をコピペ＆値で貼り付け直すマクロ

たとえば、契約開始日と契約終了日をもとに契約月数（満月数）を算出するマクロを書いてみます。

マクロの実行前とマクロの実行後のイメージはこんな感じ。

VBAコードの実践例

契約開始日と契約終了日をもとに契約月数（満月数）を算出するマクロの実践例です。

Worksheet（ワークシート）とRange（セルの範囲）のオブジェクト変数を使って、マクロの効率化を図っています。

標準モジュールにコピペして使えます。

このVBAコードの解説編 >> 【Excel VBA】数式をコピペ＆値で貼り付け直すマクロ 〜解説編〜

'契約月数(満月数)を求める数式をコピぺ＆値で貼り付け
Sub CopyPaste_FormulaToValue()
    
    '作業するワークシート(Worksheet)をオブジェクト変数に格納
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("契約月数")
    
    'コピペする範囲(Range)をオブジェクト変数に格納
    Dim Rng As Range
    Set Rng = Ws.Range("C2:C22")
    
    '契約月数(満月数)を求める数式
    Dim srcFml As String
    srcFml = "=DATEDIF(A2, B2, " & Chr(34) & "M" & Chr(34) & ")"
    
    'C列2行目に数式をセット
    Ws.Range("C2") = srcFml
    
    '対象範囲の全体に数式をコピペ
    Ws.Range("C2").Copy
    Rng.PasteSpecial Paste:=xlPasteFormulas
    
    '対象範囲の全体を値で貼り付け
    Rng.Copy
    Rng.PasteSpecial Paste:=xlPasteValues
    
    '貼り付け範囲の点線を解除
    Application.CutCopyMode = False
    
    'セルC1を選択
    Ws.Range("C1").Select

End Sub

オブジェクト変数を使用しない場合のVBAコード例

Worksheet（ワークシート）とRange（セルの範囲）のオブジェクト変数を使わずに書いたマクロです。

'オブジェクト変数不使用： 契約月数(満月数)を求める数式をコピぺ＆値で貼り付け
Sub CopyPaste_FormulaToValue_nonObject()
        
    '契約月数(満月数)を求める数式
    Dim srcFml As String
    srcFml = "=DATEDIF(A2, B2, " & Chr(34) & "M" & Chr(34) & ")"
    
    'C列2行目に数式をセット
    ThisWorkbook.Worksheets("契約月数").Range("C2") = srcFml
    
    '対象範囲の全体に数式をコピペ
    ThisWorkbook.Worksheets("契約月数").Range("C2").Copy
    ThisWorkbook.Worksheets("契約月数").Range("C2:C22").PasteSpecial Paste:=xlPasteFormulas
    
    '対象範囲の全体を値で貼り付け
    ThisWorkbook.Worksheets("契約月数").Range("C2:C22").Copy
    ThisWorkbook.Worksheets("契約月数").Range("C2:C22").PasteSpecial Paste:=xlPasteValues
    
    '貼り付け範囲の点線を解除
    Application.CutCopyMode = False
    
    'セルC1を選択
    ThisWorkbook.Worksheets("契約月数").Range("C1").Select

End Sub

オブジェクト変数を使用しなくても、同じ動きをします。

しかし、ワークシート名とセルの範囲が繰り返し出てくるのが目障り。

シート名やセルの範囲が変わってしまうとメンテナンスが大変。

効率化とは程遠いVBAコーディング例です。

まとめ

今回はRange（セル範囲）をオブジェクト変数に格納して使う方法を解説しました。

オブジェクト変数には、Workbook（ワークブック）、Worksheet（ワークシート）なども入れられるので、マクロで実現したい処理が複雑になるほど、重要性を帯びます。

オブジェクト変数をうまく使って、VBAコードを効率化しましょう！