3

so I have a Sub that will run two other macros, but I need it to run the first then, wait 1 minute, then run the second one

I've looked online and have gathered this:

Sub MainExecute
stdB18
Application.Wait(Now + TimeValue(0:01:00"))
runB18
End Sub

The problem is when I do this, it seems that it won't wait until after stdB18 has run, but instead, will wait 1 minute before running anything.

I've also tried putting the wait inside of runB18 at the top, but this makes the whole application wait before running also. Is there any way I can just make it so I can run stdB18, then wait 1 minute, then run runB18?

Thanks!

Edit: The reason I need to wait is because I need to wait for my QueryTable, which refreshes in stdB18, to load, before I execute runB18 where I have for loops that will pull data from the table.

M--
  • 25,431
  • 8
  • 61
  • 93
elguapo
  • 155
  • 1
  • 14
  • 3
    @DavidZemens of course this is an [XY question](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) but I won't consider it as a duplicate. – M-- Jul 27 '17 at 19:30
  • 1
    It's a duplicate because the *actual problem* is the same, regardless of what the OP assumed the solution might to be. In the other threads about QT refreshing, there are at least 2 or 3 methods that can be used to do run a procedure after the table refreshes, and it's difficult to understand why those are not suitable answers to this question. of course, `Application.OnTime` is the *strict* answer to "How to run a procedure at a specified (future) time", but that wasn't really the Q here (and if it was, I'd have probably found another less-applicable answer to dupe it with). @Masoud – David Zemens Jul 27 '17 at 19:40
  • 1
    @Masoud I agree with you. Maybe OP doesn't know the cause behind the issue and got stuck in something else. – Subodh Tiwari sktneer Jul 27 '17 at 19:40
  • @sktneer maybe OP didn't know, but now s/he does know, and has (hopefully) learned at least three things: the difference between Wait and OnTime, why neither are ideal for this specific problem, and finally the actual solutions to the specific problem statement in OP. :) – David Zemens Jul 27 '17 at 19:44
  • 2
    @DavidZemens You maybe correct in your assumption but there is no point closing the doors for others to learn to correct themselves if they have the same issue. – Subodh Tiwari sktneer Jul 27 '17 at 19:46
  • 2
    @DavidZemens I know what you mean. My point here is that it is almost impossible that someone that wants to know how to run a macro after that a table has been refreshed land on this thread. Duplicates are (partly) here to help search engines pick different wordings. This question does not **actually** ask how to run a sub after a table has been refreshed. So, again, in my eyes it is not the duplicate of the thread you pointed to. But does not matter that much. Just articulating my point of view. Cheers. – M-- Jul 27 '17 at 19:48

3 Answers3

7

Normally you use .Refresh to run the query. Just add BackgroundQuery:=False to it so that the code will wait until the query table is refreshed and you don't need to use Application.Wait because you don't know how much time the query will take to finish.

So try...

.Refresh BackgroundQuery:=False
Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22
  • I've been using this in my other QueryTables and I actually could never figure out what exactly this does, I just knew it worked, but I guess now I know! Thanks! :) – elguapo Jul 27 '17 at 19:44
6

You need Application.OnTime

Sub MainExecute

  Application.OnTime Now() + TimeValue("0:01:00"), "runB18"
  stdB18

End Sub
M--
  • 25,431
  • 8
  • 61
  • 93
  • Hey, thanks for the reply! this gave me the same problem though, it makes `MainExecute` wait for a minute, instead of doing stdB18 first then runB18 – elguapo Jul 27 '17 at 19:24
  • @vba.Beginner No. It will run `runB18` after 1 minute if by that time `stdB18` is done. You cannot run two modules at the same time. – M-- Jul 27 '17 at 19:25
  • Oh hey yeah this worked after I ran it a second time. Thank you! – elguapo Jul 27 '17 at 19:28
1

You have told the whole application to wait. Instead, set a timer to create an event after one minute, then create a routine to catch this event and run your second routine.

Rob Anthony
  • 1,743
  • 1
  • 13
  • 17
  • Provide an answer. This is a commentary and you are above 50 rep so can post comments everywhere. – M-- Jul 27 '17 at 19:19
  • Thanks for your reply, but I'm pretty new to coding (started VBA about a week ago) and am not too sure how to execute this. Could you provide me with some guidance please? – elguapo Jul 27 '17 at 19:25
  • 1
    @PGCodeRider read these two posts: https://meta.stackexchange.com/questions/4217/why-do-some-people-answer-in-comments and https://meta.stackexchange.com/questions/17447/answer-or-comment-whats-the-etiquette – M-- Jul 27 '17 at 19:29