|
Michael Kaden
Guest
|
Hello Andrei,
I use Excel XLL UDF and COM objects by using the OnSendMessage
So The UDF creates a String.Collection which is then processed with OnSendMessage.
This works good, but when I use circle calculations (my own not Excel iterations) it first does, what it is supposed to do. Please look at the log file which you can download here:
http://www.alerasoftlib.de/ADX/OnSenfMessage_aleralog.txt
HEXX03 starts the calculation tree when the setpoint and an actual value differ in HEXX03
The StringCollection is created
The calculation tree starts with COMB02 and finishes with HEXX03 and the StringCollection is calculated in OnSendMessage
This happens 3 times until the setpoint and the actual value in HEXX03 are within a predefined tolerance.
With that the calculation should be finished.
But then OnSendMessage runs 30 times although the String.Collection Count is 0
A new String Collection is not generated during these 30 calculations.
Me.SendMessage(MyMessage2) is not called in these 30 calculations
Call AddinModule.CurrentInstance.DoSendMess(SMSTR) is not executed in these 30 calculations
Any idea where these 30 calculations might be triggered?
Thank you very much and kind regards
Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
Hello Michael,
I suppose this relates to the logic of your code. You can add a number of debug messages to your code; use System.Diagnostics.Debug.WriteLine(). If the add-in is built in the Debug configuration, you collect the messages at run time using DebugView (see http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx).
Andrei Smolin
Add-in Express Team Leader |
|
|
Michael Kaden
Guest
|
Dear Andrei,
thank you very much for your prompt reply.
I know what the program is doing, but I do not know why. Perhaps more easy example can show what happens:
I have 2 UDF's
1 named GDES01 in Address $F$3
1 named HEXX01 in Address $J$3
The UDF in $J$3 has also $F$3 as one of the input values
So I have a calculation chain.
If I change an Input in $J$3 the following happens:
21.03.01 , 11:34:05.81 , XLL has called HEXX01 from Address [Book1]Sheet1!$J$3
21.03.01 , 11:34:05.81 , Add to sc calc , HEXX01@[Book1]Sheet1!$J$3
21.03.01 , 11:34:05.85 , OnSendMessage Start with e.Message Nu = 1026 sc.String.Collection Count 1
21.03.01 , 11:34:05.85 , Start OnSendMessage Calculation, count = 1 Do Program: HEXX01
21.03.01 , 11:34:05.86 , Current String Collection content = , HEXX01@[Book1]Sheet1!$J$3
So a collection String with one item is generated and processed. Works perfect!
If I change an input value in $F$3 then a collection String with 2 items is generated and processed, good so far, but then OnSendMessage is additionally trying to process a collection String with zero count.
21.03.01 , 11:35:13.97 , XLL has called GDES01 from Address [Book1]Sheet1!$F$3
21.03.01 , 11:35:13.97 , Add to sc calc , GDES01@[Book1]Sheet1!$F$3
21.03.01 , 11:35:13.98 , XLL has called HEXX01 from Address [Book1]Sheet1!$J$3
21.03.01 , 11:35:13.98 , Add to sc calc , HEXX01@[Book1]Sheet1!$J$3
21.03.01 , 11:35:13.99 , OnSendMessage Start with e.Message Nu = 1026 sc.String.Collection Count 2
21.03.01 , 11:35:13.99 , Start OnSendMessage Calculation, count = 2 Do Program: GDES01
21.03.01 , 11:35:14.01 , Current String Collection content = , GDES01@[Book1]Sheet1!$F$3
21.03.01 , 11:35:14.01 , Start OnSendMessage Calculation, count = 1 Do Program: HEXX01
21.03.01 , 11:35:14.01 , Current String Collection content = , HEXX01@[Book1]Sheet1!$J$3
red
If I have longer calculation chains the zero count String Collection comes up multiple times (exactly the number of dependencies between UDF cells). This string Collection is not generated by the UDF's
So what is triggering OnSendMessage trying to process a zero length String Collection for each dependency between UDF's after the calculation chain is completed?.
Hope I could describe the issue clear enough.
BTW I disabled multi threading, just in case.
I have also looked at
https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/
Is the ADX "hidden Windoww" sending the zero length String Collection?
Thank you again & kind regards
Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
Hello Michael,
Michael Kaden writes:
Is the ADX "hidden Windoww" sending the zero length String Collection?
The SendMessage method can only send an integer value. I assume this value is >= 1024 in your case.
Accordingly, the OnSendMessage event can only provide this value. Nothing else. Not a collection. Most definitely.
The method and event also let you send and receive two IntPtr parameters. These parameters are perfectly okay if they are constant values such as IntPtr.Zero or an integer cast to IntPtr. It would cause many serious issues if you use these parameters to pass an address of a variable (say, pointing to a collection).
So, your Excel add-in should add that collection as an item to a list/queue/collection that the COM add-in module could process. After that it should calls SendMessage(intNewCollectionToProcess).
When the COM add-in receives the OnSendMessage event, it should retrieve the next item (which is a collection), remove it from the list/queue/collection, and process the item (collection). While processing the item (collection), your COM add-in could use other messages to split the processing in parts or implement other logic.
Michael Kaden writes:
But then OnSendMessage runs 30 times although the String.Collection Count is 0
Some parts of your code issue that message. There can be no other explanation. To check whether this is so or not, debug your code.
Andrei Smolin
Add-in Express Team Leader |
|
|
Michael Kaden
Guest
|
Thank you Andrei,
I was under the impression that the OnSendMessage event is connected to the String Collection.... and therfore, I put a loop into the OnSendMessage which after it is called the first time will process the complete String Collection. I thought that will speed up the process.
Now I understand that SendMessage will trigger one OnSendMessage for each UDF call. This was not understood by me from the documentation.
So in my loop I emptied the String Collection but Send Message still had to process the other UDF calls even with the String Collection empty.
So I have now rectified this and with taking the loop out
The problem is solved & calculation time was halved.
Thank you & kind regards
Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 18817
Joined: 2006-05-11
|
You are welcome!
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register