Can there be cross talk/interference between VBA worker threads launched using your multithreading with VBScript example?

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Solved2.27K views

Hi Analystcave,

I am using your VBA multithreading with VBA worker threads code to launch multiple instances of a simulation program which relies on random numbers to generate traffic loadings.

I have found that I am getting a lot of duplicate maximum loadings from the individual worker VBA threads, which led me to think that I was getting duplicate random numbers.
I am now fairly sure this is not the case, so one of the other possibilities is that the threads are somehow getting values from each other. Since all the variables are identically named in the workbook copies launched via VBScript this seems like it could be possible to me, but I am not sure.

Do you know if it is possible for the individual instances of excel to interfere with each other?

Thanks in advance,

selected answer

Hi Darby, the threads are separate instances of Excel (Excel.Application) so it is not probable they are communicating. Frankly I would simply add a code that each thread create its own txt dump file. Log all individual thread characteristics to check which threads are processing similar data. You can also use txt files to communicate between threads.

commented on answer

Thanks for the reply. I did exactly that and found that my problem was with the excel random number generator – it simply wasn’t generating enough unique random numbers for my simulation.

Tom (AnalystCave)

Hi Darby, ok so you need to add a seed to the Rand function as the Rand function is only pseudo random not really random. Best practice is to use the time as the seed (see Randomize function).


Hi Analystcave, Yes I am aware of that, one of the first things I tried was offsetting the start times of the threads so that the seed times were different. But it appears that Excel’s random number generator can only generate 2^24 random numbers before repeating values which is about 16 million. My longest simulations need around 160 million random numbers I’ve currently adopted a piece of code from a forum which is apparently an implementation of the Wichmann-hill algorithm.

Simply the best place to learn Excel VBA