VBA Code to Finish 1st Task Before Moving on to Next?

[ExcelAtEverything]

Hello experts,

Is there some code I can add here to ensure that RefreshInventory completely finishes before moving on to call RefreshSales? I have RefreshInventory set to load to a pivot after all transformations are completed in Power Query, at which point there are several other queries sourcing from that pivot. I'm doing it this way because when I simply referenced the query for them all in PQ, the refresh time was about 2-3 times as long.

Thanks for any assistance!

Sub CallFormat()
     Call RefreshInventory
     Call RefreshSales
        Worksheets("CRP").Activate
        Range("B15").Select
        MsgBox "Refresh Complete"
End Sub

 

[Micron]

There might be better ways but I typically use a pause between procedure calls. This one will take fractional seconds as input in case of many multiple calls:

Sub pause(sngSecs As Single)
Dim endTime As Single

endTime = Timer

Do Until Timer > endTime + sngSecs
  ' do nothing
Loop

End Sub

Put (e.g.) Pause 3
between calls & test to see what length of delay you need.

 

[ExcelAtEverything]

There might be better ways but I typically use a pause between procedure calls. This one will take fractional seconds as input in case of many multiple calls:

Sub pause(sngSecs As Single)
Dim endTime As Single

endTime = Timer

Do Until Timer > endTime + sngSecs
  ' do nothing
Loop

End Sub

Put (e.g.) Pause 3
between calls & test to see what length of delay you need.

Thank you Micron, but if I understand you correctly then I don't think this will work for me. I'm unable to predict/preset the amount of time, because it is different all the time. Sometimes it takes 15 minutes, & sometimes it takes 25 minutes. I'm looking more for something that will simply "wait until" the first is finished before moving on to next task. Is there a way to adjust this to fit my needs?

 

[bebo021999]

Or put the
Call RefreshSales
within
RefreshInventory?

Sub RefreshInventory()
.      ..code...
        Call RefreshSales
End sub

Then the main sub:

Sub CallFormat()
     Call RefreshInventory
      Worksheets("CRP").Activate
      Range("B15").Select
      MsgBox "Refresh Complete"
End Sub

 

[ExcelAtEverything]

Or put the
Call RefreshSales
within
RefreshInventory?

Sub RefreshInventory()
.      ..code...
        Call RefreshSales
End sub

Then the main sub:

Sub CallFormat()
     Call RefreshInventory
      Worksheets("CRP").Activate
      Range("B15").Select
      MsgBox "Refresh Complete"
End Sub

But would that work? I need RefreshInventory to complete by specifically loading the transform results directly into my worksheet pivot. That worksheet pivot is what is being used as the source for RefreshSales. So if RefreshInventory is not fully complete and loaded, then the rest will be all for nothing. I can't find info anywhere that shows the specific steps in detail enough for me to know when it is finishing and when it is not, so I'm thinking I can't be the only one to come across this issue, & that there must surely (I hope) be some sort of code to say basically "RED LIGHT!" until the other finishes and then "GREEN LIGHT" to continue. Will your solution work that way?

 

[Micron]

The first code has to finish before the second call can begin - that's just the way it works. However, sometimes the effect of the first call is not realized before the 2nd starts to run, and if that call depends on the system refreshing/finalizing the 1st call, the 2nd call can fail. The pause is for allowing the system to do whatever it should be doing before the 2nd call. It's not for allowing the 1st call time to do its thing. Try a 2 or 3 second pause. Since we have no idea what the 1st called procedure does, it's not possible to know if you need to do something else at the end of it that you're not doing. You could also try Do Events in between calls, with or without the pause. It's common in Access but I can't recall seeing it in Excel vba so I don't know if it's acceptable in Excel.

 

[bebo021999]

post complete both codes to see i can help.
BTw, have you tried : Do Evens
?

 

[ExcelAtEverything]

post complete both codes to see i can help.
BTw, have you tried : Do Evens

I'm reading up on Do Events as we speak. I'm still in "VBA-101" so-to-speak, so the concept isn't quite clear, nor is the implementation. A little too sprawling for me to post here. The subs being triggered are to refresh queries. The queries take a while to refresh because they are full of user parameters and lots of query references & merges. That's also the reason I feel like I can't place a 'delay-timer' (@Micron ). Some times that initial query takes 5 minutes to run and download the results to the table... & sometimes it takes 20 minutes. It depends on the day... it depends on the date range we're looking at... it depends on the task. I could use the timer method if I was going to keep adjusting the timer each day, but that is simply an impossibility. I guess I thought there would be a way for me to just put like a 'Pause' type of command in between the two subs to tel the 2nd not to start before the first is 100% complete (or basically until the resulting worksheet table has been updated/refreshed.

 

[Micron]

I've said 2x now that the timer is for causing a delay between calls. It has no bearing on how long a query or procedure runs.
It doesn't matter if procedure/query A runs 10 minutes one day and 20 the next, or how long procedure/query B runs after that. The timer only causes a delay between procedure/query A and B. The idea is to allow the system to update/refresh screens/data whatever before procedure B runs. Don't forget that the lag time between calls is in microseconds, and therein lies the problem - sometimes. I also mentioned Do Events in my first post, which seems to have been missed.

 

[JGordon11]

Maybe you could write the code to set the top left cell in the inventory table to 0 (or whatever can be a flag that table has not updated), then refresh the inventory query, then add a do until top left <>0 (or whatever flag you chose) with do events in the loop, then add a 5 second pause that includes do events for good measure, then refresh sales. Do events must be in both loops to ensure the machine continues to process the query while in the loops.