1

How can you switch between processes based on time?

++++

I have an Excel macro sending XML requests to Google, it has 3 important processes.

  1. The process sending the XML requests. This process is limited to 10 requests per second.
  2. A loading bar with the ability to quit the program
  3. The Excel Application itself (it should be editable while the program is running)

The macro is currently using the sleep function (from kernel32.dll) to delay the processes, and the DoEvents function to make sure both process are run (and so the application can be modified in the background).

Ex. Process 1

Do While True
    Call doSomething
    DoEvents 'handles processes 2 and 3
    Sleep 100
Loop

This method delays the entire application. Editing the Excel Worksheet in the background is laggy because of the 100ms delay, and attempting to quit the program is also delayed.

To remove this lag, I would like processes 2 and 3 to be running and switch control to process 1 when it is time to send a request. After the request is sent, process 1 can give control of the program back to processes 2 and 3. Does anyone have a solution to this?

Bonus: VBA seems to have little capability for multi threading/processing, but if anyone can think of a way to run all these processes at once I'd love to know about it

Community
  • 1
  • 1
Alter
  • 3,332
  • 4
  • 31
  • 56

1 Answers1

1

The implementation details are quite involved for each of the things you are looking for but they can be easily found by searching. So I'll suggest an approach for each of your questions.

  1. Use Application.OnTime to trigger a macro to run after a given time interval.
  2. Multiple threads can be executed, but it's tricky. You'll need to either
    • Invoke another instance of Excel and make it run your Macro, that way your main thread is not affected or
    • Put your macro into a separate text file (vb-script file) and call get it executed via Shell. That way you can continue executing the existing Macro and the user regains control of the UI, but the script fetches new data in the background.
    • Execute part of your code in a separate instance of Excel and then pump in the results into your current spreadsheet via DDE (older but fairly reliable technology) or by building something called an RTD server.

Ample examples of each of the above points are available on this site and elsewhere through Google.

hnk
  • 2,216
  • 1
  • 13
  • 18