【VBA】再計算を実行した際に処理を行う

こんにちは、Ryoです。
Excelを扱っていれば、数式を用いて処理を行うことが多々あると思います。その該当セルの数値を変更する度に再計算が実行されますが、例えば合計金額のように桁数が結果によって変わる場合はセルに収まらなくなることもありますので、そんな時はCalculateイベントを活用して再計算の都度、列幅の自動調整を行うことも可能です。今回はその内容について書いていきます。

1.サンプル概要
2.サンプルコード
- ◆参考：ブック全体を対象にする場合
3．まとめ

1.サンプル概要

サンプルとして以下の表を「Sheet２」に用意しています。

E列の「金額」欄は「単価」×「数量」の計算結果が表示されるように数式が入力してあります。この状態で「数量」の数値を1000にすると、100×1000で金額欄が100,000になるのですが、現在の列幅では表示することが出来ずに「＃＃＃＃表示」となります。

この列幅を都度手動で調整するのは煩わしいので、再計算が実行される度に、列幅を自動調整するようにします。

数量に1000を入力して再計算が実行されると「単価」「数量」「金額」が入力されているC－E列の幅が自動調整され、メッセージボックスを表示します。

次に単価を10、数量を10にした場合は以下のように調整されます。

以上のサンプル内容について書いていきます。

2.サンプルコード

Private Sub Worksheet_Calculate()
'※※再計算が実行された際に処理を行う※※

    'C～E列の列幅を自動調整する
    Columns("C:E").AutoFit

    'メッセージボックスの表示
    MsgBox "再計算が行われましたので" & vbCrLf & _
    "C-E列幅を自動調整しました", vbInformation

End Sub

今回使用しているCalculateイベントは対象のシートモジュールに記述し、再計算が行われた際に処理を行うものです。今回はSheet2に表を作成しているので、Sheet2のシートモジュールに記述しています。

≪構文≫

Private Sub Worksheet_Calculate()
statements
End Sub

サンプルではE列に金額を求める数式が入力されており、「単価」や「数量」の数値を変更うすることで再計算が実行されます。

この時にCalculateイベントが発生するので、入力数値や計算結果によって列幅を変えたいC～E列の列幅を自動調整する為にAutoFitメソッドを使用します。

Columns(“C:E”).AutoFit

このAutoFitメソッドは列幅や行高さを内容に合わせて自動調整してくれるもので、オブジェクト.Autofit（オブジェクトは設定する列）と指定します。

後は実行されたことを確認する目的でメッセージボックスを表示させています。

◆参考：ブック全体を対象にする場合

量が多い複数のSheetに跨って実行したい場合、対象Sheet全てに記述するのは大変なのでブック全体を対象に処理する形もとれます。

その場合はシートモジュールではなく、ワークブックモジュール（ThisWorkbook）に記述します。

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

  Columns("C:E").AutoFit

  MsgBox Sh.Name & "で再計算が行われましたので" & vbCrLf & _
  "C-E列幅を自動調整しました", vbInformation

End Sub

先に書いた内容と同じなので、この場合は全てのシートC-E列を対象に再計算された際に列幅を自動調整する形になります。

その後のメッセージボックスでは、処理されたシートがわかるようにSh.Nameを追加することで、メッセージにシート名が表示されます。