Why UserForm is "Not Responding" During Run Time in VBA Excel?

Question

I'm very new with VBA Excel and i only know the things as far as i need for this report formatting task.

I'm almost done with my task but when i run the program and start the progress, eventhough it works successfully, GUI is not responding for a minute. I share my code here, is something wrong with it? Can you suggest me any best-practice? I don't want it to freeze because it will look bad to my manager.

Just to make it clear, by "not responding" i mean it freezes on the screen and says "Not Responding" on it's windows frame and when i click on it, it gives a message like this:

*ps: the sheet that i get records has 20997 rows and 7 columns and i make some records to another sheet on same file sized and 20997 lines 23 columns. And my GUI is very simple, it has nothing but a CommandButton that starts the progress.

How can i fix this?

Answer 1

You can prevent the freezing of excel window by putting

DoEvents

inside your loop.

Answer 2

This happens because your procedure is very busy working. For example your Sub TheLoop() is accessing 20995 x 16 times a cell to write on them a string. The interaction VBA with Excel is slow.

There is a couple of things you can do to make the procedure faster.

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

   'Disable'
   Application.EnableEvents = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

   '......  Code'

   'Enable'
   Application.EnableEvents = True
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic

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. for example:

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

EDIT:

Given that the columns between the ones that you modify are only text or empty cells, you can:

1. read the whole range into an array.
2. Then modify the array in the same way you are currently modifying cells.
3. After the modifications are done, dump the whole matrix in the range again.'

For example:

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