受講生 さん:

こんばんは。

これは計算量が多いですね。。なんとかしたくなるのも分かります (^_^;
とはいえ、基礎編レベルの知識ではこれが限界です。

受講生さんも受講済の発展編１で、 WorksheetFunction というオブジェクトを扱っています。
名前のとおり、エクセルのワークシート関数を呼び出してVBAで利用できるというものです。
使いこなせるようになると、同様の計算をするエクセルワークシート関数をご存知でしたら、自分で計算アルゴリズムを編み出さないでも１行で計算をしてくれて便利です。
古典的かつ代表的なワークシート関数でしたらほぼほぼVBAから利用可能です。

以下に、Sum関数、Count関数、CountIf関数を活用する場合のサンプルを示します。試してみてください。

Sub wsfsample()
    Range("N2").Value = WorksheetFunction.Sum(Range("M2:M51"))
    Range("N3").Value = WorksheetFunction.Count(Range("M2:M51"))
    Range("N4").Value = WorksheetFunction.CountIf(Range("F2:F51"), "=渋谷区")
End Sub

上に示したサンプルコードからも分かるとおり、引数でセルを指定する際は、セル番地でなく、セルをオブジェクトとして指定する必要があります。
あと、エクセルのバージョンによっては、 WorksheetFunction の前に Application. をつけなくてはならないかも。
そこだけ注意。

それから、エクセルで開いているファイルのどれかに計算量の多いワークシート関数が大量に含まれている場合も処理が遅くなる傾向があります。
セルの編集がある都度、すべてのワークシート関数が再計算をするからです。
VLookUp関数とかIsError関数とか、そういうたぐいのがぐちゃぐちゃと入れ子になっているセルが大量にあるとかの場合ですね。

その場合、マクロでの処理開始の前に、ワークシート関数の再計算機能を停止させ、処理後に自動で再計算するように再設定します。
以下の要領。

Sub calcsample()
    Application.Calculation = xlCalculationManual
    Range("N2").Value = WorksheetFunction.Sum(Range("M2:M51"))
    Range("N3").Value = WorksheetFunction.Count(Range("M2:M51"))
    Range("N4").Value = WorksheetFunction.CountIf(Range("F2:F51"), "=渋谷区")
    Application.Calculation = xlCalculationAutomatic '[*]
End Sub

[*]について補足すると、エクセルで設定画面から再計算を自分で止めている人も（滅多にないですが）いるかもしれないので、以下のほうがより親切。

Sub calcvsample()
    Dim c As Long
    c = Application.Calculation '[+]
    Application.Calculation = xlCalculationManual
    Range("N2").Value = WorksheetFunction.Sum(Range("M2:M51"))
    Range("N3").Value = WorksheetFunction.Count(Range("M2:M51"))
    Range("N4").Value = WorksheetFunction.CountIf(Range("F2:F51"), "=渋谷区")
    Application.Calculation = c '[*]
End Sub

なぜ変数を Long 型にしたのか？とか [+] でなにをしているのか？とかいったことについては、基礎編レベルを大きく超えるのでここでは説明割愛します。
受講生さんは発展編１受講済のようですので、まずは上記についてテキストにて再学習のうえ、さらにご質問あるようでしたら、発展編１講座のどこかでまたご質問いただければと。

2016/11/07 07:07