Блог

Главная - Вопросы по программированию - Замедление работы VBA при создании новых книг

Замедление работы VBA при создании новых книг

#excel #vba

Вопрос:

Я создал простой макрос для фильтрации некоторых данных, копирования их в новую книгу, сохранения этой книги и закрытия этой книги.

Я намереваюсь использовать это для больших наборов данных (где исходный набор данных может составлять более 10000 строк), однако при тестировании с 25 строками это занимает ОЧЕНЬ много времени (т. е. Более нескольких минут). Я новичок в VBA и мне было интересно, почему это работает так медленно?

Примечание: Я понимаю, что использование Select может повлиять на производительность, однако я не думаю, что здесь этого можно избежать?

Код ниже:

 Sub Macro7()
'
' Macro7 Macro
'

'
 Application.ScreenUpdating = False
 Application.Calculation = xlManual
 
 Dim cl As Range, rng As Range
 
 Set rng = Range("G3:G5")
 
 For Each cl In rng
        ActiveSheet.Range("$A$2:$D$25").AutoFilter Field:=2, Criteria1:=cl.Value
        Cells.Select
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste
        
        Dim Path1 As String
        Dim Path2 As String
        Dim myfilename As String

        Path1 = "C:UsersnameDocuments"
        Path2 = cl.Value
        ActiveWorkbook.SaveAs Filename:=Path1 amp; "/" amp; Path2, FileFormat:=xlNormal
        
        ActiveWorkbook.Close
        
        Windows("doc.xlsm").Activate
        Range("A1").Select
        
    Next cl
    
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
    
End Sub

Комментарии:

1. Ну Cells.Select и Selection.Copy можно упростить до Cells.Copy . Здесь нет ничего слишком сложного, но, возможно, вы могли бы ограничить копию только используемыми ячейками, а не всем листом. Не уверен, в чем заключается узкое место.

2. Хммм, я новичок в VBA, но я не уверен, что это создаст какие-либо проблемы со временем?

3. Вы думали о том, чтобы иметь лист в исходной книге, куда вы можете временно поместить данные, а затем экспортировать этот отдельный лист в файл CSV? Это может ускорить процесс, так как вам не придется каждый раз создавать книгу, затем копировать данные, затем сохранять и закрывать новую книгу!

Ответ №1:

Медлительность здесь была результатом копирования всего листа каждый раз (1 миллион или около того строк) — я определил диапазон копирования, и это устранило проблему медленности

Ответ №2:

Вы уверены, что можете избежать активации и выбора, мы просто должны использовать переменные:

 Sub Macro7()
     Application.ScreenUpdating = False
     Application.Calculation = xlManual
     
     Dim twkb As Workbook
     Dim tws As Worksheet
     
     Dim ws As Worksheet
     Set ws = ThisWorkbook.ActiveSheet
     
     Dim rng As Variant
     Dim cpyrng As Range
     
     rng = Range("G3:G5").Value
     
     Dim fltrng As Range
     Set fltrng = ws.Range("$A$2:$D$25")
    
     Dim i As Long
     For i = LBound(rng, 1) To UBound(rng, 1)
            fltrng.AutoFilter Field:=2, Criteria1:=rng(i, 1)
            Set cpyrng = ws.Range("$A$1").Resize(fltrng.Rows.Count   1, fltrng.Columns.Count).SpecialCells(xlCellTypeVisible)
            
            Set twkb = Workbooks.Add
            Set tws = twkb.Worksheets(1)
            
            cpyrng.Copy tws.Range("A1")
            
            Dim Path1 As String
            Dim Path2 As String
            Dim myfilename As String
    
            Path1 = "C:UsersnameDocuments"
            Path2 = rng(i, 1)
            twkb.SaveAs Filename:=Path1 amp; "/" amp; Path2, FileFormat:=xlNormal
            
            twkb.Close
        Next i
        
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Обратите внимание, что это может не повысить производительность. Горлышко бутылки будет использоваться для создания и сохранения рабочей книги.

Метки: