小粋空間  -  a cheap joke and a play on words site

Excel VBAでセルに設定された数式を取得する方法

Excel VBAでセルに設定された式を取得する方法を紹介します。

また、セルに数式を設定する方法や、取得した式を書き換える方法もあわせて紹介します。

１．問題点

ExcelのB7セルに「=SUM(B2:B6)」という、B2～B6セルの合計「15」を表示する式が入ったワークブックがあります。

このB7セルに設定された式そのものをVBA取得したいのですが、次のように記述すると、計算した値「15」しか取得できません。

Sub test()
    MsgBox Range("B7").Value
End Sub

実行結果

２．セルに設定された数式を取得する

Excel VBAでセルに設定された式を取得するには、Formulaプロパティを利用します。

Sub test()
    MsgBox Range("B7").Formula
End Sub

実行結果

Valueプロパティが値そのものを扱うプロパティであるのに対し、Formulaプロパティは実際に入力されている数式が格納されるプロパティです。

３．セルに数式を設定する

セルに数式を設定する場合、２項と同様、Formulaプロパティを利用します。

Sub test()
    Range("B7").Formula = "=SUM(C2:C6)"
End Sub

右辺には数式そのものをダブルクォーテーションで括ったものを記述します。

なお設定するときに限り、Formulaプロパティを省略することが可能です。

Sub test()
    Range("B7") = "=SUM(C2:C6)"
End Sub

４．取得した数式を書き換える

取得した数式は文字列として扱えるので、Replaceなどを利用して書き換えることができます。

あとは書き換えた文字列をFormulaプロパティに設定すればOKです。

下記の例では、B7セルに設定された「=SUM(B2:B6)」を取得し、「B2:B6」の部分を「C2:C6」に書き換えて元のセルに設定します。

Sub test()
    Dim str As String
    str = Range("B7").Formula
    Range("B7") = Replace(str, "B2:B6", "C2:C6")
End Sub