Simulating multithreading in vba

Hello,

I am working on making a project more efficient by breaking up the subs and putting each into it's own file so that each can run simultaneously (mimicking multithreading). First pass allowed me to create shared variables where the data in one variable (e.g. raw data stored in an array) can be shared among several workbooks (in the same instance of excel), but when I tried to create a parallel sequence, Excel refuses. It runs one file and waits for it to finish completely before moving to the nextfile.

Does anyone know of any tricks to simulate multithreading? I have heard of using vbs, Application.ontime and Waitformultipleobjects API. Does anyone have any examples of any method?

Right now I ended up having to start a new session of Excel and running each fie manually. What I would like to do is have one master and several slave files that can run in parallel.

Thanks.

abousetta

Hi Kyle,

Sorry, me too dumb to learn new learn new tricks :... seriously I know I need to learn to program in C++, or another 'real' programming language but I have just never found the time to learn. So as the old saying goes... 'if your only tool is a hammer then all problems appear to be nails' . I can live with this for now if I must but I have broken down the project from one file that takes 9 hours to run into 9 files (one primary and eight secondary files).

1) The master prepares the data and prepares eight arrays for further manipulation.
2) Each of the secondary files picks up its respective array and works on it.
3) In the end, the master brings everything back to one final variable that is printed to a text file.

I got them to work sequentially, but can't get to work simultaneously. I have tried putting triggers (e.g. workbook_open) and delaying the process in each secondary file (assuming that the focus would come back to the primary file so that it would open the next file) but I couldn't get it work. The primary file just waited for each secondary file to complete its routine before continuing to the next line of code. I am frustrated but am just creating temporary workarounds to this problem.

Have a great Sunday.

abousetta

I'm uploading two pairs of example files. The first pair transfer the variable from one workbook to another via public variables but have to be in the same instance. The second pair just open two separate instances of Excel. Both have the same problem of waiting in queue until the routine is completed before continuing.

Maybe someone might have any ideas on how to trick Excel vba into running multiple subs simultaneously.

abousetta

I guess I needed to talk it out loud with you guys. I finally got it to work. I just had to change the syntax a bit to allow ontime to run in the workbook_open sub. Attached are updated files if anyone has a similar need.

Good luck.

abousetta