EXCEL VBAで計算式をセットする

はじめに

見積書などで計算式をセルにセットして利用することが多いのですが、誤って計算式を削除してしまうことがあります。

シートを保護すれば良いのですが、「計算式セット」ボタンを配置してVBAで計算式をセットする方法のメモです。

間違って削除したり計算式がおかしくなったりしたとき、簡単に復旧できるのがメリットです。

列の挿入や削除で計算式の修正が必要になるのがデメリットではありますが、レイアウトが確定していれば気にする必要は無いものと思います。

今回の完成版は下記からダウンロードできます。

見積書

1 ファイル 25.53 KB

ダウンロード

ボタンの設置とコードの記述

１．ボタンの配置

開発タブの挿入をクリックし下記赤枠のコマンドボタンをクリックします。

※開発タブが表示されていない場合はこちら

マウスで適当な大きさのボタンを配置します。

ボタンを右クリックしてプロパティを選択し、プロパティウィンドウのCaption部分を「計算式セット」に修正します。

プロパティウィンドウを閉じてから「計算式セット」ボタンをダブルクリックします。

VBエディタが表示されるのでコードを記述します。

※Private Sub CommandButton1_Click()とEnd Subの間に下記コードを貼り付けます。

    Dim i As Integer
    
    With Sheets("見積書")
        ' --------------------------------------------------
        ' 明細行　計算式
        ' --------------------------------------------------
        For i = 18 To 29
            .Cells(i, 15).Value = "=L" & Trim(Str(i)) & "*N" & Trim(Str(i))
        Next i
        ' --------------------------------------------------
        ' 明細合計　計算式
        ' --------------------------------------------------
        .Cells(30, 15).Value = "=SUM(O18:O29)"
        ' --------------------------------------------------
        ' 消費税　計算式
        ' --------------------------------------------------
        .Cells(16, 16).Value = "=O30*O16"
        ' --------------------------------------------------
        ' 税込合計　計算式
        ' --------------------------------------------------
        .Cells(16, 7).Value = "=O30+P16"
    End With

ファイルメニューの上書き保存をクリックしますと、メッセージが表示されるので「いいえ」をクリックしてマクロ有効ブックとして保存します。

※拡張子がxlsmになります。

開発タブのデザインモードをクリックします。

「計算式セット」ボタンをクリックすると計算式がセットされます。

おわりに

明細行の記載の無い行の計算式を消してしまうといった使い方ができるので、IF文などで０の場合は表示しない等の複雑な式にしなくて済みます。

印刷範囲を指定して配置したボタンが印刷されないように注意が必要です。