OnSendMessage Error 50290

Add-in Express™ Support Service
That's what is more important than anything else

OnSendMessage Error 50290
Excel UDF running in paralell 
Michael Kaden


Guest


Hi Andrei,

I battle for some time now with Error 5029

I have a UDF which is in the Excel sheet calculation tree 

 Call AddinModule.CurrentInstance.DoSendMess(SMSTR)


SMSTR is a string containing the caller (cell) address and the calculation Sub to be used 

Public Sub DoSendMess(WorkName As String)
        'Invoke and send Message
        sc.Add(WorkName)
        Me.SendMessage(MyMessage2)  
End Sub


On the OnSendMessage Event the string (WorkName) in the string collection is the split into Program Name and Caller Address adn te appropriate Sub called for execution: 

Private Sub AddinModule_OnSendMessage(sender As Object, e As ADXSendMessageEventArgs) Handles MyBase.OnSendMessage 


If (e.Message = MyMessage2) Then

            SyncLock sc.SyncRoot

            Recalc = Recalc + 1

                WorkName = sc.Item(0)
                strx = WorkName.Split("@")
                Dim OSAddres As String() = strx(1).Split("!")
                ProName = strx(0)
  
                PR = ExcelApp.Range(strx(1))

                sc.RemoveAt(0)

            Select Case ProName

                    Case "FUEL01"
                   If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcFuel01(PR)
                        If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case "FUEL02"
                        If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcFuel02(PR)
                   If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

               Case "FUEL03"
                        If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcFuel03(PR)
                    If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case "FUEL04"
                    If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcFuel04(PR)
                    If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

               Case "FUEL05"
                   If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                   Call CalcFuel05(PR)
                    If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

               Case "FUEL06"
                   If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                   Call CalcFuel06(PR)
                   If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case "FUELMX"
                    If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcFuelMX(PR)
                   If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case "COMB01", "COMB02", "COMB03"
                   If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcComb(PR)
                    If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

               Case "HEXX01", "HEXX02", "HEXX03"
                    If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcHex(PR)
                   If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case "GDES01"
                   If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                  Call CalcGasDesign(PR)
                   If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

               Case "GASS01", "GASS02", "GASS03", "GARE01"
                    If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcGasSplit(PR)
                   If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case "GASM01"
                   If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcGasMix(PR)
                  If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

               Case "STEM01", "STEM02", "STEM03", "STEM04", "STEM08", "STEM05", "STEM06", "STEM07"
                   If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcBoiler(PR)
                   If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)
               Case "EMIS01"

                    If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcEMI(PR)
                    If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case "HUMI01"
                    If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcHUMI(PR)
                    If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

               Case "KXAX01", "KXAX02"
                    If SRE = True Then Call serv.LogWrite("Start Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address, True)
                    Call CalcKXA(PR)
                    If SRE = True Then Call serv.LogWrite("Completed Calculate on Send Message, " & ProName & ", " & PR.Worksheet.Name & ", " & PR.Address)

                Case Else
           End Select


As you can see, I make a log entry before and after the Sub is called. Now, from time to time, I get a strange behavior as shown in the log File

21.08.06 , 10:32:52.6683 , Start Calculate on Send Message, KXAX01, Simple GT Exit T, $AC$42
21.08.06 , 10:32:52.6754 , Completed Calculate on Send Message, KXAX01, Simple GT Exit T, $AC$42

21.08.06 , 10:32:52.6764 , Start Calculate on Send Message, HEXX02, Simple GT Exit T, $AK$4

21.08.06 , 10:32:52.6973 , Start Calculate on Send Message, KXAX01, Simple GT Exit T, $AG$42
21.08.06 , 10:32:52.71 , Error in DLL _ , , Program _ , CalcKXA , Error number _ , 50290 , Exception from HRESULT: 0x800AC472 , Line 170
21.08.06 , 10:32:52.73 , Error in DLL _ , , Program _ , CalcKXA , Error number _ , 50290 , Exception from HRESULT: 0x800AC472 , Line 170
21.08.06 , 10:32:52.76 , Error in DLL _ , , Program _ , CalcKXA , Error number _ , 50290 , Exception from HRESULT: 0x800AC472 , Line 3180
21.08.06 , 10:32:52.78 , Error in DLL _ , , Program _ , CalcKXA , Error number _ , 50290 , Exception from HRESULT: 0x800AC472 , Line 3540
21.08.06 , 10:32:52.80 , Error in DLL _ , , Program _ , CalcKXA , Error number _ , 50290 , Exception from HRESULT: 0x800AC472 , Line 5400
21.08.06 , 10:32:52.8018 , Completed Calculate on Send Message, KXAX01, Simple GT Exit T, $AG$42

21.08.06 , 10:32:52.8018 , Start Calculate on Send Message, HEXX02, Simple GT Exit T, $AS$4
21.08.06 , 10:32:52.82 , Error in DLL _ , , Program _ , CalcHex , Error number _ , 50290 , Exception from HRESULT: 0x800AC472 , Line 720
21.08.06 , 10:32:52.84 , Error in DLL _ , , Program _ , OnSendMessage , Error number _ , 50290 , Exception from HRESULT: 0x800AC472 , Line 555
21.08.06 , 10:32:52.8491 , Completed Calculate on Send Message, HEXX02, Simple GT Exit T, $AK$4


Calculation for cell $AG$42 is started before calculation for cell $AK$4 is completed

In summary it looks like

$AK$4 is started
$AG$42 is started
5 Errors 50290 (which I believe is when different Subs want to write to the sheet at the same time)
$AG$42 is completed
$AS$4 is started
2 Errors 50290
$AK$4 is completed

It looks to me that OnSendMessage is Running again before it has finished the previous run? But how is that possible?

I read your post about volatile UDF's, could it be that my UDF is changed to volataile, but then the OnSendMessage should still process the StringCollection one be one in a row?


My UDF has many optional parameters including Range Address as string, but no "As Range" only Double and string 


         Public Shared Function alera(Prog As String, Optional D1 As Double = 0,
                                     Optional D2 As Double = 0, Optional D3 As Double = 0,
                                     Optional D4 As Double = 0, Optional D5 As Double = 0, Optional D6 As Double = 0,
                                     Optional D7 As Double = 0, Optional D8 As Double = 0, Optional D9 As Double = 0,
                                     Optional D10 As Double = 0, Optional D11 As Double = 0, Optional D12 As Double = 0,
                                     Optional D13 As Double = 0, Optional D14 As Double = 0, Optional D15 As Double = 0,
                                     Optional D16 As String = "", Optional D17 As String = "", Optional D18 As String = "",
                                     Optional D19 As String = "", Optional D20 As String = "") As String



Any Idea where this behavior could come from?

Thank you and kind regards

Michael
Posted 06 Aug, 2021 04:16:00 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello Michael,

It looks like Excel can interrupt code running on the main thread and perform a UDF call. This ends with your code getting a new message while the previous message isn't processed yet. You can manage a list of requests. Every OnSendMessage processing requests from your UDF should 1) add the request to the list and 2) initiate processing the requests if the list isn't processed at the moment. Note that the initiation should be done via a separate message. In this way you guarantee no delay in handling messages sent by the UDF.


Andrei Smolin
Add-in Express Team Leader
Posted 06 Aug, 2021 09:15:25 Top
Michael Kaden


Guest


Hello Andrei

thank you for you prompt answer. I have difficulties understanding it, sorry.

The UDF already passes the request to the Sub DoSendMessage

The Sub DoSendMessage adds the Request to the "sc" String Collection (is that the list you are referring too?)
and calls Me.SendMessage(MyMessage2)

The ADX OnSendMessage processes each entry in the "sc" String Collection and Removes the sc.item one by one. I understand that the OnSendMessage does that until the "sc" string collection is empty.

The observed behavior indicates that Sub OnSendMessage is called again while the previous Sub OnSendMessage has not reached End Sub yet. As Sub OnSendMessage is in the UI Thread I do not understand how this is possible. I do not have DoEvents or something like that anywhere.

Question 1: How is the ADX OnSendMessage "fired"?

Question 2: is there a way to delay a new OnSendMessage Event firing until the previous one has been completed?

Sorry if I did not understand your recommendation & thank you very much for your help.

kind regards

Michael
Posted 06 Aug, 2021 11:25:28 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello Michael,

When code is interrupted, it is put on stack while the interruption is being processed. Even on the same thread. Your worries would be okay if you happen to find this issue on a Windows Forms application developed by you. Excel is a complex application developed by those who know and use MANY described, non-described and non-public API things so that Excel is able to work in many scenarios that we might never heard of.

Just an example: Outlook is able to receive emails even while your add-in shows a message box.


Andrei Smolin
Add-in Express Team Leader
Posted 06 Aug, 2021 14:17:50 Top
Michael Kaden


Guest


Dear Andrei,

thank you very much. I am aware that Office/Excel has many surprises. My question was more directly related specifically to the AddIn Express OnSendMesssage Event. Is there any workaround to prevent a new OnSendMessage Event being fired before the previous OnSendMessage Event is completed?

i.e.can we control the behavior of the AddIn Express OnSendMessage Event or is Windows/Office/Excel deciding when to fire that Event?

thank you & kind regards

Michael
Posted 06 Aug, 2021 16:14:16 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hello Michael,

Is there any workaround to prevent a new OnSendMessage Event being fired before the previous OnSendMessage Event is completed?


I think you can try to handle the list of events in a bit different way, for example: 


Public Sub DoSendMess(WorkName As String) 
    'Invoke and send Message 
    sc.Add(WorkName)
    
    If sc.Count = 1 Then
      Me.SendMessage(MyMessage2)   
    End If
End Sub


Private Sub AddinModule_OnSendMessage(sender As Object, e As ADXSendMessageEventArgs) Handles MyBase.OnSendMessage  
 
If (e.Message = MyMessage2) Then 
    ' ...

    sc.RemoveAt(0)
    If sc.Count > 0 Then
      Me.SendMessage(MyMessage2)
    End If
End If

End Sub


Hope this helps.
Posted 09 Aug, 2021 04:26:11 Top
Michael Kaden


Guest


Thank you Dimitri,

I tried with a small Dataset (100 entries) and so far it works. I will try tonight with my normal volume = 3.000 + Datasets and report back.

The observation so far is that it increases the calculation time by about 50 to 80 %.

Thanks for your help.

Kind regards

Michael
Posted 09 Aug, 2021 07:15:34 Top
Michael Kaden


Guest


Dear Dimitri,

Tested it with a large Dataset and it works perfect.

Thank you very much again

kind regards

Michael
Posted 10 Aug, 2021 04:46:50 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hello Michael,

Thank you for letting us know!
Posted 10 Aug, 2021 07:36:56 Top