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

数式をコピペ＆値で貼り付け直すマクロを解説します。

このマクロには、実践的なオブジェクト変数の使い方も含んでいます。

Worksheeオブジェクトの基本編 >> こちら

Rangeオブジェクトの基本編 >> こちら

お題：契約開始日と契約終了日をもとに契約月数（満月数）を算出する

次のようなワークシートがあるとします。

• ワークシート名 [契約月数]
• A列 [契約開始日]
• B列 [契約終了日]
• C列 [契約月数(満月数)]

A列・B列に入力されている日付を利用して、C列に契約月数(満月数)を求めます。

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

VBAコードの実例

こちらの記事に書いた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コードの解説

VBAコーディングの流れ

オブジェクトを変数に格納

    '作業するワークシート(Worksheet)をオブジェクト変数に格納
    Dim Ws As Worksheet
    Set Ws = ThisWorkbook.Worksheets("契約月数")
    
    'コピペする範囲(Range)をオブジェクト変数に格納
    Dim Rng As Range
    Set Rng = Ws.Range("C2:C22")

ワークシートとセルの範囲をオブジェクト変数に格納しています。

• Worksheetのオブジェクト変数 Ws <– [契約月数]シート
• Rangeのオブジェクト変数 Rng <– セルC2〜C22の範囲

Worksheetオブジェクト・Rangeオブジェクトの基本について詳しく知りたい方は、次の記事をどうぞ。

Worksheeオブジェクトの基本編 >> こちら

Rangeオブジェクトの基本編 >> こちら

数式を変数に格納＆セルにセット

    '契約月数(満月数)を求める数式
    Dim srcFml As String
    srcFml = "=DATEDIF(A2, B2, " & Chr(34) & "M" & Chr(34) & ")"

数式を変数に格納するときは、文字列の先頭にあらかじめ = を付けておきます。

そうすることで、セルに格納された文字列は数式として認識されます。

途中に挟んでいるChr(34)は、ダブルクォーテーションの代わりです。

セルに格納されると、このような数式になります。

=DATEDIF(A2, B2, “M”)

契約月数(満月数)の算出には、Excel標準のDATEDIF関数を使用します。

    'C列2行目に数式をセット
    Ws.Range("C2") = srcFml

ワークシートWsを指定してC列2行目のセルに、変数に格納した数式を代入します。

対象範囲に数式をコピペ

    '対象範囲の全体に数式をコピペ
    Ws.Range("C2").Copy
    Rng.PasteSpecial Paste:=xlPasteFormulas

ワークシートWsのセルC2をコピーします。

コピーした数式を、範囲Rngに「形式を選択して貼り付け」します。

「形式を選択して貼り付け」で数式をペーストするには、次のように記述します。

同一範囲を値で貼り付け直し

    '対象範囲の全体を値で貼り付け
    Rng.Copy
    Rng.PasteSpecial Paste:=xlPasteValues

ワークシートWs内の範囲Rngをコピーします。

範囲Rngに「形式を選択して貼り付け」します。

「形式を選択して貼り付け」で値をペーストするには、次のように記述します。

終了処理

いずれも見た目の問題なので、省略してもOKです。

    '貼り付け範囲の点線を解除
    Application.CutCopyMode = False
    
    'セルC1を選択
    Ws.Range("C1").Select

コピペを実行すると、貼り付けられた範囲が点線で残ります。

通常は[Esc]キーを押すなどして消去するのですが、これをマクロで実行するのが次の1行。

セルの選択については省略します。

まとめ

Worksheetオブジェクト・Rangeオブジェクトを使って、数式をコピペ＆値で貼り付け直すマクロを解説しました。

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