[[20201029123856]]　『VBAの処理速度』（ミサイル）　ページの最後に飛ぶ

『VBAの処理速度』（ミサイル）

こちらのコードを使って、統計データから値を反映させる処理を行っていますが、処理時間が30~40秒ほどかかってしまい、もう少し速くなるようにコードの改善をしたいです。何かいい方法はございませんでしょうか・・・？

Dim k As Long, l As Long, j As Long, i As Long, o As String

    k = ActiveSheet.Cells(11, 3)
    l = ActiveSheet.Cells(10, 3)
    o = ActiveSheet.Cells(9, 4)
    For i = 0 To 11
    For j = 0 To 7
    ActiveSheet.Cells(13 + i, 5 + j) = ExecuteExcel4Macro("'ファイルパス名】\[ファイル名.xls]" & o & "'!R" & k + i & "C" & l + j)
    Next j
    Next i

＜使用 Excel：unknown、使用 OS：unknown ＞

普通にブックを開いたほうが速いと思いますよ。
（tkit） 2020/10/29(木) 12:51

統計データのシート数がとデータ数が多いので、マクロでデータ処理したいところではございます・・・。
（ミサイル） 2020/10/29(木) 13:11

 tkitさんのおっしゃっているのは、VBAの中で目的のシートを開き、データを転記(コピー)するということでしょう。
 　
（tora） 2020/10/29(木) 13:29

ExecuteExcel4Macro を使用しているのはどうして?
（WAKAR） 2020/10/29(木) 13:50

普通にブックを開く＝Workbooks.Open("ファイル名") とご存じなければ、なぜと聞いても答えらのでは。

Workbooks.Openを使った方が一般的なので、検索して出てきたサンプルを参考に書き直してみてください。

数秒くらいにはなると思います。
（通りすがり） 2020/10/29(木) 14:00

取り合えず、以下のコードで試して何秒だったか
教えてください。

データ元のシート内容を配列で取得すれば多少速くなりそうですが、
ループ数が少ないので、100msecも変わらないかもです。

動作確認はしておりませんので、あしからず。

 Sub sample()
     Dim ws As Worksheet
     Dim openBook As Workbook
     Dim openPath As String
     Dim pasteArr As Variant
     Dim k As Long, l As Long, j As Long, i As Long, o As String
     Set ws = ActiveSheet
     With ws
         k = .Cells(11, 3)
         l = .Cells(10, 3)
         o = .Cells(9, 4)
     End With
     '▼任意のフルパスを入れてください
     openPath = ""
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     Set openBook = Workbooks.Open(Filename:=openPath, ReadOnly:=True)
     ws.Parent.Activate
     ReDim pastarr(11, 7)
     With openBook.Worksheets(o)
         For i = 0 To 11
             For j = 0 To 7
                 pasteArr(i, j) = .Cells(k + i, l + j).Value
             Next j
         Next i
     End With
     ws.Cells(13, 5).Resize(UBound(pasteArr, 1) + 1, UBound(pasteArr, 2) + 1).Value = pasteArr
     Erase pasteArr
     Set ws = Nothing
     openBook.Saved = True
     openBook.Close
     Set openBook = Nothing
 End Sub

（tkit） 2020/10/29(木) 15:44

ExecuteExcel4Macroを使用しているのは、統計データファイルを非表示のまま転記したいという意図がございます。
（ミサイル） 2020/10/29(木) 16:12

画面更新を抑止すればその点は問題になりません。
（γ） 2020/10/29(木) 16:15

wb.Windows(1).Visible = False とか。
（通りすがり） 2020/10/29(木) 16:29

＞ExecuteExcel4Macroを使用しているのは、統計データファイルを非表示のまま転記したいという意図がございます。

ExecuteExcel4Macroでアクセスする度、ブックを開いているようなものなので、時間が掛かるのは当たり前です。
実行時間短縮は諦めてください。
以上
（tkit） 2020/10/29(木) 16:51

これでは、だめですか。

    With Cells(13, 5).Resize(8, 12)
        .Formula = "='ファイルパス名】\[ファイル名.xls]" & o & "'!" & Cells(k, l).Address(0, 0)
        .Value = .Value
    End With

（マナ） 2020/10/29(木) 17:32

 いままでの経験では、マナさんの案が「ExecuteExcel4Macro」を使うより50倍くらい速いです。

 30～40秒なら単純計算で、たった１秒で終わります。

（半平太） 2020/10/29(木) 19:18

 (1)
 質問者さんの提示されたコードは、縦12行横8行に書き込む処理ですが、
 それが30～40秒かかるというのは時間が掛かりすぎの印象です。
 ワークシートに多数の式が書き込まれていて、
 書き込みによって式の再計算でも起きているのでしょうか。
 1秒もかからないはずですが。

 普通に開いて配列にとりこみ、それを一括して書き込む方式と比べると、
 今の程度のセル範囲の大きさでは、両者は余り差はないですが、
 多くなれば、Excel4Macroのほうが断然遅くなります。
 それは、一セル毎に書き込むというのが、とても非効率なためです。

 (2)
 Excel4Macroに関しては、
 ブックを開かずにセル値を取得（ExecuteExcel4Macro,Excel.Application）
https://excel-ubara.com/excelvba5/EXCELVBA242.html
 が参考になるでしょう。
 その結論では、
 >余程の事情が無い限り、ExecuteExcel4Macroは積極的に使うものではないでしょう。
 とされています。

 どうしても、Excel4macroに魅力を感じて、この延長線上でスピードアップを求めるなら、
 それは「解無し」ということです。（既に指摘がありました）

 (3)
 現在の範囲の大きさでは少なくとも、
 マナさんの計算式埋め込み方式が最速で、他と二つと比べて1/30程度の時間ですむはずです。
 可能性があると思います。

 # ただ私見では、普通に開いて、速度を求めるなら配列利用などを工夫するという方式を
 # 中心に考えるということでいいような気がします。
 # それは計算式を構成するのが手間という私の個人的な事情によりますが。

（γ） 2020/10/29(木) 20:00

コメント返信：

[ 一覧(最新更新順) ]

[[20201029123856]] 『VBAの処理速度』（ミサイル） ページの最後に飛ぶ

[[20201029123856]]　『VBAの処理速度』（ミサイル）　ページの最後に飛ぶ