Блог

У меня есть 2 книги, которые содержат один и тот же макрос. В одной книге макрос выполняется очень быстро, менее чем за секунду. В другом случае для запуска требуется почти 30 секунд. Я использую Excel 2003. Разрывы страниц отключены в обеих книгах. Я не знаю, что может быть причиной того, что один из них работает медленнее другого. Есть идеи?

 Sub viewFirst()
Dim dataSheet As Worksheet, inputSheet As Worksheet, projectID As Long
Dim projectRow As Long, lLastRec As Long, inputLastRow As Long, dataLastRow As Long, x As Long, sh As Shape
Worksheets("Input").Select
ActiveSheet.Protect "", UserInterfaceOnly:=True
Range("a1").Select
ActiveSheet.Pictures.Insert ("working.jpg")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set inputSheet = Worksheets("Input")
Set dataSheet = Worksheets("Database")
With inputSheet
    inputLastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
End With
With dataSheet
    dataLastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
    lLastRec = dataLastRow - 1
End With
With inputSheet
    .Range("currentProject").Value = 1
    projectID = .Range("currentProject").Value
    projectRow = projectID   1
    For x = 1 To inputLastRow
        If Range("b" amp; x).HasFormula Then
            x = x   1
        End If
        If x > inputLastRow Then
            Exit For
        End If
        If Not Range("b" amp; x).HasFormula Then
            .Range("b" amp; x).Value = dataSheet.Cells(projectRow, 2   x)
        End If
    Next x
    .Range("d125").Value = dataSheet.Cells(projectRow, 2   149)
    .Range("d128").Value = dataSheet.Cells(projectRow, 2   150)
    .Range("d131").Value = dataSheet.Cells(projectRow, 2   151)
    .Range("d134").Value = dataSheet.Cells(projectRow, 2   152)
    .Range("d137").Value = dataSheet.Cells(projectRow, 2   153)
    .Range("d140").Value = dataSheet.Cells(projectRow, 2   154)
End With
With ActiveSheet
    For Each sh In .Shapes
        If sh.Type = msoPicture Then
            ActiveSheet.Unprotect ""
            sh.Delete
            ActiveSheet.Protect "", UserInterfaceOnly:=True
        End If
    Next sh
End With
Range("b5").Select
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
  

-РЕДАКТИРОВАТЬ-

osknows, спасибо за ответ. Просто для пояснения, рабочие книги никогда не открываются одновременно, и снова рабочие книги идентичны, за исключением данных в таблице данных — таблица, в которой макрос выполняется медленно, имеет 35 строк x 204 столбца, таблица данных, которая выполняется быстро, имеет 56 строк X 156 столбцов. Я собираюсь выполнить поиск скрытых столбцов или непустых ячеек на листе ввода.

Не видя 2 рабочих книг, трудно сказать. Лучший совет — точно измерять скорость вашего кода с помощью…

В модуле decare

  Public Declare Function GetTickCount Lib "kernel32" () As Long
  

затем в вашем коде между определенными строками кода поместите

 dtStart = GetTickCount
dtline2 = GetTickCount
dtline3 = GetTickCount
dtline4 = GetTickCount 
..
etc
  

количество тактов между dtStart и dtline2 равно dtline2 - stStart и т. д

Также ряд факторов, которые могут замедлить работу:

1. inputLastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1 может содержать много строк, которые кажутся пустыми, но таковыми не являются.

2. Set inputSheet = Worksheets("Input") amp; Set dataSheet = Worksheets("Database") могут быть большие сложные диапазоны

3. For Each sh In .Shapes может включать множество повторяющихся фигур друг над другом, которые выглядят идентично

4. У вас есть неопределенные диапазоны и таблицы, которые, если у вас открыто несколько рабочих книг и вы используете их во время выполнения кода, то рабочие книги / листы / диапазоны явно не определены. (Например, диапазон в зависимости от диапазона) Приобретите привычку использовать полный путь к диапазону Filepath / Workbook / Sheet / Range или cell и т.д., Используя With инструкции

например

 With ThisWorkbook
  With SheetXYZ
     With .range("XYZ1")

     End with
  End With
End With
  

или

  With ThisWorkbook
  With SheetXYZ.range("XYZ1")
     .formula = "=Now()"

  End With
End With
  

Также ознакомьтесь с страницами Excel этого удобного сайта

На более медленной машине удалите все надстройки. Если у вас есть надстройка с глобальным событием изменения, это будет срабатывать каждый раз при изменении любого рабочего листа и может быть причиной замедления. Вы пишете в электронную таблицу довольно много, поэтому это будет называться «много».

Вместо записи по ячейкам, рассмотрите возможность построения массива (2 измерения, нижняя граница равна 1) и запишите все данные в ячейку одним большим махом. Вот пример того, как это работает

 Sub WriteOnce()

    Dim aReturn() As Double
    Dim i As Long, j As Long

    Const lLASTROW As Long = 10
    Const lLASTCOL As Long = 5

    ReDim aReturn(1 To lLASTROW, 1 To lLASTCOL)

    For i = 1 To lLASTROW
        For j = 1 To lLASTCOL
            aReturn(i, j) = Rnd
        Next j
    Next i

    Sheet1.Range("A1").Resize(UBound(aReturn, 1), UBound(aReturn, 2)).Value = aReturn

End Sub
  

Поскольку я обращаюсь к рабочему листу только один раз, любые обработчики событий будут запускаться только один раз.