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:
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"
.Range(.Cells(2, 3), .Cells(20997, 5)) = ResultValues
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.
Dim arrRangeValues() as Variant
Dim j as Long
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/ "
Range("A2:V20997").Value2 = arrRangeValues
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. :)
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"