Excel OnSendMessage problems - Add-in Express .NET forum

Michael Kaden

Guest

My addin is built up in the following way:

UDF Function in the XLL Module sends the UDF Address and Program name to DoSendMesage

 SMSTR = ProName & "@" & AddressNow
 Call AddinModule.CurrentInstance.DoSendMess(SMSTR)

The DoSendMess SUB adds the SMTR to the string colllection

Public Sub DoSendMess(WorkName As String)
sc.Add(WorkName)

In the OnSendMessage Event the following is done:
Get the zero item of the String Collection
Separate the Caller Address and the program Name
Call the Sub (in this case CalcHex) to do the calculation

WorkName = sc.Item(0)
strx = WorkName.Split("@")
ProName = strx(0)
PR = ExcelApp.Range(strx(1))
Call CalcHex(PR)

This worked very well up to now. The changes I have:

I have about 30 conected UDF's in the sheet
I changed to Windows 10 and Excel 365
I have a much faster PC intel i9Gen10 3,7GHz, 128 GB RAM x64

Say I have CalcHex("$A$1"), CalcHex("$A$6"), CalcHex("$A$11"), CalcHex("$A$16") in my Excel Sheet

What I can observe now is that a new OnSendMessaage is started and calling CalcHex("$A$6") while the previous called SUB CalcHex("$A$1") has not finished its calculation. I assume that the Sub CalcHex cannot be accessed and therefore I get numerous errors like:

21.06.08 , 16:33:56.98 , Error in DLL _ , , Program _ , CalcHex , Error number _ , 50290 , Exception from HRESULT: 0x800AC472

I thought that the OnSendMessage can only be fire once and waits until

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

is completed?

To find out the reason, I set up a logfile. When the OnSendmessage Evenzt is started I log

21.06.08 , 18:14:50.76 , ######## OnSendMessage Event Calculation Do Program: HEXX03 at @@@$AB$56

After that of course calculations are done and a number of new items are added to the String Collection.

At the end of the OnSendMessage Event I put in the Log Entry

21.06.08 , 18:15:04.26 , ######## OnSendMessage Event Calculation Do Program COMPLETED: HEXX03 at @@@$AB$56

The time delay comes because I have a breakpoint in the sub

But then I get

21.06.08 , 18:15:04.26 , ######## OnSendMessage Event Calculation Do Program COMPLETED: HEXX03 at @@@$AB$56

21.06.08 , 18:15:04.28 , ######## OnSendMessage Event Calculation Do Program: COMB02 at @@@$H$6

21.06.08 , 18:15:04.30 , XLL UDF send to DoSendMess @$H$36 - GDES01
21.06.08 , 18:15:04.30 , DoSendMess add to StringCollection , @@$H$36
21.06.08 , 18:15:04.30 , sc count @@@@16

21.06.08 , 18:15:04.31 , ######## OnSendMessage Event Calculation Do Program: COMB02 at @@@$X$6

That means the OnSendmessage Event starts again before the previous Event is finished.

I tried fro many days now and do not get any close to understanding what is going on.

Looking forward to your suggestions.

Thank you for your help & kind regards

Michael

Michael Kaden

Guest

Hello Andrei,

I did some more research and I believe the problem is related to Excel:

Construction of a dependency tree & Construction of a calculation chain

My Excel sheet looks like that:

http://www.alerasoftlib.de/ADX/SheetCopy.jpg

The manual input is in UDF 01 Fuel Value

The red lines directly take that input Value and enter a value into
UDF 03 - gas value
UDF 02 - Fuel Value

The black lines are dependencies of UDF's like

UDF 04 depends on UDF 01
UDF 05 depends on UDF 04
UDF 06 depends on UDF 03

UDF 08 depends on UDF 05 and UDF 06

So I would assume that UDF 08 is in the calculation chain after

UDF 03 - UDF 06 and after UDF 01 - UDF 04 - UDF 05

But this is not so. The red numbers next to the UDF number show the actual position of the UDF in the calculation chain. As UDF 03 is only calculated after UDF 19, which each entry into UDF 01 we get 2 runs of the whole calculation and 2 results in UDF 19.

This I found out by analysis of the calculation tree with manual input of the UDF 01 Fuel Value.

If I now want to automate (iterate) the process by making UDF 19 to change the input value in UDF 1 = dotted green line, I think that the double calculation will generate the error described above. What do you think, is that a possible explanation?

What is the solution? I was thinking not to let UDF 19 change the UDF input value, while the calculation chain is not finished, i.e. the StringCollection > 0. I would do that by using the background worker watching the SC.Count and WorkerCompleted to "trigger" the new Input into UDF 01.

Your comments and suggestions will be very helpful.

Than you & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18825
Joined: 2006-05-11

Hello Michael,

What you see is the way Windows works. To process a message, Windows postpones the current code execution, process the message, and then runs the code postponed.

You should use a Queue: it is better prepared for additions and deletions. Also, use SyncLock - see https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/synclock-statement; in C# this is lock - to organize processing the requests in an order. I'd suggest that in OnSendMessage you do the following: 1) add a new request to the queue, 2) use SyncLock to process all requests from the queue. In this way, if some OnSendMessage (old call) is being executed, and OnSendMessage is called again (new call), the new request will be added to the queue so that it will be processed by the old call and when the new call starts processing the queue it'll find the queue empty.

Andrei Smolin
Add-in Express Team Leader

Andrei Smolin

Add-in Express team

Posts: 18825
Joined: 2006-05-11

Hello Michael,

Michael Kaden writes:
What is the solution?

I'm not sure that your diagnosis is correct. Your assumption of the calculation order may be wrong: it's Excel who decides.

Anyway, I think about a bit higher level. I'm familiar with the chemistry and many technical details: in one of my previous lives I participated in a project targeting newbie operators of an oil plant; it was a model of the plant, in fact. We've created these entities:
- Control room, this is where an operator works; it contains many control and measurement units (CMUs) of different types from a simple red lamp and some switchers to a complex multi-channel unit displaying some tiny details of the process.
- CMU, 1) it displays information obtained through multiple channels; 2) it may allow controlling the process; control actions are sent through channels, too.
- Channel, it connects a given CMU to a control device or a data source (sensor) providing a single data value at a time. There were several channel types. Some of them imitated the reality, say a lamp is turned on when this or that sensor triggers it, some were required by models (see below) e.g. to provide a model with the input flow, pressure, temperature, whatever and to consume the model's results: output flow, pressure, etc might be put in a sensor.
- Device or control device, that's the model of a furnace, tank, column, heat exchanger, valve etc. I assume your UDFs trigger such models.
- Sensor - this is the source of the actual info: a temperature sensor, a chemistry analyzer could provide several sensors, etc.

We've used channels to connect CMUs, models, sensors, etc. in a net. When the operator changed something, the net was recalculated several times (until the models converge) and the result values were displayed on the CMUs in the control room's UI.

I think about getting rid of UDFs. This would let you control the recalculation order completely. You could track changes in certain cells using the COM add-in to trigger recalculation of your models. You would use flags to distinguish between user actions and changes produced by your code.

Also, I assume the set of data associated with a given model/CMU (you don't seem to distinguish these) may contain more info than displayed. Also, in future, the set may evolve to provide/process more information. For these reason, I'd use the information currently shown as the base and use a pane to let the user access the complete set of info. Say, if a cell within L6:N26 is selected, I'd show a pane containing all information, say, a historical chart. The pane could also provide possible actions associated with that heat exchanger, say, turn it off (if applicable) or imitate an emergency scenario.

In an early version of the project I mentioned, we automated Visio to display a control room's UI (imitating a real control room with all CMUs). When we were ready to use Windows API on a VB6 form, we got rid of Visio completely. The form events generated requests; say, double-clicking a form generated a request of the DoubleClickRequest type. A timer generated a request to recalculate the models every N seconds. All requests were filed in a queue; compare this with your OnSendMessage. The timer class also dispatched requests: when the timer decided it's time to process requests, it took them from the queue and passed them to appropriate request handlers: to recalculate models, to update CMUs, or to perform UI-related things such as zoom or double-click to open the UI of a CMU, etc. The ability to recalculate models allowed us to use any initial state.

If you get rid of UDFs, you would only use Excel for supplying models with data and displaying the results. If you change the UI design, you can get rid of Excel completely. To save your time, a pane (if you would use panes as suggested above) should display a single UserControl; the UserControl contains all the controls required for a given goal (e.g. for the heat exchanger info and actions). When you get rid of Excel, you would show these UserControls on your forms.

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Dear Andrei,

thank you very much for your detailed information and taking the time to look at "a higher level" and the underlying issues.

I am well aware of your scenario. I have done process models, including closed loop controls, already 45 years ago with analog computers and also with Fortran 4 punchcards. The visualisation was non existent. Then i build models with VB6 WinForms, that looked much better and when Visual Studio came about it allowed us to build process model with much more ease and also much better acceptance by the user.

Such "stand alone" model builders are readily available on the market such as Thermoflow or Ebsilon(Steag). In my company we use these for large projects. Also closed loop controllers based on Windows with elaborate UI, as you describe (left click opens a pane with more information, or opens an input form etc.) We use Wonderware "InTouch" which communicates with PLC plant controllers.

If you have 10 minutes, the following Video gives you some basic information on aleraSoft:

http://www.alerasoftlib.de/ADX/201204%20asoft.mp4

Everybody is comfortable in using Excel and the software is built in such a way that everybody can built models without a lot of training, and setting up models is possible in a matter of minutes. The possibilities to use all Excel features and the interchangeability with MS Office is also important. Furthermore the price is only a fraction of the costs of the above systems, usually ?В?Т?? 30.000 + 5.000 annually.

This is why we decided on an Excel AddIn and not a stand alone Visual Basic solution. When we got to know AddInExpress our decision was reinforced.

If you want to get to know the AddIn in more detail, please visit our Video Section:

http://www.alerasoft.com/en/videos/

But now more to the current issue:

As a pure model builder, i.e. without any closed loop controllers the AddIn works perfect I did not look at the Excel calculation tree as for this model building it does not make a big difference if a calculation takes a few seconds.

With entering closed loop control we now have some issues with that.

Obviously Excel is not reliable in ensuring that a UDF(1) is only calculated when all UDF'S on which the UDF(1) depends have been calculated. This results in double calculation costing calculation time, but much worth is that a wrong loop input value based on only partial calculation is generated. This can lead to the situation that the loop never converges. In the model shown in

http://www.alerasoftlib.de/ADX/SheetCopy.jpg

in one calculation cycle, UDF 19 sends an input to UDF 01 twice of which the first input is based on only 15 UDF calculations and therefore wrong!!

I tried your suggestion with SyncLog and it works to that extent that I do not get these errors any more:

21.06.09 , 15:19:42.73 , Error in DLL _ , , Program _ , CalcHex , Error number _ , 50290 , Exception from HRESULT: 0x800AC472

BTW. If I have a breakpoint anywhere, that seems to "break" the SyncLock and I get the errors again when the calculation SUB tries to access a cell.

So I believe that the problem can only be solved, when UDF19 waits until all calculations are done i.e. StringCollection.count = 0 and then writes a new value into UDF 01. Do you know another way to wait for StringCollection.count = 0 then to do it in another thread, for example with the background worker

In the DoWork have a loop which monitors sc.count (or the equivalent global variable?)
End DoWork on StringCollection.count = 0 - or a maximum Loop time
In the WorkCompleted if SC.Count = 0, write the last value which was calculated by UDF 19 to UDF 01 input.

I believe this should work, unfortunately it will however not solve the problem that Excel calculates 32 UDF's although there are only 15 UDF's in the chain. but I think we can live with that, we will see.

Thank you very much for your help & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18825
Joined: 2006-05-11

Hello Michael,

I see.

I don't know a way to change the calculation order. The only thing that comes to my mind is the topic at https://www.add-in-express.com/forum/read.php?FID=5&TID=9658. That is, in some situation a non-volatile XLL function becomes volatile and that page describes a way to make it non-volatile again. Does this relate to your case?

Andrei Smolin
Add-in Express Team Leader