マクロからワークシートに計算式を書き込むことができます。
ワークシートの計算式を動的にセットするサンプルです。
(画像をクリックすると、このサンプルがダウンロードできます)
何行入力されるかわからないような表には、予め想定される最大の行数分に横合計などの計算式をセットしておくようなことをされていると思いますが、このサンプルでは、「数量」「単価」が入力されるとその行に計算式がセットされるようにしています。ですから空の状態では何も式がないので小さいファイルサイズで済みます。
このマクロは、ワークシート上でセル内容が変わったことのイベントで動作させます。このため、「Worksheet_Change」のイベント処理(Sheet1)に書き込みます。)を使います。
'***************************************************************************************************
' 計算式をVBAから書き込むサンプル Sheet1(Class)
'
' 作成者:井上治 URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev 変更履歴内容------------------------------------------------------------------------>
'03/07/11(1.00)新規作成
'04/02/28(1.01)初回修正
'20/02/23(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit
'***************************************************************************************************
' ■■■ ワークシートイベント ■■■
'***************************************************************************************************
'* 処理名 ：Worksheet_Change
'* 機能 ：セル値変更イベント
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：Arg1 = Target(Range)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2003年07月11日
'* 作成者 ：井上 治
'* 更新日 ：2020年02月23日
'* 更新者 ：井上 治
'* 機能説明：数量､単価を入力した行に計算式をセットする
'* 注意事項：
'***************************************************************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------------------------------------
Dim lngRow As Long ' 行INDEX
Dim lngCol As Long ' カラムINDEX
' 変更した行を取得
lngRow = Target.Row
' 見出し行は無視
If lngRow <= 1 Then Exit Sub
' 変更したカラムを取得
lngCol = Target.Column
' 数量､単価以外は無視(商品列は無視)
If ((lngCol < 3) Or (lngCol > 4)) Then Exit Sub
' 計算式セット自体でもイベントが発生するのでイベントを抑制
Application.EnableEvents = False
' 計算式をセット(R1C1形式なら式は固定で良い)
Cells(lngRow, 1).FormulaR1C1 = "=ROW()-1" ' №(行番号-1)
Cells(lngRow, 5).FormulaR1C1 = "=RC3*RC4" ' 金額(=数量×単価)
' イベントを再開
Application.EnableEvents = True
End Sub
'----------------------------------------<< End of Source >>----------------------------------------
「数量」「単価」が入力されたと判断できたら計算式をセットしますが、計算式をセットすること自体でもこの「Worksheet_Change」イベントが呼ばれてしまうので、輻輳を避けるために計算式をセットする直前にイベント処理を停止させています。(EnableEvents:=False)
計算式は「A1参照形式」だと行ごとに式の中身を変えなければならないので、ここでは式を固定できる「R1C1参照形式」を使います。「R」の後ろに数字を置かない場合は式があるセルの行を指すのでどの行でも式の中身が変化しません。
この方法はその時のExcelの表示形式が「A1参照形式」のままであっても問題ありません。
「計算式をVBAから書き込む」の核心部分の説明から外れますが、このような利用者が入力を行なうワークシートの場合は利用者の「誤操作」の配慮も必要です。 このサンプルでは「商品」「数量」「単価」の列以外はロックしており、シート保護を掛けています。
ここで問題となるのは、マクロの動作が「ロックされたセルに計算式を書き込む」ということになっている点です。
これは「ロックしたセルに、シート保護を解除せずにデータを書き込む。」で説明している通りですが、 今回のサンプルにも「Workbook_Open」の記述を投入してあります。