Excel VBA 選択範囲の罫線色の変更プロージャの動作速度の改善について

Question

次はエクセルで既に設定されている罫線の色を一括変更するプロージャーですが、表形式のシートで実行してみると結構動作が遅いです。高速化できますか？

Sub 罫線色変更()
Application.ScreenUpdating = False
    Dim r As Range
    Dim i As Long
    Application.Dialogs(xlDialogEditColor).Show (1)
    For Each r In Selection
        For i = 7 To 10
            If r.Borders(i).LineStyle <> xlNone Then
                r.Borders(i).Color = ActiveWorkbook.Colors(1)
            End If
        Next i
    Next r
Application.ScreenUpdating = True
End Sub

ママチャリ · Accepted Answer

RangeをFor Eachでループしているように、BorderもFor Eachでループした方が早いと思います。
実際にやってみて、処理時間を報告して頂けると嬉しいです。その際、選択した（処理した）行列数もよろしくです。

いんちょ · Answer

各セルについて上下左右の罫線をチェックされていますが、それだと同じ罫線を重複してチェックすることになります。上と左だけチェックして、右端と下端のセルだけ右と下の罫線をチェックするようにすれば、時間は半分強になるかと思います。

tatsumaru77 · Answer

No4です。
>表形式のシートで実行してみると結構動作が遅いです。
表形式のシートということなので、No4のように
「選択範囲のすべてのセルが全く等しく罫線をもっている」という前提かと思いましたが、前提が違うようですね。

＞今回は、各セルの罫線の状態が異なる場合を想定していました。
ということなので、役に立たないかもしれませんが、
「選択範囲のすべてのセルが全く等しく罫線をもっている」場合のマクロです。
以下のようにしてください。

Sub 罫線色変更()
    Application.ScreenUpdating = False
    Application.Dialogs(xlDialogEditColor).Show (1)
    Selection.Borders.Color = ActiveWorkbook.Colors(1)
    Application.ScreenUpdating = True
End Sub

Qchan1962 · Answer

＞colr ですが、Double型にする必要はありますか？

独学なので習慣というか・・確か？どこかで覚えたような気がする程度です

少し調べました　と言いてもVBEのオブジェクトブラウザーで
Excelライブラリ　Bordersクラスの　Colorメンバーで見ると

Property Color As Variant
    Excel.Borders のメンバー

とあります　という事は　Variant　ですね
これは取得できなかった時にEmptyを返す必要があるのかもしれません？

簡単に確かめると
Sub a()
Dim c As Variant
c = Range("A1").Borders(7).Color
Debug.Print TypeName(c)　'Double
End Sub

Doubleと出力されます
Variant/Double　なのですね

必要があるかはわかりません
ただ、処理速度という意味ではVariant型は使わない方が良いというのも
昔どこかで見た覚えがありますのでAs Double　としました

あやふやな情報（記憶）になりますので確認が必要と思います

Qchan1962 · Answer

こんばんは　既にある回答と重複いたしますが

Selectionの形状とLineの有無の形で処理を短絡（範囲を一度に設定）できない状態になりますので　複雑な状態（ランダムな選択範囲と飛び飛びな罫線）での処理手順は　示されているような手順になるかと思います

ご質問に示されているコードで少しでもという事でしたら
＃３様が回答されている事とオブジェクトなのでWith でくくり　さらに
条件（同色の場合の処理をしない）を増やすくらいでしょうか・・・
早くなるといってもわずか　かも

Dim r As Range
Dim i As Long
Dim colr As Double
Application.Dialogs(xlDialogEditColor).Show (1)
colr = ActiveWorkbook.Colors(1)
Application.ScreenUpdating = False
For Each r In Selection
For i = 7 To 10
With r.Borders(i)
If .LineStyle <> xlNone Then
If .Color <> colr Then .Color = colr
End If
End With
Next i
Next r
Application.ScreenUpdating = True

tatsumaru77 · Answer

No2です。
「選択範囲のすべてのセルが全く等しく罫線をもっている」という解釈です。
添付図の左側の場合は、A1:C10が選択されている前提です。
添付図の右側の場合は、F3:H9が選択されている前提です。

bonaron · Answer

> ActiveWorkbook.Colors(1)

これを変数に代入して だけ参照するようにすれば
かなり速くなると思われます。

Dim col As Long
Application.Dialogs(xlDialogEditColor).Show (1)
col =  ActiveWorkbook.Colors(1)
For Each r In Selection
For i = 7 To 10
If r.Borders(i).LineStyle <> xlNone Then
r.Borders(i).Color = col

tatsumaru77 · Answer

現在のマクロは、選択された範囲内のセルの罫線の有無を判定し、罫線がある場合に、色付けを行っています。

もし、選択された範囲には必ず罫線があるものとして、色付けを行ってよいなら、高速化は可能です。

その場合は、罫線がある範囲を選択してマクロを実行することになりますが、いかがでしょうか。それで良ければ、マクロの提供は可能です。

fujillin · Answer

こんばんは

簡単にテストしてみましたが・・

VBAで時間がかかる原因のひとつに、シート情報へのアクセスがあります。
罫線の有無をチェックせずに、色をまとめてセットするだけで上手く行かないかと思いましたが、罫線が設定されていない部分にまで罫線が引かれてしまうようで、どうやら、ご提示のように一つ一つの罫線をチェックして設定せざるを得ないようですね。

とはいえ、対象が表であるなら、隣接するセル間の罫線を重複して2回処理していることになりますので、2辺だけをチェックするロジックに変えればだいぶ速くできそうであろうと考えてみました。
上記でチェックできない外側の2辺（＝1行と1列分）はそれぞれ別にチェックすることになりますが、処理量が「半分+α（＝6割以下程度か？）」くらいになることが期待できます。
簡単なものでテストしてみると、期待には届かず、約6.5割くらいの処理時間（＝約3.5割減）となりました。

ただし、上記は選択が単純な矩形エリアの場合を想定していますので、複数のエリアを選択することも許容する場合には、処理のロジックとして、エリア毎に処理するようにしないと上手くいきません。（Area属性は矩形なので）
こちらの場合でも大きくは変わらないと思いますが、もう少し時間がかかる可能性はあります。

速度は大幅には向上しませんけれど、多少なりとも速くはなりますので、ご参考までに。

Excel VBA 選択範囲の罫線色の変更プロージャの動作速度の改善について

RangeをFor Eachでループしているように、BorderもFor Eachでループした方が早いと思います。

各セルについて上下左右の罫線をチェックされていますが、それだと同じ罫線を重複してチェックすることになります。

No4です。

＞colr ですが、Double型にする必要はありますか？

こんばんは 既にある回答と重複いたしますが

No2です。

> ActiveWorkbook.Colors(1)

現在のマクロは、選択された範囲内のセルの罫線の有無を判定し、罫線がある場合に、色付けを行っています。

こんばんは

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

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

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

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

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

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

こんばんは　既にある回答と重複いたしますが