Various Instance of Excel talking to each other crash

[schabo]

Hello everyone,
I am working simultaneously with multiple instances of Excel, as this is a way to utilize more CPU and decrease calculation time per instance. Basically, there is one "Master" instance and several "Slave" instances. When I start the day, I first open the Master Instance, and from that instance I have a lot of code that does the rest of the work for me. So from the Master instance, I first run the code "Open_All_Slaves" which opens my 4 slaves instances. Once the slaves are open, the Master has various codes that cause each slaves to perform some tasks. For example, Master can ask Slave 1 to run a certain of its own code (let's call that "Slave_1_Code"), or Master can instruct each slave to copy and paste some information etc, or Master can change a setpoint on one of the scalp, for example, if the Temperature setpoint on Scalp 2 is 48C, Master can instruct Scalp 2 to change that setpoint from 48C to 50C. That structure enables me to have a very efficient Dashboard view in Master with bunch of control buttons, and each Slave do the heavy lifting and communicates back some results to Master. Each Slave and Master exchange data via a my local SQL database and I recently took all my charts out of Excel and put them into PowerBI, which receives live date from SQL.
My problem is that one or more of the Instances will freeze from time to time, and I can't pinpoint what causes the freeze. Sometimes it will happen right at the beginning when I run the "Open_All_Slaves", sometimes it freezes when I run a specific code from Master, and sometimes everything goes well for hours, I run a ton of codes without freeze and then out of nowhere an Instance freezes.

I looked on most forums and can't find the answer to this issue. My feeling is that there is a certain messagebox that arise in one of the Slave that cause a message box to pop up, and because the user can't click Yes/No, the Slave goes into an endless waiting period and freezes.

I tried to strategically place some "On error Resume Next" and "Application.DisplayAlerts = False" in different locations of the code, but can't find what really works. I am really running out of ideas here.

I have one of the strongest CPU available on the market these days, 48 GB Ram, 2 NVIDIA P1000, run Windows 10 Pro with Excel 2019. The CPU is rarely used at more than 25% and the Ram usage is normally less than 50% adn each excel file are quite small at less than 2MB.

Thanks for the help!

 

[offthelip]

Finding these sorts of crashes can be very difficult in any system. The approach that I have usually taken is to start writing bits of code that you can put into each of the slaves and the master that continuously logs what the state of the slave is and what it is doing. Then what you hope for is that after a few crashes you are able to detect some common activity that might be happening. One area to be particularly aware of is when you have multi instance working it is often the common resources that is where the problem is. e.g. in your case I would look at how they are accessing th sql database.
I once spent three weeks working nights , writing new code every shift until eventuallly I tried something that detected the problem, ( Not EXCEL!!) !! Best of luck

 

[schabo]

Thank you for your insight. We did what you recommended about a year ago. We got inspired by a "Logger" code that we found on "Excel Macro Mastery" if I remember correctly. Initially, that Logger was only in few location in our code, but as we discovered its usefulness, it is now in almost half of our code. Initially, the Log would only appear in the Immediate Window, but it was hard to trace the history especially in the case of a crash, so we now also copy each Log in a .txt file in real time so we can review the Log history outside of Excel in the even of a crash. It is very interesting you mention that the issue might be with SQL because generally speaking, the last Log that we see before a crash is when the code calls a "SQL query refresh". For your reference, we are using ADODB.Command in VBA to send info from Excel to SQL, and use the standard Query tool to pull from SQL to Excel. After doing some research on SQL stability, we found that some of the issues might be because we had enabled the "Enable Fast Data Load" in the Query Properties, which creates Foreground work vs Background work. We unchecked that box and it did helped a bit, but it isn't the entire solution. Do you have any idea which other parameter we could check about our SQL connectivity that might help?
Even though the Instances of Excel should be completely segregated (in theory), they do seem to somehow conflict with one another at times. Thanks for the help, much appreciated.

 

[offthelip]

You are obviously going down the right tracks, I am not a SQL database expert so I haven't got any further suggestions of changing any other parameters. My only suggestion is to look at where the SQL database is running; is it on a dedicated CPU with a dedicated disk?