OnSendMessage - Add-in Express .NET forum

Michael Kaden

Guest

Dear Andrei,

as I am new to the SendMessage/OnSendMessage Method I have made a test Project "ExcelCrash" which I send by email to support.

First, please confirm if I am right in that an Excel UDF cannot manipulate (change values, interior properties etc.) of cells in a range.Offset(x,y) from the calling cell. This is valid for all UDF's regardless if they are XLL or ComAddIn UDF's. So to manipulate other cells from an UDF one needs the SendMessage/OnSendMessage Method. Correct?

After you installed the ExcelCrash AddIn, open a new workbook. The Ribbon Tab "ExCrash" will be opened. If you press "populate" then the AddIn will populate 14 columns with UDF's. the number of rows is defined by the code

Private Sub Populate_OnClick......................
'##################################
Dim Tabsize As Integer = 100
'#################################

the first 10 columns will have an argument depending on "B1" The next 4 Columns will have UDF's with no depending argument. I did this to test out the Range.Dirty calculation, as discussed in this post:

https://www.add-in-express.com/forum/read.php?FID=5&TID=14891&MID=76767#message76767

I work with two String Collections and 2 different UDF's just to see how the method works.

In cell B2 B3 B4 I show how often the OnSendMessage Event is called, The String Collection 1 and 2 are called.

I can set this counters to 0 with the Zero Ribbon Button

On pressing Populate the UDF's are entered and calculated. I do not know, why the UDF's are calculated twice?

Changing the Value in B2 then the UDF's with depending arguments are calculated

Pressing Dirty, then all UDF's also without depending arguments are calculated.

So far so good, also the calculation time looks reasonable.

When the number of rows Tabsize = in increased, at some point EXCEL crashes. Tabsize = 50.000 should crash reproducibly. The crash reports, attached to the project email, shows "The tread used up its stack"

If I am not mistaken, then Excel (Office 2010) should allow some 1 mio. rows.

Have I made a mistake in applying the SendMessage/OnSendMessage Method?

Thank you and kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Michael,

Michael Kaden writes:
as I am new to the SendMessage/OnSendMessage Method

This method internally uses the very same mechanisms as a usual System.Windows.Forms.Timer. You can regard the SendMessage/OnSendMessage machinery as a System.Windows.Forms.Timer which is set to a small amount of time and which you turn off as soon as the timer's Tick event occurs. More than that, you can REPLACE the SendMessage/OnSendMessage machinery with a System.Windows.Forms.Timer. The question may arise: what amount of time is small enough for the whole machinery to work? The answer is: the smaller, the better. Actually, the timer's Tick event (as well as OnSendMesage event) may only occur if the thread in which the timer is created is *not* busy: if you start a timer before you run a long non-interrupted calculation, the event won't occur until the calculation ends or until it issues an async call.

Michael Kaden writes:
First, please confirm if I am right in that an Excel UDF cannot manipulate (change values, interior properties etc.) of cells in a range.Offset(x,y) from the calling cell. This is valid for all UDF's regardless if they are XLL or ComAddIn UDF's. So to manipulate other cells from an UDF one needs the SendMessage/OnSendMessage Method. Correct?

There's no strict ban to do this. Sometimes, it is possible to do; see e.g. section Returning dates from an XLL at https://www.add-in-express.com/docs/net-excel-udf-tips.php#returning-dates. But most often, this doesn't work. This doesn't depend on using XLL or Automation add-in (still, Automation add-ins look far more tolerant). We saw issues depending on the Excel version used. We don't know whether any given call to the Excel object model will work or not in this or that Excel version. Because of this uncertainty, we strongly recommend that you avoid using the Excel object model when in an XLL call. You understand that an Automation add-in *uses* the Excel object model; we recommend not to modify the caller cell(s) in an Automation add-in.

The SendMessage/OnSendMessage machinery (or a timer) lets you introduce a small delay so that parts of your code execute outside of the UDF call context. The context outside of the UDF call context is a context where the object model is ready to be called; in this context all object model calls execute without the restrictions above. This is the context in which VBA macros, COM add-ins, and external programs accessing the Excel object model work.

Michael Kaden writes:
I do not know, why the UDF's are calculated twice?

I don't see this. 14 column x 100 values per column = 1400 calls of ExCrash1. Plus, 1400 calls of ExCrash2. Each call ends with a SendMessage call; total of 2800 calls. I have these values in B1-B4: 1, 2800, 1400, 1400. Clicking Populate for the second time produces: 1, 5600, 2800, 2800. Pressing Dirty produces 1, 7000, 3500, 3500.

Michael Kaden writes:
When the number of rows Tabsize = in increased, at some point EXCEL crashes. Tabsize = 50.000 should crash reproducibly. The crash reports, attached to the project email, shows "The tread used up its stack"

I wonder if replacing multiple PR.Offset(0, 1) calls with calling PR.Offset(0, 1) once, assigning the result to a variable of the Range type, using that variable and releasing it after use solves the issue.

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Dear Andrei,

1.) Why is this issue important to us? Our "main" AddIn uses iterations so we have limited control over how often SendMessage/On Sendmessage is called. Also we have committed ourselves now to use the SendMessage/OnSendMessage method to ensure that we have a reliably and robust way to manipulate multiple cells after an AddIn UDF is triggered. Your information on Excel Object Model and UDF's was very convincing. With VBA, which e used in the past, as you mentioned this is no problem. That is why we have made the test "ExcelCrash" to see if there are limitations which we have to cater for.

2.) Your suggestion

I wonder if replacing multiple PR.Offset(0, 1) calls with calling PR.Offset(0, 1) once, assigning the result to a variable of the Range type, using that variable and releasing it after use solves the issue.

seems not to point at the issue as when we use only UDF's without depending arguments i.e. all UDF's are "=ExCrash1(0)", no relative arguments used, the problem still is there at a certain number of rows. The number of rows where this happens is not constant.

3.) We updated our test program to make it easier to manipulate. Copy send to you in the support mail.

In B5 you can enter a rows number which is used with the next populate button click
In B6 & B7 we show how many loops where executed on the last OnSendMessage event with StingCollection.Count>0 i.e. this will show us if the complete StringCollection was executed with one OnSendMessage event.

We "feel" that the issue is connected to the StringCollection. Although the Count property is LONG and therefore should not give any problems, we feel that we run into memory problems. We could not find any data on what can happen if the StringCollection is very large.

Following examples have been tried:

Opening new workbook, Row Numbers = 100 click populate no problem, however we have 100 rows each with 7 columns with UDF ExCrash1 = 700 calculations ExCrash1 and 100 rows each with 7 columns with UDF ExCrash2 = 700 calculations ExCrash2. On writing the formulas to the cells, each cell should be calculated once. We get however 2 x 1400 = 2800 calculations.

The list however makes mathematical sense:

all OnSendMessage calls 2.800
Collection 1 calls 1.400
Collection 2 calls 1.400
Row Numbers 100
StringCollection1.Count 1.400
StringCollection1.Count 1.400

We then set the counter to 0 and change the Input argument (B1) which gives the following results:

all OnSendMessage calls 1.000
Collection 1 calls 500
Collection 2 calls 500
Row Numbers 100
StringCollection1.Count 500
StringCollection1.Count 500

which is perfect as only the first 10 columns have argument in the formulas. If we press Dirty then we get

all OnSendMessage calls 1.400
Collection 1 calls 700
Collection 2 calls 700
Row Numbers 100
StringCollection1.Count 700
StringCollection1.Count 700

which is exactly what it should be.

If we now press zero and enter a new Row Number, for example 4000 and press populate, then we get:

all OnSendMessage calls 10.000
Collection 1 calls 56.000
Collection 2 calls 56.000
Row Numbers 4.000
StringCollection1.Count 56.000
StringCollection1.Count 56.000

which is correct, only again 4000 X 7 twice as above and all OnSendMessage calls 10.000 which is the public class variable "Zahl" why this is not 112.000 I do not understand? Is it because not all StringCollection Items are handled within one OnSendMessage Event?

Now we can increase Row Numbers and click populate until the crash accurse. Just below the row numbers where the crash happens we get the following error:

Error number 50290
Exception from HRESULT 0x800AC472
Procedure = OnSendMessage

then

Error number 5
Index was out of range. Must be non-negative and less then the size of the collection
Procedure = OnSendMessage

For me it looks like OnSendMessage Event wants to get a StringColllection.item which is not there?
Perhaps a memory problem?

MSForum however implies that something with the EXCEL Object Model going wrong is the culprit see:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel?forum=vsto

The row Numbers when it happens are not always the same. It is interesting to see that the time to execute if no crash happens increases not linear, i.e. calculating 2000 rows needs much more than twice the time that calculating 1000 rows take.

Sorry that I come back to this issue over and over again, but I am afraid that there is something in our AddIn which might fire back in a big way in future.

Thank you very much for your help.

kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Michael,

I would like to access your PC remotely. Is this possible? If yes, I suggest using TeamViewer; in this case you'll need to send me your session ID and password to the support email address. Do you have a headset?

Andrei Smolin
Add-in Express Team Leader

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Michael,

I have only 40 minutes left for today. Can we schedule the remote for tomorrow?

Andrei Smolin
Add-in Express Team Leader

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Michael,

Do you still need my help on anything? Or, I can close this topic as well as your other one?

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Dear Andrei,

Unfortunately I am still battling with it. The problem is that the issue is not easily reproducible.

The issue came up initially on my main program when the following sequence is initiated:

XLL UDF creates sendMessage and writes a string containing the "work to be done definition" and the caller address into a String Collection.

The OnSendMessage reads the String Collection and changes cell values (Offset to caller) and properties accordingly

One of the cells changed is argument in another XLL UDF

I made sure there is no indefinite loop, but results in just a calculation tree.

First I studied the link in my post above:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel?forum=vsto

and I found it very interesting especially the items about multi thread user interface.

Then I cam about some posts about the fact that StringCollection is not "threadsafe", i.e. one part can manipulate (add / delete) to a StringCollection while another art of the program is for example looping through this StringCollection.

So I tried to enclose any StringCollection action (adding/reading/deleting) in
SyncLock StringCollection.SyncRoot
End SyncLock

would iMessageFilter help?

Michael Kaden

Guest

Sorry somehow I clicked the wrong key - continuing here:

so this worked, no more errors and the number of calculations are exactly as intended, however the time to just do 2 UDFs is 10 seconds.

That is as far as I got right now. A I said the issue is not reproducible, so I am investigating further. Just thought you might have a hint where to look. Sometimes it is difficult to see the trees for all the forest (German saying)

Thank you & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Michael,

Michael Kaden writes:
One of the cells changed is argument in another XLL UDF

When OnSendMessage occurs, you should scan the StringCollection in order to such issues and resolve them before they have chance to occur.

Michael Kaden writes:
and I found it very interesting especially the items about multi thread user interface.

Your add-in does not have a multi-threaded UI. Both XLL and COM add-in work in the main thread. When you switch between the XLL and COM Add-in contexts, this occurs on the main thread as well.

You can use threads in your add-in(s) e.g. to perform some calculations; you must *not* use the Excel object model on a thread other than the main one. If you follow this rule, you don't need to use SyncLock and IMessageFilter. Anyway, I'm really skeptical about using IMessageFilter in a real-world Office add-in written in C#/VB.NET.

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Guest

Dear Andrei,

I have done the following in OnSendMessage:

sc is the StringCollection
WorkName is the String Item which contains the ProgramName to be executed & "@" & the caller Range address



        If (e.Message = MyMessage2) Then
            If sc.Count < 1 Then GoTo EndThis
                For k = 0 To sc.Count - 1
                    WorkName = sc.Item(0)
                    strx = WorkName.Split("@")
                    ProName = strx(0)
                    PR = ExcelApp.Range(strx(1))
                    'now go and execute the formulas
                    Select Case ProName
                        Case "FUEL01"
                            Call CalcFuel01(PR)
                        Case "FUEL02"
                            Call CalcFuel02(PR)
                        Case "FUEL03"
                            Call CalcFuel03(PR)
                        Case "FUEL04"
                            Call CalcFuel04(PR)
                        Case "FUELMX"
                        Case Else
                    End Select
                sc.RemoveAt(0)
            Next k
EndThis:

        End If

New in this is that I goto out the For/Next loop if the StringCollection is empty and also in the loop I always read Item(0) and then remove item(0). This seems to work without problems. I will do some more testing.

Thank you very much and kind regards

Michael