こんにちは、Ryoです。
Excelを日々扱う中で数式を使うことも多いと思いますが、VBAで集計などの処理を行った際に数式も設定したいことや、既に入力されている数式を修正したいこともあるかもしれませんので、今回は数式に関する内容を書いていきたいと思います。
1.サンプル概要
サンプルとして以下画像の簡易的な表があるとします。空欄になっているセルには何もありませんので、ここに「a)×管理費」と「計」に算出する数式をVBAで入力します。
サンプルコードを実行すると数式が入力され計算結果が表示されます。
2.サンプルコード
◆サンプル1・・・数式の入力
Sub Sample1()
'＊＊＊セルへ数式を入力する＊＊＊
Dim D_Str, Adm, Addr1, Addr2 As String
Dim Col_a, LastRow, S_Row As Long
Dim i As Integer
'***表の見出し検索、セル位置取得、最終行取得***
For Each D_Str In ActiveSheet.UsedRange
If D_Str = "品番" Then
'列番号取得
Col_a = D_Str.Column
'入力最終行の取得
LastRow = Cells(Rows.Count, Col_a).End(xlUp).Row
ElseIf D_Str = "管理費" Then
'絶対参照形式で取得
Adm = D_Str.Offset(0, 1).Address
ElseIf D_Str = "a)×管理費" Then
D_Str.Offset(1, 0).Select
'入力開始行の取得
S_Row = D_Str.Row + 1
Exit For
End If
Next
'***セルに数式の入力***
For i = S_Row To LastRow
With ActiveCell
'a)数量×単価欄のセルアドレスを相対参照で取得
Addr1 = .Offset(0, -1).Address(False, False)
'アクティブセルのアドレス（相対参照）で取得
Addr2 = .Address(False, False)
'アクティブセルへの数式入力
.Formula = "=" & Addr1 & "*" & Adm
'「計」欄への数式入力
.Offset(0, 1).Formula = "=" & Addr1 & "+" & Addr2
.Offset(1, 0).Select
End With
Next i
End Sub
セルへの数式を入力するにはFormulaプロパティを使用します。
オブジェクト.Formula＝数式
構文としてはシンプルなのでわかりやすいですね。
サンプルではセル毎に入力していますが、用途によっては複数セルに入力したいことがあるかもしれませんので、その場合は
例：Range(“A1:A10”).Formula = “=B1*C1” ’セルA1～A10に数式を入力
このように記述することでRange指定した範囲に数式が入力されます。
一見指定した”=B1*C1″が範囲指定セルに書き込まれるように思いますが、式が相対参照なのでセルA10に入力される数式は”=B10*C10″のように自動的に変えてくれます。
変えたくない場合は$B$1のような絶対参照形式で指定すればOKです。
◆動作説明
サンプルコード動作の説明ですが、For～Eachで行っているのは表の見出しを検索/取得して数式入力の開始行、最終行や必要となる「管理費」のデータを取得しています。
次にセルに数式を入力しますが、「a)*管理費」欄はセルE5に「管理費」データのG2を乗じたものなので、それぞれのセルアドレスを取得して.Formula = “=” & Addr1 & “*” & Admのように数式の形に連結し入力しています。（G2は固定なので絶対参照形式）
また「計」の欄はE列とF列を加算すれば良いので、同様に
.Offset(0, 1).Formula = “=” & Addr1 & “+” & Addr2の形で数式とし入力します。
サンプルは都度入力ですが、この辺りは上述した通り相対参照なので範囲指定で入力しても大丈夫です。
◆サンプル2・・・数式を取得し編集（書き換）を実行
このサンプルは参考として見てもらえれば良いかと思いますが、私の場合は計測機の補正値が日々変わることもあって、入力済みの数式を編集しながらデータ書込みをVBAで行うことがありますので、頻度的には結構多いですね。
サンプルとしてはサンプル1で実行した表の「a)×管理費」の数式をセルアドレスではなく「数値」で乗算する数式に変更するものです。
≪実行前≫
≪実行後≫
サンプルコードは次の通りです。
Sub Sample2()
'セルに入力された数式を取得し編集（書き換）を実行
Dim D_Str, Adm, Adm_Adrr As String
Dim Col_a, LastRow, S_Row As Long
Dim i As Integer
For Each D_Str In ActiveSheet.UsedRange
If D_Str = "品番" Then
Col_a = D_Str.Column
LastRow = Cells(Rows.Count, Col_a).End(xlUp).Row
ElseIf D_Str = "管理費" Then
'数値を取得する
Adm = D_Str.Offset(0, 1)
'セルアドレス（絶対参照）で取得
Adm_Adrr = D_Str.Offset(0, 1).Address
ElseIf D_Str = "a)×管理費" Then
D_Str.Offset(1, 0).Select
S_Row = D_Str.Row + 1
Exit For
End If
Next
For i = S_Row To LastRow
With ActiveCell
'アクティブセルの数式を取得してReplace関数で
'入力済みの$G$2を数値に置換する
.Formula = Replace(.Formula, Adm_Adrr, Adm)
.Offset(1, 0).Select
End With
Next i
End Sub
基本的にはサンプル1と同様ですが、変更している部分は「管理費」のデータを数値としても取得する点、数式入力時にReplace関数を使って文字の置換を行っています。
Replace(.Formula⇒現在の数式, Adm_Adrr⇒置換対象の文字, Adm⇒置換後の文字)
具体的にはReplace(=E5*$G$2,$G$2,0.1)となっているので入力済み数式内の絶対参照アドレスが数値に置換されるということです。
言い忘れるところでしたが、数式の取得も同様にFormulaプロパティなので、
オブジェクト.Formula・・・A1形式
オブジェクト.FormulaR1C1・・・R1C1形式
これで取得出来ます。
私はあまり使わないのですがR1C1形式で取得する場合は、Excel側がA1形式であってもR1C1形式で取得してくれます。ただし、入力する場合はR1C1形式で設定してもExcel側がA1形式になっていれば「A1形式」になりますので、ご注意を。
3.まとめ
VBAからでも数式の入力や取得、編集の方法を知っていると双方の良い所を合わせて使えるので変化に対する柔軟性を持たせることが出来ますから良いと思います。
本サンプルは単純な例ですが、実運用であればLenなどで文字数をカウント/ループ処理をして置換対象の文字列を抜き出し、指定する文字と置き換えるなどの処理になろうかと思いますので、その際の検討の一助になればと思い書いてみました。
以上、セルへ数式の入力や取得/編集を行う方法についてでした！
今回の記事が何かの参考になれば幸いです。
Ryo
