“Windows Explorer Not Responding” in VBA Excel Runtime for Large Data Set

I was supposed to format an excel file for specific report requirements and this was requiring to code in VBA. First I divided task to some mini-tasks then did some googling. Code was giving 100% correct output but it had a little flaw: It was freezing while run time. It was freezing on the screen and says “Not Responding” on it’s windows frame and when i click on it, it gives a message like this:

winexp

The excel sheet that i get records had 20997 rows and 7 columns and i make some records to another sheet on same file sized and 20997 lines 23 columns. This freezing problem was happening because my procedure was very busy working. I can’t post the original code because it’s already replaced with the new one, But; my Sub TheLoop() was accessing 20995 x 16 times a cell to write on them a string. Naturally, VBA’s interaction with excel took some time.

I tried couple of things to make the procedure faster according to advice of an colleague.

1. Disabling events handlers, screen updating and calculations before you run your procedure. At the end of the procedure restore the settings again. Like this:


'Disabling event handlers, screen updating and calculations'
 Application.EnableEvents = False
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

'some code comes here'

'Enabling event handlers, screen updating and calculations'
 Application.EnableEvents = True
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic

This helped a little bit but screen freeze still existed.

2. You can optimize the Sub TheLoop. Instead of writing immediately on the cells, write the values inside an array. After the array is full with the values, assign the values of the array to the range that you need. Like this:

Dim ResultValues() As String
Dim j As Long

ReDim ResultValues(2 To 20997, 1 To 3)

For j = 2 To 20997
 ResultValues(j, 1) = "New Defect"
 ResultValues(j, 2) = "3"
 ResultValues(j, 3) = "2"
Next j

With ThisWorkbook.Worksheets("myWorksheet")
 .Range(.Cells(2, 3), .Cells(20997, 5)) = ResultValues
End With

Luckily the columns between the ones that i modified were only text or empty cells. (some of the columns were not meant to be modified) So;

  • I read the whole range into an array.
  • Then modified the array in the same way you are currently modifying cells.
  • After the modifications, dumped the whole matrix in the range again.

For instance:

Sub TheLoop()
Dim arrRangeValues() as Variant
Dim j as Long

arrRangeValues= Range("A2:V20997").Value2

For j = 2 To 20997
 arrRangeValues(j, 1) = "Defect" 'Cells(row_index , column_index)'
 arrRangeValues(j, 3) = "New Defect"
 arrRangeValues(j, 4) = "3" ' this one also might be empty'
 arrRangeValues(j, 5) = "2" ' this one also might be empty'

 arrRangeValues(j, 7) = "Name Surname"
 arrRangeValues(j, 8) = arrRangeValues(j, 7)
 arrRangeValues(j, 16) = arrRangeValues(j, 7)
 ...
 arrRangeValues(j, 10) = " http://SERVER_NAME:8888/PROJECT_NAME/ "
Next j

Range("A2:V20997").Value2 = arrRangeValues
End Sub

I decided to try second advise, but eventhough i don’t know about Dim and ReDim in VBA, it seem like a necessary requirement a write my columns next-to-next in here ReDim ResultValues(2 To 20997, 1 To 3)  but there are some other columns which are including nothing but a title and most of the those columns are between the columns that i record in TheLoop subroutine. And i did’t want to write extra codes for moving columns to between others. 3 of the remaining columns were getting data from another excel sheet.

Then i decided to make more research, and found the perfect solution. Instead of using for loop in TheLoop subroutine, i removed the loop and changed it as in below. That makes it incredibly faster when i compare it with my first code eventhough i didn’t disable event properties, and now it’s not freezing.🙂

Sub TheLoop()

 Cells(2, 1).Resize(20996) = "Defect"
 Cells(2, 3).Resize(20996) = "New Defect"
 Cells(2, 4).Resize(20996) = "3"
 Cells(2, 5).Resize(20996) = "2"
 Cells(2, 7).Resize(20996) = "Name Surname"
 Cells(2, 8).Resize(20996) = "Name Surname"
 Cells(2, 9).Resize(20996) = "FALSE"


 Cells(2, 10).Resize(20996) = " http://SERVER_NAME:8888/PROJECT_NAME/ "


 Cells(2, 12).Resize(20996) = "Software Quality"
 Cells(2, 13).Resize(20996) = "Unsigned"
 Cells(2, 14).Resize(20996) = "Software Quality"
 Cells(2, 15).Resize(20996) = "1"
 Cells(2, 16).Resize(20996) = "Name Surname"
 Cells(2, 18).Resize(20996) = "Software Quality"
 Cells(2, 20).Resize(20996) = "Development"
 Cells(2, 22).Resize(20996) = " TYPE YOUR MODULE'S NAME TO HERE"

End Sub

Yorum bırakın

Filed under VBA

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Connecting to %s