スピル系関数の結果を値貼り付けする

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

上図のようなケースで、セルC2に、セル範囲A2:A6の合計値を入力したいとします。もちろん、いろいろな方法があります。セルの数値をひとつずつ足していくのも手ですが、とはいえ合計です。SUM関数を使えば一発でしょう。マクロの中でワークシート関数を利用するのでしたら、WorksheetFunctionを使います。

Sub Macro1()
    Range("C2").Value = WorksheetFunction.Sum(Range("A2:A6"))
End Sub

もちろん、この考え方でも、セルC2に合計値が入力されます。しかし、実務は複雑です。SUM関数ひとつで済むケースなど、むしろ珍しいでしょう。計算するにしても、もっと長くて複雑な数式になることもあります。そんなときは、ひとつの方法として、一度その数式をセル(ここではC2)に代入して、その数式の結果を値貼り付けするという考え方もあります。たとえば、次のように。

Sub Macro2()
    Range("C2").Formula = "=SUM(A2:A6)"
    Range("C2").Value = Range("C2").Value
End Sub

結果は同じなので画像は省略します。さらに、ちょっと慣れた方でしたら、次のようにも書けます。

Sub Macro2()
    With Range("C2")
        .Formula = "=SUM(A2:A6)"
        .Value = .Value
    End With
End Sub

Withを使った「.Value = .Value」部分がプロっぽいと評判ですｗ では、別のワークシート関数を使ってみましょう。今度はSORT関数です。SORT関数の詳しい解説は、下記のページをご覧ください。

SORT関数の解説

SUM関数のときと、同じマクロでやってみます。

Sub Macro3()
    With Range("E2")
        .Formula = "=SORT(A2:C6)"
        .Value = .Value
    End With
End Sub

何とも期待外れです。実は、結果がスピるような数式(動的配列数式)をセルに代入するときは、FormulaプロパティではなくFormula2プロパティを使わなければなりません。これはVBAのルールです。なので、次のように変更します。

Sub Macro3()
    With Range("E2")
        .Formula2 = "=SORT(A2:C6)"
        .Value = .Value
    End With
End Sub

しかし、残念ながら結果は同じです。

このへんまでくると、頭が混乱してChatGPTやCopilotなどAIに頼る人もいるでしょうね。でも、ここで作りたいマクロの動作を、正確な表現でプロンプトに書く自信がありますか？ということで、AIに代わって解説します。まず「.Formula2 = "=SORT(A2:C6)"」と「.Value = .Value」のうち、どちらがマズかったのか、ひとつずつ実行してみます。

Sub Macro3()
    With Range("E2")
        .Formula2 = "=SORT(A2:C6)"
''        .Value = .Value
    End With
End Sub

おお、うまくいってるじゃないですか。じゃ、この状態に対して「.Value = .Value」だけを実行してみます。

Sub Macro3()
    With Range("E2")
''        .Formula2 = "=SORT(A2:C6)"
        .Value = .Value
    End With
End Sub

なるほど、こいつが原因でしたか。でも、数式を代入したのはセルE2です。ほかはすべて、その数式の結果です。だったら、数式のセルを値貼り付けすれば成功するようにも思えます。このへんは、日頃から"スピル系"の関数を、ワークシート上で使っている方ならピンと来るかもしれません。手動操作で試してみましょう。

上図のセルE2にはSORT関数を入力しました。そのセルE2だけをコピーして、セルI2に値貼り付けします。結果は下図のようになります。

ここは、下図のようにSORT関数の結果すべてをコピーして、値貼り付けしなければなりません。

したがって「.Value = .Value」の対象オブジェクトは「Range("E2")」ではなく「Range("E2#")」です。では、そのように変更して、最初からもう一度マクロを実行してみます。

Sub Macro3()
    With Range("E2#")
        .Formula2 = "=SORT(A2:C6)"
        .Value = .Value
    End With
End Sub

これではエラーです。なぜエラーになるか分かりますか？上記のコードで、セルE2がスピるのは「.Formula2 = "=SORT(A2:C6)"」が実行された後です。その前(Withで定義されるとき)には、まだセルE2はスピっていません。スピッていないセルに対してスピル範囲演算子(#)を指定すると、エラーだからです。

つまり「.Formula2 = "=SORT(A2:C6)"」はセルE2に対して行われなければならず、「.Value = .Value」の対象はセルE2#でなければならないです。Withを使わないで書くと下のようになります。

Sub Macro3()
    Range("E2").Formula2 = "=SORT(A2:C6)"
    Range("E2#").Value = Range("E2#").Value
End Sub

これではWithで括れません。したがって、正しく動作するコードは

Sub Macro3()
    Range("E2").Formula2 = "=SORT(A2:C6)"
    With Range("E2#")
        .Value = .Value
    End With
End Sub

です。

言われてみれば理解できるかもしれません。でも、スピルの仕様や動作、スピル範囲演算子(#)の挙動などを正しく理解していないと、「関数の結果を値貼り付けする」という簡単な処理であっても、思わぬ落とし穴にハマります。ご注意ください。