|
Michael Kaden
Posts: 31
Joined: 2023-10-25
|
Hello Andrei,
I have a XLL / COM ADDIN and it works perfect if all the XLL functions refer to cells on et same sheet. However if an argument n the XLL function refers to a cell on another sheet, sometimes the COM following doing the calculation in OnSendMessage is putting #NUM in the target cell instead of the calculated Value sometimes EXCEL shuts down and reopens, Where is the best place to look for a solution. BTW when we had problems with OnSendMessage before eve on a single sheet red:
Private Sub AddinModule_OnSendMessage(sender As Object, e As ADXSendMessageEventArgs) Handles MyBase.OnSendMessage
If sc.Count > scmax Then scmax = sc.Count
If sctx.Count > sctxmax Then sctxmax = sctx.Count
Other activities
If (e.Message = MyMessage2) Then
red
..................Program Calculation of String.Collection (Writing to cells)
red
If I understood Dimitri correctly he put this in to ensure that the XLL is not calculating as long as the String.Collection is not empty.
I am running AddIn Express Version 10.4.4741
Thank you and kind regards
Michael |
|
|
Michael Kaden
Posts: 31
Joined: 2023-10-25
|
Sorry I tried to color the code Dimitri put in it is:
SyncLock sc.SyncRoot
........Calculations
If sc.Count > 0 Then
Me.SendMessage(MyMessage2) 'added by Dimitri
End If
End SyncLock |
|
|
Michael Kaden
Posts: 31
Joined: 2023-10-25
|
Ok I did a bit of searcher also in this forum. But I did not find a solution yet. If I understand it right SyncLock is disabled when the sheet focus changes so what other means do we have to prevent XLL starting when the COM writes to a cell on another sheet and this cell is part of an argument in the XLL function on this sheet?
Thank you and kind regards Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 19232
Joined: 2006-05-11
|
Hello Michael,
I've studied your old topic at https://www.add-in-express.com/forum/read.php?FID=5&TID=16217; please be aware that I assume your current code wasn't changed much.
What Dmitry suggested was creating a list of requests. Each request is processed separately by issuing a MyMessage2 message; that's exactly what the following code lines do:
sc.RemoveAt(0) ' remove request just processed; assumed is: we process the first element in the list
If sc.Count > 0 Then ' if there are other requests
Me.SendMessage(MyMessage2) ' process the next one
End If
The above code should guarantee that no request starts to be processed, if there's a request being processed. Nevertheless, I suggest that you check your logs when Excel crashes: whether the statement above is true or not?
Also, I believe the condition below should be removed; it should work incorrectly if you have time-consuming business calculations in CalcFuelXX, CalcGasXX etc. Also, I'd add a debug print to this method in order to check how often Excel invokes your UDF.
Public Sub DoSendMess(WorkName As String)
'Invoke and send Message
sc.Add(WorkName)
If sc.Count = 1 Then
Me.SendMessage(MyMessage2) ' should be called unconditionally
End If
End Sub
Again, I don't have your code, that's why I can only suggest logging. I assume that in the case of Excel crashing your logs might shed some light or directly point you to an issue in your current code. If not, extend your logging to find the method (code line) that crashes.
First off, it looks like you don't release the PR variable in AddinModule_OnSendMessage(); this should be fixed.
Second, I believe that SyncLock doesn't make sense because you only have one thread as all messages are processed on the main thread. On the other hand, it may add to the issue, if two or more requests are executed at the same time.
Regards from Poland (GMT+1),
Andrei Smolin
Add-in Express Team Leader |
|
|
Michael Kaden
Posts: 31
Joined: 2023-10-25
|
Thank you very much Andrei,
A far as I can see the crash comes because XLL calculation starts before the string collection is empty. I expected that SyncLock prevents that. I am not only working in one thread as the functions called in OnSendMessage use the Backgroundworker to do some calculation in another thread, but the Backgroundworker never connects to the UI.
It has no
ExcelApp.Calculation = XlCalculation.xlCalculationAutomatic
ExcelApp.EnableEvents = True
It just does some iterations and sends the result back to the function in OnSendMessage. I went through the code
and at the beginning of OnSendMessage we set:
ExcelApp.Calculation = XlCalculation.xlCalculationManual
ExcelApp.EnableEvents = False
And nowhere in the code including in the Backgroundworker this is changed.
The problem i have with Error logging or even step through the program is that the crash is not reproducible it sometimes happens but when i step through it does not. Perhaps also important is that it only happens when the XLL function has an argument from another sheet.
I do not really understand, but perhaps
6600: If sc.Count > 0 Then 'added by Dimitri
6610: Me.SendMessage(MyMessage2) 'added by Dimitri
6620: End If 'added by Dimitri
Is exiting and re-calling OnSendMessage as long as the String.Collection is not empty?
Could that release SyncLock and would it perhaps better to
6600: If sc.Count > 0 Then
6610: GoTo anotherstring 'where anotherstring: is a label on the top of "message2" but still after SyncLock.sc.SyncRoot so Synclock is not "broken"
6620: End If
Sorry i thought to understand how to separate XLL and COM but somehow not quite.
If i cannot find an easy fix as the next step I will take the BackgroundWorker out and do the iteration in the function in OnSendMessage. Or is there any other way to ensure XLL is not running as long as the String.Collection is not empty?
Thank you for your comment about to release PR. I do this at the end inside the function called by OnSendMessage
For example
Call CalcFuel01(PR)
has
If PR IsNot Nothing Then Marshal.ReleaseComObject(PR) : PR = Nothing
Exit Sub
Is that ok?
Sorry for not understanding and thank you very much for your help
Kind regards Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 19232
Joined: 2006-05-11
|
Hello Michael,
Sorry for leaving you without a response; I've overlooked your post for some reason. Sorry again.
Michael Kaden writes:
A far as I can see the crash comes because XLL calculation starts before the string collection is empty.
I assume, your XLL puts the next string to the collection and sends a message. How do you call XLL calculation? And how it can start before the collection is empty?
Michael Kaden writes:
at the beginning of OnSendMessage we set:
ExcelApp.Calculation = XlCalculation.xlCalculationManual
ExcelApp.EnableEvents = False
When do you restore the settings? I remember that Excel may invoke your XLL even when your add-in processes the message in the AddinModule_OnSendMessage method. Does that mean that the XLL may be invoked while you have ExcelApp.Calculation = XlCalculation.xlCalculationManual and ExcelApp.EnableEvents = False?
Michael Kaden writes:
The problem i have with Error logging or even step through the program is that the crash is not reproducible it sometimes happens but when i step through it does not.
This is why I suggest extending your logging. The log should show how a method starts and exits so that you could have more knowledge (and ideas) of what's going on.
Michael Kaden writes:
Perhaps also important is that it only happens when the XLL function has an argument from another sheet.
Make sure that PR = ExcelApp.Range(strx(1)) doesn't produce an exception. Also, make sure your background thread doesn't refer to the Excel Object model; it should only process data, not ranges/cells.
Regards from Poland (GMT+1),
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register