Greetings

I wrote a macro for excel (in VBA) that processes the data and puts it in a table (on a separate sheet).

The table is quite large, so the macro works for a relatively long time. For some reason it seems to me that this is due to the fact that after each entry in the cell, excel performs some actions on redrawing the sheet and this is what significantly reduces the speed of the macro.

Is it really so? And if so, how best to optimize the process? I thought about blocking a sheet (redrawing) until all the data are entered, and only after that unlock the sheet.

Am I right? And if so, how is this better implemented?

  • how best to optimize the process? Option 1 - turn off screen updates at the time of making changes ( Application.ScreenUpdating=False ), and do not forget to turn it back on. Option 2 - accumulate changes in a two-dimensional array, and then copy it in one line to the desired range. However, both are possible at the same time. - Akina
  • The first option did not help: ( - Zhihar
  • one
    excel-vba.ru/chto-umeet-excel/… - slippyk
  • @Zhihar, a lot of factors. Reversal to leaf objects is slow, you need to work with arrays. Recalculating the formulas, changing the formatting ... It is possible that you turn off ScreenUpdating in the wrong place ... Show the code. - vikttur

1 answer 1

I gave a very useful link to slippyk , I recommend to get acquainted with it.

To what is written there, I add the following from myself:

  1. It is useful for performance not only to read / write cells using a two-dimensional array. More universal advice: avoid, if possible, any calls in the loop to VBA library objects. Worksheet , Range , Borders , etc. are all COM objects, and each call to their methods / properties is an additional overhead.

Accordingly, if you write cells with an array in one call .Range(...).Value = array , and then you want to format the table, setting the number formats, cell borders, etc., then process the cells with ranges too. If the formatting for different columns of the table should differ, treat each column as one range. But in no case in large tables do not process the cells one by one.

  1. Turning off the screen update, be sure to ensure that the Excel operation mode is restored so that an error during the macro execution will not lead the user to an empty application window. It will close Excel through the task manager and is not well expressed to the programmer :) Use the "On Error" operators.

Below I enclose a sample code that demonstrates the speed of filling a sheet with a million values ​​(a table of 10,000 lines per 100 columns). To run the code:

  • create an Excel workbook and two sheets in it

  • open the VBA editor window

  • rename sheets to "Sheet_1" and "Sheet_2"

  • on Sheet_1, you can enter several numeric values ​​(by entering a string or date, you can later see how the macro responds to the error)

  • create a module and insert macro code into it

  • run the macro (I have about 1.5 seconds) and go to the Excel window to see the results

 Option Explicit Public Sub Test() Dim i As Long, j As Long, sum As Long Dim iLastRow As Long, iLastCol As Long Dim src_data As Variant Dim dst_data() As Variant ' установка ΠΎΠ±Ρ€Π°Π±ΠΎΡ‚Ρ‡ΠΈΠΊΠ° ошибок On Error GoTo lbl_error ' ΠΎΡ‚ΠΊΠ»ΡŽΡ‡Π΅Π½ΠΈΠ΅ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΉ, способных Π·Π°ΠΌΠ΅Π΄Π»ΠΈΡ‚ΡŒ ΠΈΠ»ΠΈ ΠΏΡ€ΠΈΠΎΡΡ‚Π°Π½ΠΎΠ²ΠΈΡ‚ΡŒ Ρ€Π°Π±ΠΎΡ‚Ρƒ макроса Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False ' Ρ‡Ρ‚Π΅Π½ΠΈΠ΅ исходных Π΄Π°Π½Π½Ρ‹Ρ… Π² Π΄Π²ΡƒΠΌΠ΅Ρ€Π½Ρ‹ΠΉ массив Variant With Sheet_1 iLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1 iLastCol = .UsedRange.Column + .UsedRange.Columns.Count - 1 src_data = .Range(.Cells(3, 3), .Cells(iLastRow, iLastCol)).Value End With ' какая-Π½ΠΈΠ±ΡƒΠ΄ΡŒ ΠΎΠ±Ρ€Π°Π±ΠΎΡ‚ΠΊΠ° исходных Π΄Π°Π½Π½Ρ‹Ρ… (Π½Π΅Π²Π°ΠΆΠ½ΠΎ какая) sum = 0 For i = LBound(src_data, 1) To UBound(src_data, 1) For j = LBound(src_data, 2) To UBound(src_data, 2) sum = sum + CLng(src_data(i, j)) Next j Next i ' Ρ„ΠΎΡ€ΠΌΠΈΡ€ΠΎΠ²Π°Π½ΠΈΠ΅ Π²Ρ‹Ρ…ΠΎΠ΄Π½ΠΎΠ³ΠΎ массива Π΄Π°Π½Π½Ρ‹Ρ… 10 000 * 100 = 1 000 000 Π·Π½Π°Ρ‡Π΅Π½ΠΈΠΉ ReDim dst_data(0 To 9999, 0 To 99) For i = 0 To 9999 For j = 0 To 99 dst_data(i, j) = sum + i + j Next j Next i ' запись Π²Ρ‹Ρ…ΠΎΠ΄Π½ΠΎΠ³ΠΎ массива Π² мСсто назначСния With Sheet_2 .Range(.Cells(2, 2), .Cells(10001, 101)).Value = dst_data End With ' Ρ„ΠΎΡ€ΠΌΠ°Ρ‚ΠΈΡ€ΠΎΠ²Π°Π½ΠΈΠ΅ Π²Ρ‹Ρ…ΠΎΠ΄Π½ΠΎΠΉ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ With Sheet_2 ' Ρ„ΠΎΡ€ΠΌΠ°Ρ‚Ρ‹ ΠΏΠ΅Ρ€Π²Ρ‹Ρ… Ρ‚Ρ€Ρ‘Ρ… ΠΊΠΎΠ»ΠΎΠ½ΠΎΠΊ .Range(.Cells(2, 2), .Cells(10001, 2)).NumberFormat = "0.00" .Range(.Cells(2, 3), .Cells(10001, 4)).HorizontalAlignment = xlRight ' Π³Ρ€Π°Π½ΠΈΡ†Ρ‹ Π½Π° всю Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ With .Range(.Cells(2, 2), .Cells(10001, 101)) Call .BorderAround(xlContinuous, xlThin) With .Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlHairline End With With .Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlHairline End With End With End With ' ΠΏΠ΅Ρ€Π΅ΠΏΡ€Ρ‹Π³Π½ΡƒΡ‚ΡŒ Π±Π»ΠΎΠΊ ΠΎΠ±Ρ€Π°Π±ΠΎΡ‚ΠΊΠΈ ошибок GoTo lbl_finish ' ΠΎΠ±Ρ€Π°Π±ΠΎΡ‚Ρ‡ΠΈΠΊ ошибок lbl_error: Call MsgBox("Ошибка " & CStr(Err.Number) & ": " & Err.Description, vbCritical) On Error Resume Next ' Π²ΠΎΡΡΡ‚Π°Π½ΠΎΠ²ΠΈΡ‚ΡŒ Ρ€Π΅ΠΆΠΈΠΌ Ρ€Π°Π±ΠΎΡ‚Ρ‹ Excel lbl_finish: Application.DisplayAlerts = True Application.EnableEvents = True Application.ScreenUpdating = True ' ΠΏΠΎΠΊΠ°Π·Π°Ρ‚ΡŒ сформированный лист Sheet_2.Activate End Sub
  • 1. Error handler should not be used without thinking. Only if absolutely nothing at all. It is necessary to bypass checks in the code for possible errors. 2. It is undesirable to disable events and messages unnecessarily, just like a sheet recalculation (this is not in the code). 3. Updating the screen is not necessary to turn off at the beginning of the procedure - it is possible before the line where the list is being accessed (formatting, data upload ...) - vikttur
  • vikktur , thanks for the comment! I completely agree with the 3rd point, but I would like explanations for 1-2. I did not notice a noticeable effect of the handler on speed. Can you tell me in which scenarios "On Error" interferes and what exactly? Regarding the disabling of messages and events (DisplayAlerts) - from my experience this makes sense: for example, when combining cells containing different values, you can get the message "In the merged cell, only ... is saved." And the cells are combined, but the unclamped message suspends the macro until the user closes the message window - velial
  • This is what he wrote about: it is necessary to provide in the code. Disable messages - and skip what you need. * If Merge * - and go further. Events - disable if they are processed and if the macro touches these ranges. Error handler Example: open a book and take data from the specified sheet. If the wrong way or no sheet - an error. You can apply a handler. But you can check the path of Dir (sPath, vbDirectory) and the presence of a fox in the family of Worksheets ... I do not dissuade from using a handler, but I do not advise you to use it wherever you can squeeze it. - vikttur