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

Alright, i believe i found the best solution for this. (a) :)

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