Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : 指定なし)
VBA処理速度低下に関して
投稿日時: 20/09/10 11:03:44
投稿者: tanreinama

お世話になります。
コーディングの問題ではないのでスレ違いかも知れませんがご教示よろしくお願いいたします。
 
WINDOWS7でEXCEL2017を使用しています。
毎月行っているEXCELブックの業務でFOR NEXT処理で「20200908」の様な文字列が入っているセルをDATEVALUE関数で日付データに置き換えるというVBAコードを実行しているのですが1か月ほど前から処理が一気に遅くなりました。
置き換えする列が5000個ほどあるのですが以前は2秒ほど終わっていたものが今は20分ほどかかる様になりました。
WINDOWS10でEXCEL2019でも全く同様の状況が起こっています。
処理中は「再計算(プロセッサ4個)40% 」のようにEXCELの下のバーに表示されています。
ちなみにマルチスレッド処理でプロセッサは4個使用していますがマルチスレッド処理を無効にしても結果は変わりません。
以下のコードを組んで実行させて処理時間を図ったところ100個のセルを日付に変更するのに17.5秒がかかります。
ちなみにブックの自動計算を処理中だけ手動に変えた2つめのコードだと処理時間が0.1秒で済みます。
EXCEL自体もしくはVBAの使用が大きく変わったのでしょうか。VBAに依存している業務も多く手動計算、自動計算のタイミングを計りコードを書き換えるのはリスクが高すぎます。何か解決策はないでしょうか。
 
----------------------------------------------------------------------------------------------
Sub 処理時間変更@()
Dim startTime As Double
Dim MyRa As Range
Dim se As Range
 
Set MyRa = Range("A1:A100")
MyRa.Clear
MyRa.Value = "20200911 "
 
'開始時間取得
startTime = Timer
 
For Each se In MyRa
    If Len(se) = 8 And IsDate(Left(se, 4) & "/" & Mid(se, 5, 2) & "/" & Right(se, 2)) Then
        se.Value = DateValue(Left(se, 4) & "/" & Mid(se, 5, 2) & "/" & Right(se, 2))
        se.NumberFormatLocal = "yyyy""年""m""月""d""日"""
    End If
Next
 
 
'処理時間表示
MsgBox Timer - startTime
End Sub
----------------------------------------------------------------------------------------------
Sub 処理時間測定A()
Dim startTime As Double
Dim MyRa As Range
Dim se As Range
 
Set MyRa = Range("A1:A100")
MyRa.Clear
MyRa.Value = "20200911 "
 
'開始時間取得
startTime = Timer
 
'ブックの計算を手動に変更
Application.Calculation = xlCalculationManual
 
For Each se In MyRa
    If Len(se) = 8 And IsDate(Left(se, 4) & "/" & Mid(se, 5, 2) & "/" & Right(se, 2)) Then
        se.Value = DateValue(Left(se, 4) & "/" & Mid(se, 5, 2) & "/" & Right(se, 2))
        se.NumberFormatLocal = "yyyy""年""m""月""d""日"""
    End If
Next
 
'処理時間表示
MsgBox Timer - startTime
 
'ブックの計算を自動に戻す
Application.Calculation = xlCalculationAutomatic
End Sub
----------------------------------------------------------------------------------------------
 
 

回答
投稿日時: 20/09/10 11:56:45
投稿者: 半平太

当方で掲示のコードをテストしましたら、どちらも0.07秒でした。
 
ブックの計算を手動に変更して速くなったのであれば、以下が原因です。
1.数式に重いものがある。
2.変換後の日付データを毎回セルに書き込んでいる
 
なので、変換後の日付を一旦配列に格納し、最後に一気に書き込むのがいいと思います。

Sub 処理時間変更@()
    Dim startTime As Double
    Dim MyRa As Range
    Dim se As Range
    Dim i As Long
    Dim Result()
    
    'ブックの計算を手動に変更
    Application.Calculation = xlCalculationAutomatic
    
    Set MyRa = Range("A1:A100")
    MyRa.Clear
    MyRa.Value = "20200911"
    
    '開始時間取得
    startTime = Timer
    
    ReDim Result(1 To 100, 1 To 1)
    
    For Each se In MyRa
     If Len(se) = 8 And IsDate(Left(se, 4) & "/" & Mid(se, 5, 2) & "/" & Right(se, 2)) Then
        i = i + 1
        Result(i, 1) = DateValue(Left(se, 4) & "/" & Mid(se, 5, 2) & "/" & Right(se, 2))
     End If
    Next
    MyRa.Value = Result
    MyRa.NumberFormatLocal = "yyyy""年""m""月""d""日"""
    
    '処理時間表示
    MsgBox Timer - startTime
End Sub

当方のテストでは、0.008秒(1桁速いです)
 
尚、この手当で改善したとしても、数式については見直しをした方がいいと思います。
多分、以前の数式が余分なことをするものに変わっていると思います。

投稿日時: 20/09/10 13:49:34
投稿者: tanreinama

半平太さん早速ご回答いただきありがとうございます。
配列に格納して一気に書き出すなるほどですね。大変参考になります。
他のコードを参考に一個づつ書き出しではなく一気書き出すコードに変えていきます。
ちなみにForNextループを使わず
MyRa.Offset(0, 1).FormulaR1C1 = "=DATEVALUE(LEFT(RC[-1],4)&""/""&MID(RC[-1],5,2)&""/""&RIGHT(RC[-1],2))"
右となりのセルに計算式を入れてコピー⇒値で張り付けでも早く処理できました。
気になるのは以前のコードを半平太さんのパソコンで再現しても時間がかからず、当方のパソコンでもこれまで何年も時間がかからず処理できていたものが急に時間がかかるようなったことです。

回答
投稿日時: 20/09/10 14:20:55
投稿者: 半平太

今のところ、数式が重くなったんだろうなぁと思っています。
データを一つ入力するだけでも、何か引っ掛かる感じがしないですか?
 
・どんな数式があるのか、それらを幾つのセルに埋めているのか、
・データ量はどのくらいあるのか、
・ファイルサイズはどれくらい膨らんでいるのか
 
そんな情報がなければ、全く分からないです。
千里眼じゃないので。

回答
投稿日時: 20/09/10 16:50:57
投稿者: Suzu

引用:
WINDOWS7でEXCEL2017を使用しています。

引用:
WINDOWS10でEXCEL2019でも全く同様の状況が起こっています。

・OS及び、Officeを変えても同じ傾向
 
テストコード 提示頂いたコードはサンプルなのでしょうか?
 
A1:A100 に対し
 セルの値をクリア
 文字列 20200911 を代入
 
 A1:A100 のセルそれぞれに対し
   値の桁数が8桁
   日付と判定可能な値
  なのかを判定し
  セルの値の各桁から日付を生成
 
と言う流れですよね。
 
前半と後半
セルの範囲が一緒で、前半で値を代入しているのですからループを渡す必要が見えないのですが。。
 
極端な話
 
With Range("A1:A100")
  .NumberFormatLocal = "yyyy""年""m""月""d""日"""
  .Value = #9/11/2020#
End With
で良いのでは?
 
 
 
 
コードに問題がある前提で検討をされている様ですが VBAの処理の問題なのでしょうか?
 
それ以前の検討はされていますか?
 
再現確認は行われたのでしょうか?
先のどちらのマシン環境で実行したのでしょうか?
ファイルは同じファイルで実行したのでしょうか?
ファイルはネットワーク共有ファイル?
  共有モードで開いていませんか?
    他者が開いていませんか?
  ネットワークトラフィックが増える/ネットワーク構造の変更が行われませんでしたか?
  保存に際してセキュリティープログラムを通過させていませんか?
ファイル自体、開くのに時間が掛かりませんか?

回答
投稿日時: 20/09/11 17:05:04
投稿者: mattuwan44

そのファイルで、コピペを多用してませんか?
名前の定義やスタイルなど使ってないけど1回設定して情報が残っていたりすると、
いつの間にか大量に増殖していることがあります。
そんな感じなんじゃないでしょうか?
 
新規ブックに値のみのコピペと、マクロをコピペしてみてはいかがでしょうか?
 
セルの書式設定も列でできるものは列で丸ごと設定した方がよいです。
セル毎に設定していると重くなることがあります。
 
あと、シート上の数式ではNow関数とかToday関数等の揮発性関数を多用していると、
今のマクロでは重くなるかなと思います。

回答
投稿日時: 20/09/12 08:48:34
投稿者: チオチモリン

そのブック、または同時に開いているブックに「リンクされた図」はありませんか?
あれば削除(他のブックであれば閉じる)してみて下さい。

投稿日時: 20/09/14 22:09:14
投稿者: tanreinama

半平太様はじめ皆様ご回答ありがとうございます。
会社の基幹システムからデータウェアハウスで営業データを取得した際に伝票日付が入ったカラムは日付データでは「20200914」などの様な文字データになっています。
文字データを日付データに変更するVBAコードを処理するブックにコーディングしてありそれを実行していたり、アドインとして保存していてショートカットキーで呼び出して選択範囲において日付変更を実行するなどで使っています。
当方でこのVBA処理を使い始めたのが2012年ごろでしたが、これまで1万個ぐらいのセルを処理してもせいぜい2、3秒ぐらいの処理だったと思います。
アドインで実行する時などは処理対象のブックにもよりますが基本的に計算式が入っていない事が多くデータ容量もわずかです。条件付き書式、名前、図形などが倍々で増えていることもなく5kバイト程度のブックで処理している事がほとんどです。
普段は体感的にワークシート関数を使っても引っかかる様な遅さを感じることもありません。バックグラウンドで別の処理が走っていることもありません。
掲示したコードはその処理を再現するために、全くデータの入っていないブックにコードを入れただけのサンプルです。
実際の処理データは日付に変換するセルが何千か何万かあり、セルに書かれている日付もサンプルコードと違い区々です。
何故か1か月ほど前から急に時間がかかるようになりましたが
Application.Calculation = xlCalculationManual を実行すれば時間はかかりません。

回答
投稿日時: 20/09/15 00:09:09
投稿者: バチバッチ

開始時に
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
終了時に
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
とかで早くなるならセルの更新がおそいんだろうけど・・・
皆さん指摘してる関数の更新で時間かかってるとか
一度モジュールのコードだけを新しいブックにコピペして、それを起動してみるとかはどうですか?

投稿日時: 20/09/15 07:47:25
投稿者: tanreinama

パチパッチさん、ありがとうございます。
多分コード云々以前のパソコン自体に内在する問題だと思います。
パソコンの電源を立ち上げて何も稼働していない状況で、上記のサンプルコードしか入っていない状況でシンプルな実行時間だけを検証した結果、100個のセル書き換えに17.5秒もかかってしまいます。
EXCELブックはワークシート上のセルに数式や値などのデータは入っておらず条件付き書式、図形、名前、ハイパーリンクなども全く入っておらず、単純にモジュールのみの状態で再現している状況です。
上記コードを実行してもワークシートに計算式を入れる訳ではないのでセルの再計算に時間がかかることもないはずなのですが。
再度パソコンの環境をセキュリティ面も含めて再確認したいと思います。

投稿日時: 20/09/15 09:44:16
投稿者: tanreinama

申し訳ございません。
自己解決しました。原因はバックグラウンドでEXCELアドインが常駐していたためでした。
アドインブックのシート内セルに
=OFFSET、INDIRECT、MATCHの様なセルの再計算に時間がかかるワークシート関数が多様されていました。
先日何かの手違いでアドイン設定してしまったのが原因でした。
大変ご迷惑をお掛けしました。そして沢山のご回答ありがとうございました。