1

Im just realizing that I have two macros on two different excel sheets that are scheduled to run at certain times throughout the day. They overlap at 1130 and are intended to capture market events at those times. One will run in about 10 seconds while the other can take about 1min to run. Both contain copy and pasting of some sort. Its important that I get the information exactly at 1130 so a delay would not be ideal.

One of the macros is scheduled through Application.Ontime and the other is part of a worksheet.calculate function.

I was wondering how VBA handles this? Will it run the macro all the way through then begin the second or will it try to run at the same time causing the copy/paste not work correctly.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
novawaly
  • 1,051
  • 3
  • 12
  • 27
  • 2
    VBA is single-threaded, so it won't be running them simultaneously – John Coleman Mar 13 '19 at 15:49
  • Ok cool - any idea on how will it determine which runs first? – novawaly Mar 13 '19 at 15:50
  • 1
    Your question isn't very clear. You talk about *two* scheduled functions in the title of the question but then say "One of the macros is scheduled through Application.Ontime and the other is part of a worksheet.calculate function" -- which suggests that only one of the two functions is scheduled (with the trigger of the other one left unspecified). As a default, `Application.OnTime` will run the procedure when scheduled -- or as soon as it can (as soon as Excel is in ready-mode). – John Coleman Mar 13 '19 at 15:54
  • Sorry - The 2nd one will always be triggered at 1130 as well. – novawaly Mar 13 '19 at 15:57
  • @novawaly As far as I know, VBA would run the macros synchronously. VBA executes all procedures and /or functions sequentially. In other words, VBA consists of only one thread. There are workarounds like using VBA to create other VBA worker threads, which essentially opening a new instance of Excel and running code there. Check out [this](https://stackoverflow.com/questions/5721564/multi-threading-in-vba) post about multi-threading in VBA. – rickmanalexander Mar 13 '19 at 16:01
  • ok perfect. Thank you – novawaly Mar 13 '19 at 16:03
  • I guess you could add a `MsgBox "procedure x/y ran"` and you can see which ran first. But as pointed out, since vba is single-threaded, it depends on which procedure has top position in the queue, so you don't even have a guarantee they always run in the same order. If you want to manipulate them, you can create your own thread – Samuel Hulla Mar 13 '19 at 16:04
  • You are most welcome.Just a note that if you have the know how, you could use `C# ` or `VB.NET` which DO provide the framework to multi-thread, albeit, more abstract. – rickmanalexander Mar 13 '19 at 16:07
  • If you want to deterministically schedule one before the other, you need to specify different times for each. The code should be written in such a way that whichever macro runs first, makes no difference. This is textbook *temporal coupling*. – Mathieu Guindon Mar 13 '19 at 16:12
  • 1
    @rickmanalexander while C# or VB.NET do provide framework for muti-threading, concurrent access on COM objects will throw `COMException` left & right. COM and .NET have wildly different threading models, so .NET threads need to be synchronized. So yeah, .NET can do MT, but .NET/COM Interop + MT = trouble. – Mathieu Guindon Mar 13 '19 at 16:14
  • @MathieuGuindon My friend, your comment went over this ole boy's head, lol. All I know is that they each support multi-threading, but I am woefully ignorant at the deep inner-workings of each. Definitely trying to learn all I can though, so thanks for the info! – rickmanalexander Mar 13 '19 at 16:24

0 Answers0