I want to overcome the situation where the screen hardens until the Excel VBA procedure is finished.

Asked 1 months ago, Updated 1 months ago, 2 views

Procedure Description:
There are more than 100 lines of data on the sheet.The content of the ABC column is keyed to go to SQLServer to retrieve data one line at a time and store the results in the column H.
Looped a collection of rows with ForEach statements.The connection to SQLServer is before the loop starts, and it's like repeating the record set retrieval and closing in the loop.

Problem:
Regardless of whether the Application.ScreeUpdating is TRUE, I would like to do something about the situation where I can hardly get a screen response until the processing is complete.
"Nearly available" means that the ProgressBar on UserForm indicates progress, but the screen hardens in the middle and does not indicate progress.
At the end of the process, the programming message box appears, so you know the completion just in case.

It can't be helped because of the heavy handling, and I've neglected this issue until now
Is there a solution?

I don't even know if it's happening because the memory is eating (I don't know how to check it), but if that's the case, if you can open the memory in a timely manner,
Will the screen reflect smoothly?

I was able to solve various problems on this site, so I was wondering if I could solve this problem.Thank you for your cooperation.

===Addition===

UserForm1.ProgressBar1.Value=0
UserForm1.ProgressBar1.Min=0
UserForm1.ProgressBar1.Max=100

Range ("A9999").Select
Selection.End(xlUp).Select
MaxLine= ActiveCell.Row
Set DataSheet= Range ("A2:A" & MaxLine)

For Each x In DataSheet
    W_LCnt = W_LCnt+1

        strSQL="SELECT XXXXX"   
        RS. OpenstrSQL, Conn, adOpenStatic, adLockReadOnly, -1

        If RS.EOF Then
            x.Offset(0,5).Value="None"
        Else
            RS.MoveFirst
            x.Offset(0,5).CopyFromRecordset RS
        End If
        RS.Close
'#        DoEvents
        UserForm1.ProgressBar1.Value=W_LCnt/100
Next x

vba

2022-09-30 13:53

2 Answers

This is what bothered me when I saw the current code.

1) DoEvents has been commented out, but is it actually uncommented?

2) Does the variable W_LCnt mean the number of processes?
 If so,
  UserForm1.ProgressBar1.Value=W_LCnt/100
 
instead of
  UserForm1.ProgressBar1.Value=(W_LCnt/(MaxLine-1))*100
 Isn't that right?

Will the progress bar still stop in the middle?

I replaced SQL with a time-consuming one and ran it in my environment. 1)and 2) and the progress bar was displayed properly.


2022-09-30 13:53

There is a function called DoEvents.
If you call this function during a loop, the OS will be able to control other events, so you may be able to resolve it.

This article will be helpful.


2022-09-30 13:53

If you have any answers or tips


© 2022 OneMinuteCode. All rights reserved.