ExcelのマクロVBA動作を軽くする方法

Question

添付画像のように、sheet1の支店の数を、カウントのシートで数えています。
(特定の文字と文字色をカウントしています)

マクロは以下のように設定しています。

Public Function CountColor(ByVal area As Range, ByVal colorCell As Range) As Long
Dim targetRange As Range
Dim wkCount As Long
wkCount = 0
For Each targetRange In area
If targetRange.Font.Color = colorCell.Font.Color Then
If targetRange.Value = colorCell.Value Then
wkCount = wkCount + 1
End If
End If
Next
CountColor = wkCount
End Function

ですが、sheet1で支店を追加したり、フィルターをかけたり等の作業をすると、動作がとても重くなります。
sheet1の支店の数なども、都度増やしたり減らしたりして、カウントのシートに反映させたいので、どうにかサクサク更新して作業が軽くなるような方法はないでしょうか。

fujillin · Accepted Answer

こんにちは

＞どうにかサクサク更新して作業が軽くなるような方法はないでしょうか。

速度を上げるためのアイデアを以下にいくつか…

1）すでに指摘が出ている内容はもっともな内容です。
組み込み関数等では未使用範囲は計算をスキップしているようですが、VBAの場合は指定範囲全部を計算しますので、範囲を狭めることで計算回数を大きく減らせます。

2）セルにアクセスすると時間がかかるので回数を減らす。
具体的には、colorCell.Font.Color、colorCell.Valueにループで毎回アクセスしていますが、固定値なのでループ外で最初に変数に格納しておくようにするだけで速度はさらに上がります。
（ほぼ半分以下になります）

3）ループで1セルずつチェックせずにFIND、FINDNEXTで検索する方が速いかと予想しましたが、どうやらそうでもないらしく、簡単なテストを行ったところではご提示のままよりは大分速くなりますが、1）と2）の組み合わせの方がさらに速くなるようです。

4）ワークシート関数のCountIfでカウントするのが最も速そうなので、これで実現できるように、全体の仕組みを以下のように変えます。
考え方としては、計算にかかる時間を分散して一部を事前に計算しておいて単純なCOUNTIF計算が可能な形式にしておくことと、VBAよりも速いと考えられる組み込み関数を利用することでしょうか。

・Sheet1に作業列を用意します（どこでも良い）
・color値は10進8桁で表示できるので、「文字列+color値（前0で8桁固定）」を返す関数を用意しておく。(関数を、仮にCとする）
・関数Cで、A列の値を「文字列+color値」として作業列に記入する。
（　=C(A1）のような要領)
・実際にカウントする関数は、=COUNTIF(Sheet1!作業列, C(A1)) 等として入力。

※ 簡単なテストしかしていませんが、最後の4）の方法が最も速いと思われます。
※ ただし、No1様のご指摘にもありますが、色を変えても関数の監視対象外なので、色の編集操作を反映させるには少々注意が必要になります。
念のため、Sheet1のDeActivateなどで、再実行しておくのも手かもしれません。
（まぁ、この点に関しては、現状でも似たところがあると思いますので・・・）

ママチャリ · Answer

大きなお世話かもしれませんが、文字の色に意味を持たせるのは、やめた方が良いですよ！！例えば、色を変えただけでは再計算が行われないので、タイミングによっては、間違った表示になっていることがあります。

さて、本題の処理を軽くする件です。
集計範囲を「C:C」のように列を丸ごと指定しているようですが、これだと未使用の行に対してもカウント判定を行ってしまうので、余計な時間を要しているのではないでしょうか。
対応策としては、集計範囲を「C1:C200」のように限定するとか・・・。
もしくは、関数側で未使用行を対象外にするとか・・・。

関数側で対応するなら、こんな感じですかね。
For Each targetRange In Intersect(area, area.Worksheet.UsedRange)

ExcelのマクロVBA動作を軽くする方法

こんにちは

大きなお世話かもしれませんが、文字の色に意味を持たせるのは、やめた方が良いですよ！！例えば、色を変えただけでは再計算が行われないので、タイミングによっては、間違った表示になっていることがあります。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング