|
Michael Kaden
Posts: 22
Joined: 2023-10-25
|
Hello Andrei,
hope you are well. My Excel AddIn is running well. In one Sub I do loops to solve problems and I use the Backgroundworker which speeds up this process considerable. Up to now this worked very well. Without changing any code I now observe 2 problems when the sub with the BGW is called which have not been there before.
1.) When I input a manual value in a cell which uses the AddIn and the BGW the sheet is locked I have to unlock it to continue.
2.) From time to time the Workbook freezes or shut down and opens immediately the same workbook.
When I put a thread.sleep(100) in that Sub before the BGW is called, the problem seems to go away.
Do you have any explanations for this behavior and what is the most stable remedy?
Thank you and kind regards.
Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 19138
Joined: 2006-05-11
|
Hello Michael,
I don't understand the description. What's "the sheet is locked"? Also, "input a manual value in a cell" may mean you are editing the cell either in the Formula Bar or in the cell itself. Such a scenario - if this is the scenario and if it affects your add-in - should be detected by your code, check https://www.add-in-express.com/creating-addins-blog/excel-check-user-edit-cell/.
Michael Kaden writes:
When I put a thread.sleep(100) in that Sub before the BGW is called, the problem seems to go away.
If this is the case of editing a cell, that would mean that Excel switching from the editing context to the context where your calls can be served.
These are guesses, though, as I don't understand the scenario.
Regards from Poland (GMT+2),
Andrei Smolin
Add-in Express Team Leader |
|
|
Michael Kaden
Posts: 22
Joined: 2023-10-25
|
Hello Andrei, thank you very much for your answer and sorry for the delay in responding.
I looked at your recommendation and could not identify any of the scenarios mentioned in my code. Let me try to explain it in more detail.
I have an EXCEL AddIn using Excel AddIn (XLLModule) and COM AddIn together. I use SendMessage to create the calculation tree.
That all works very well.
I do not like the EXCEL iterative calculation so I switch it off.
The same with the "Solver".
But I have one module where the result is specified, like the temperature at the outlet of a combustion process. and the module changes the input value, like the fuel to be burnt until the specified result, i.e. the temperature is reached.
I have done a lot of this in Fortran about 40 years ago. We called it self learning software now they say AI.
The second step is with the gradient method where, if there is not enough temperature the fuel will be increased. With the first 2 results we can make linear function.
As soon as we have 3 points we can make a polynomial function, which becomes more accurate with each step.
The calculation of the next step and also the calculation of the polynomial approximation is done in the Background Worker BGW, which speeds up the process considerably. This works very good and the solution is found in the order of 50 ms with 3 or 4 iterations.
Lately I get sometimes, not always only sometimes, the effect that the workbook changes the sheet to protected:
I have to unprotect the sheet each time manually.
If I put Thread.Sleep(100) in the module which starts the BGW , i.e. in this example the module which changes the input fuel.
Or put it into the DoWork
Private Sub BGWLOOP_DoWork(sender As Object, e As DoWorkEventArgs) Handles BGWLOOP.DoWork
Threading.Thread.Sleep(50)
End Sub
The problem goes away. In principle I can live with that, but I would like to understand what happens.
Do you have any suggestion?
Thank you very much and kind regards. Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 19138
Joined: 2006-05-11
|
Hello Michael,
Can it be that you directly access the Excel object model from your background thread? If so, you need to reorganize your code so that the background thread only calculates without accessing the Excel object model; say, it mustn't read or write cells. When calculations are done, write the result to a global variable (e.g. array) and use ADXAddiModule.SendMessage to send a message to your add-in. To receive the message, your add-in module must handle the ADXAddinMoule.OnSendMessage event. When this event occurs, you need to handle the global variable to provide your results to the user.
Check section Wait a little; see the PDF file in the folder {Add-in Express}\Docs on your development PC.
Regards from Poland (GMT+2),
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register