Office TANAKA - Excel VBA Tips[オートカルクを操作する]

Excelではセルを選択したとき、選択したセル範囲の合計やデータの個数などが、ステータスバーに表示されます。
この機能を「オートカルク」と呼びます。

これ自体すごく便利な機能なのですが、このオートカルクはマクロから自由に操作することができません。たまに「オートカルクに表示されている計算結果をマクロから取得できないか」という質問を目にします。おそらくそれは無理でしょうし、その計算結果を取得するくらいなら自分で計算した方が早いです。

このオートカルク機能をマクロから自由に操作することはできませんが、設定を変える程度なら可能です。あまり知られていない方法ですが、参考までにご紹介しましょう。

まず、オートカルクの計算方法として何が設定されているかを取得してみます。
ステータスバーを右クリックすると、現在の計算方法を設定するためのショートカットメニューが表示されますよね。ここには、現在の計算方法が何なのか記録されているわけですから、このメニューの状態を調べます。

このショートカットメニューはCommandBars("AutoCalculate")です。コマンドは全部で7つ。選択されたコマンドにはチェックマークが付きますので、どのコマンドにチェックマークが付いているかを調べます。なお、メニューにチェックマークを付ける方法は「メニューにチェックマークを付ける」を参照してください。次のコードは、現在のオートカルクに設定されている計算方法を表示します。

Sub Sample1()
    Dim i As Long
    With Application.CommandBars("AutoCalculate")
        For i = 1 To 7
            If .Controls(i).State Then
                MsgBox .Controls(i).Caption
                Exit For
            End If
        Next i
    End With
End Sub

任意の計算方法を指定するには、このメニューのコマンドをクリック(実行)してやればいいです。メニューのコマンドを実行するにはExecuteメソッドを使います。次のコードは、もしオートカルクの計算方法が「合計」になっていなかったら「合計」にします。

Sub Sample2()
    With Application.CommandBars("AutoCalculate")
        If .Controls(7).State = False Then
            .Controls(7).Execute
            MsgBox "「合計」にしました"
        End If
    End With
End Sub