CODEWITHSUNDEEP
vbauserform
Mohsen
Mohsen

Reputation: 175

VBA userform is "Not Responding" while macro is running

Why my userform shows "Not Responding" when I run the following code? I have been trying to resolve it, but it is not resolved yet.

Actually it works sometimes. I think the problem has something to do with the screen updating.

enter image description here

' The input button in Sheet1
Sub Rectangle1_Click()

    'Remember time when macro starts
    StartTime = Timer

    ' To improve speed and performance
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ' Show the userform
    UserForm1.Show vbModeless
    UserForm1.Label1.Caption = "Calculation in progress ... " & vbNewLine & "Please be patient"
    UserForm1.Label1.Font.Size = 12
    UserForm1.Top = (Application.Height / 2) - (UserForm1.Height / 2)
    UserForm1.Left = (Application.Width / 2) - (UserForm1.Width / 2)
    UserForm1.CommandButton1.Visible = False
    UserForm1.Repaint

    Call Insert_RawData

    'Determine how many seconds code took to run
    SecondsElapsed = Round(Timer - StartTime, 2)
    UserForm1.Label1.Caption = "This code ran successfully in " & SecondsElapsed & " seconds"
    UserForm1.CommandButton1.Visible = True

    ' Return back to the original settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationSemiautomatic

End Sub

Upvotes: 1

Views: 1288

Answers (1)

JMichael
JMichael

Reputation: 564

It could be a number of things. If the issue is intermittent like you mention, then in all likelihood the program IS working, and Windows is just labeling it non-responsive because the program is working too hard to respond to the OS.

The likely issue is probably one, or a combination, of the following:

  • The amount of data being processed by Insert_RawData
  • The amount of data in the workbook(s) open when the macro is running
  • Insert_RawData is encountering an error due to a bad/unhandled value in one of the cells it works with (less likely)

Some suggestions to try and narrow the source down:

  • If there's a way to consistently run so that you get the "Non-Responsive" in the Excel window, insert a break point at the call to Insert_RawData and watch it run to see if it's hitting an error
    • Alternatively, try to put some error checks into Insert_RawData and break points on the code handling the case that the error check fails
  • Capture the amount of data processed (bytes, cells, whatever is easiest) during each run of the macro along with the run time & see if there's a threshold your hitting (ex. <= 1 GB runs fine, but >1GB and the application looks frozen)

Unless you are hitting an error in Insert_RawData though, it's likely that the macro WILL complete, it just might take a really long time.

Upvotes: 1

Related Questions