[[20201118192655]]　『ListView1読み込みスピードアップするには』（nobu）　ページの最後に飛ぶ

『ListView1読み込みスピードアップするには』（nobu）

読み込みデータの容量が多いのか読み込みに時間がかかります。
読み込み時間を短縮させる方法は、ありませんか？
アドバイス　よろしくお願いします。
以下マクロです
Private Sub UserForm_Activate()

    Dim WB As Excel.Workbook
    Dim sh As Excel.Worksheet
Application.ScreenUpdating = False

    Dim selIdx As Long
    selIdx = Val(Range("Y1"))
    If selIdx = 0 Then selIdx = 4

    Dim DBpath As String
    Dim i As Long
    With ListView1
        .View = lvwReport           ''表示
        .LabelEdit = lvwManual      ''ラベル編集
        .HideSelection = False      ''選択の自動解除
        .AllowColumnReorder = True  ''列幅の変更許可
        .FullRowSelect = True       ''行全体選択
        .Gridlines = True           ''グリッド
        ''列見出し
        .ColumnHeaders.Add , "_hinichi", "月日", 75
        .ColumnHeaders.Add , "_siire", "取引先", 100
        .ColumnHeaders.Add , "_genba", "件名", 110
        .ColumnHeaders.Add , "_tyuuban", "注番", 60
        .ColumnHeaders.Add , "_siharai", "金額", 90
        .ColumnHeaders.Add , "_sousatu", "相殺金額", 70
        .ColumnHeaders.Add , "_tekiou", "適応", 200
        .ColumnHeaders.Add , "_tuuti", "通知", 40
     End With

DBpath = "D:\Office\Item\支払台帳\支払台帳2020.xlsm"

    If Not m_ShownFlag Then
    Set WB = Workbooks.Open(DBpath)
    Set sh = WB.Worksheets("台帳")
        With ListView1
            For i = 4 To Range("A" & Rows.Count).End(xlUp).Row

            With .ListItems.Add(Text:=Cells(i, 1).Value)
                .SubItems(1) = Cells(i, 2).Value
                .SubItems(2) = Cells(i, 3).Value
                .SubItems(3) = Cells(i, 5).Value
                .SubItems(4) = Format(Cells(i, 9).Value, "#,##0")
                .SubItems(5) = Format(Cells(i, 14).Value, "#,##0")
                .SubItems(6) = Cells(i, 16).Value
                .SubItems(7) = Cells(i, 15).Value
             End With
ListView1.ColumnHeaders.Item(2).Alignment = lvwColumnLeft
ListView1.ColumnHeaders.Item(3).Alignment = lvwColumnLeft
ListView1.ColumnHeaders.Item(4).Alignment = lvwColumnCenter
ListView1.ColumnHeaders.Item(5).Alignment = lvwColumnRight
ListView1.ColumnHeaders.Item(6).Alignment = lvwColumnRight
ListView1.ColumnHeaders.Item(7).Alignment = lvwColumnLeft
ListView1.ColumnHeaders.Item(8).Alignment = lvwColumnCenter
          Next
         '年月日の部分は初めから降順にする
         .SortKey = .ColumnHeaders("_hinichi").INDEX - 1
         .SortOrder = lvwDescending
         .Sorted = True
    WB.Close False
        If .ListItems.Count >= selIdx Then
            .ListItems(selIdx).Selected = True
            ListView1.SetFocus
                End If
         End With
      Application.ScreenUpdating = True
    End If
End Sub

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

どのくらいの時間がかかるんでしょうか。
なにせ、そのコントロールは大昔に作成されたもので、
更新などはされていないものですから、性能は低いかもしれませんね。
値のセットは今の方法以外は無いんじゃないですか？

なお、別件ですが、気づきましたので。
支払台帳2020.xlsmの操作にあたって、シートの指定がされていませんが、
シートを指定した形で、セルの値を取得したほうがよいかと思います。
アクティブシートのままだと、気持ちが悪いです。
（今はシートが一つしかなくても、今後は保証できませんし。）
（γ） 2020/11/18(水) 20:44

 データが何行あるのかわかりませんが、
 ブックを開くのに時間がかかっているならどうしようもないと思います。

 それでも、細々時間を削ろうとするなら、

 ListView1.ColumnHeaders.Item(2).Alignment = lvwColumnLeft
 これら一連のステートメントは、For Nextループの中にある必要はないと思います。

 Cellにいちいちアクセスしないで、配列に読み出しておいてから
 配列からListViewに値をセットした方が、早くなります。

 多分ボトルネックは、ファイルを開くところだと思うので、
 UserForm_Activate だと頻繁に読み出すことになるので、
 UserForm_Initialize で一回だけで済むようにしたり、

 ファイルを開いたときに同時に"支払台帳2020.xlsm"を開いて
 隠しシートに必要なデータをコピーしておく

 などが考えられると思います。
（´・ω・`） 2020/11/18(水) 22:43

（γ）さん
ご返事ありがとうございます。
シートの指定ですか、やってみます。

（´・ω・`）さん
ありがとうございます。
以下のご指摘ですがもう少し詳しく
サンプルマクロなど、お願いできますか？
マクロ苦手なので・・・
＞ ListView1.ColumnHeaders.Item(2).Alignment = lvwColumnLeft
＞これら一連のステートメントは、For Nextループの中にある必要はないと思います。
＞ Cellにいちいちアクセスしないで、配列に読み出しておいてから
＞配列からListViewに値をセットした方が、早くなります。

（nobu） 2020/11/19(木) 04:19

 Alignment をセットしているステートメントは For  Next ループの外にだしてもいいですね？
 ColumnHeaders.Add している With の中にあってもよいと思います。

 配列については
http://officetanaka.net/excel/vba/speed/s11.htm
 を参考にしてください。
 読み込みだけなので、速度向上は微々たるものだと思います。
（´・ω・`） 2020/11/19(木) 09:34

（´・ω・`）さん
ありがとうございました。
速度、限界ですかね・・・
（nobu） 2020/11/19(木) 15:49

 前にも書いてますが
 > 多分ボトルネックは、ファイルを開くところだと思うので、
 と思います。

 本気でやるなら、時間を計測してみたらどうでしょう。
 データをコピーしておいて、いちいち別ファイルを開かないようにするのがいいと思います。

 更新のタイミングがよく分かりませんが。
（´・ω・`） 2020/11/19(木) 17:06

（´・ω・`）さん
ありがとうございます。
以下は、どのようにすれば可能ですか？
ご教授お願いします。
アクティブなBOOKにコピペしておくのですか
別ファイルのデータ量がたしかに時間がかかるようになったのは
日々の積み重ねで多くなってきてからのような気がします。

データをコピーしておいて、いちいち別ファイルを開かないようにするのがいいと思います。（nobu） 2020/11/19(木) 19:58

 運用がわからないので、ほんとに早くなるかどうかは、不確かですが、

 UserFormを開くたびに　支払台帳2020.xlsm を開いているので、
 UserFormを表示したり閉じたりを繰り返す場合は、
 ・UserForm_Initialize で ListViewを初期設定する。
 ・UserFormを閉じるときは Unlooad しないで、Hideするだけにする。
 のが有効だと思います。

 また、支払台帳2020.xlsm と同じデータを、自分のシートに持てば、
 支払台帳2020.xlsm　を開く必要がなくなるので、その分のオーバーヘッドがなくなると思いますが、
 データの整合性をどう保つかが問題になります。
 それは、これらの２つのブックをどう運用しているかを知らないと判断できないので、
 私にはわかりません。
 ご自分で考えないといけない課題だと思います。

 提示された　UserForm_Activate だけを見る限り、
 技術的には部分的な速度向上は、見込みが少ないので、
 全体的な運用を見直さないと無理ですねという話をしています。
 ちょいちょいっと、サンプルコードがかけるようなことではありません。
（´・ω・`） 2020/11/20(金) 09:13

nobuさんの言ってる「読み込みに時間がかかります」が

 昔は一瞬で開いたのが今は1秒かかる
なのか
 昔は1秒で開けたのが今は10秒かかる
なのか
 昔は10秒で開けたのが今は1分かかる
なのか。

速度のチューニングの話なのに、現状も目標も妥協ラインも明示されて無いため、欲しい回答は得られないと思います。
もし1分→10秒が狙いなのに、1秒→一瞬を目的としたチューニング方法は全く効果がないですからね。

また、「容量が多いのか」については、数件しかないデータを用意して速度を計測して、

 100件のとき何秒かかる
 10000件のとき何秒かかる
だから、「容量が増えたので遅くなった」といえます。

ソースコードを途中からバッサリ切り落として、どこまでなら早く、どこから先は遅いのか、1行づつ増やして試してみれば、遅くしている原因のプログラムは分かります。

遅くなっている原因を特定するところから、初めてはどうでしょう。
（通りすがり） 2020/11/20(金) 09:44

（´・ω・`）さん
詳しい説明ありがとうございました。
＞また、支払台帳2020.xlsm と同じデータを、自分のシートに持てば、
やはり、こちらの説明のように自分のシートが一番ですかね・・・
検討してみます

（通りすがり）さん
ありがとうございました。
こつこつやってみます。
（nobu） 2020/11/20(金) 13:11

コメント返信：

[ 一覧(最新更新順) ]

[[20201118192655]] 『ListView1読み込みスピードアップするには』（nobu） ページの最後に飛ぶ

[[20201118192655]]　『ListView1読み込みスピードアップするには』（nobu）　ページの最後に飛ぶ