1

Perhaps this is a straightforward question, but I need to 100% verify this. I have a function (Execute) that calls different modules in order.. Like this:

Private Sub Execute()
Call Func1
Call Func2
Call Func3
End Sub

Functions 1 and 2 are quite complicated functions that draws and manipulates data. What is very important is that functions 1 and 2 are executed and completed prior to running function 3.

One way I do this currently is by forcing a 1 second lag prior function 3 to ensure 1 and 2 are finished, something like this:

Private Sub Execute()
Call Func1
Call Func2
Application.Wait (Now + TimeValue("0:00:01"))
Call Func3
End Sub

I am thinking of removing the 1 second lag - but I really want to make sure that functions are running and completing in a synchronized manner.

Thanks

Oday Salim
  • 1,129
  • 3
  • 23
  • 46
  • 4
    VBA is single threaded so there is no need for the `.Wait()`. What a wonderful thing it would be if we could run parallel though. – JNevill Jun 19 '18 at 20:22
  • You see, I am loading and manipulating huge amounts from SQL to Excel in Functions 1 and 2. I need to make sure that it is all complete and loaded before attempting function 3.. Do you know what I mean? I do not want data still loading in memory while attempting to run func 3. – Oday Salim Jun 19 '18 at 20:23
  • 1
    One caution is if you are using Power Query in one of those functions. VBA doesn't wait for PQ by default. You can force it; (backgroundRefresh = False, Application.CalculateUntilAsyncQueriesDone, etc.) – Mistella Jun 19 '18 at 20:24
  • Luckily I do not use Power Query. I only use ADODB for SQL Server connections and standard functions in Excel & VBA. – Oday Salim Jun 19 '18 at 20:27
  • Drop the `Call` keyword, it's redundant, useless and obsolete. Also if none of your `Function` procedures return anything, they should probably be `Sub` procedures instead. Lastly, the parentheses around the argument list in the `Application.Wait` call (why is there no `Call` on that one?! Gotta love the consistency of `Call` proponents), are force-evaluating `Now + TimeValue("0:00:01")` as a value, and force-passing the result `ByVal`. If this extraneous parentheses thing is a habit, you'll inevitably run into "Object Required" errors caused by it. – Mathieu Guindon Jun 19 '18 at 21:21
  • Also see if the [temporal coupling](https://stackoverflow.com/a/50743044/1188513) can be removed by eliminating global state and introducing parameters. That said, VBA is single-threaded and, unless you're writing event-driven code, completely synchronous - unless you're invoking an API that is documented as asynchronous. – Mathieu Guindon Jun 19 '18 at 21:24

1 Answers1

2

I am pretty certain that VBA function calls would be synchronous, but one easy way you could verify this would be to put debug.print statements inside your functions. In Func1 put debug.print "Entering Func1" as the very first statement and debug.print "Exiting Func1" as the very last statement. Put equivalent statements in the other two functions. After you run your Execute function, examine the debug window to check that all the messages come out in the expected order.

Skippy
  • 1,595
  • 1
  • 9
  • 13