xLL & Excel Objects

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

xLL & Excel Objects
How to access Excel Objects from XLL AddIn 
Michael Kaden




Posts: 251
Joined: 2017-11-15
Dear Andrei,

thank you very much for the explanation. Using the OnSendMessage Event is still new to me. I understand that the OnSendMessage Event is called each time a UDF is called, even if the collection has already completed all calculations.

Anyway it looks like a powerful tool.

However it has it's challenges. I think I have to be careful that the code following the UDF completion does not influence the OnSendMessage Event results, for example by changing a global variable used by the OnSendMessage Event.

Example:

I use SendKeys.Send("^%" & "{F9}") to renew the values in all cells. If I only want to change cells with text, I set a global variable TextOnly = true. SendKeys will then initiate the calculation of all UDF's.

The code in the OnSendMessage Event will then check if TextOnly = true and if yes will only recalculate cells with a specific formula writing text to the cell. But at the end, I have to set TextOnly = False. If I do it after the SendKey command then some OnSendMessage Events will already use TextOnly = False. If I use it at the end of the OnSendMessage Event I am not sure that the change to TextOnly = False will be guaranteed, and that TextOnly = False will only be executed after all OnSendMessage Events are completed.

So I think to use SendKeys.Send("^%" & "{F9}") in the OnSendMessage Event context is not a good idea? Perhaps I will use the Find method to get the cells with formulas making text and in combination with range.calculate renew these cells one by one. so at the end of the Find loop I am sure that all calulations are completed and I can set TextOnly = False.

With regard to my code example above I have learned that remove(k) is not right it should be RemoveAt(k) but this would give an error as the collection will be reduced while the loop is still running, so collection.items with high index will not be found. So I took "remove" out. Could there be a problem with the collection.Clear() command if not all the cells were assigned their values in one go?

Do you have any links where I can read more about timing issues using the OnSendMessage Event.

Thanks again & kind regards

Michael
Posted 27 Mar, 2018 09:54:13 Top
Andrei Smolin


Add-in Express team


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

No, we have no links on the OnSendMessage event.

Still, I suggest that you regard the SendMessage/OnSendMessage machinery as a short-interval timer that fires only once: you start that timer by calling SendMessage(); when the interval - it is never specifically defined, it is volatile, but it is the least possible interval in the current moment - is over, the timer triggers the OnSendMessage event and stops.

SendKeys isn't a good idea. It works by sending keys to the *focused* window. Imagine the user selecting a text box in a pane or Ribbon. Consider calling Range.Calculate() instead.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 27 Mar, 2018 10:24:32 Top
Michael Kaden




Posts: 251
Joined: 2017-11-15
Dear Andrei,

thank you for your message.

Up to now, I have used Sendkeys as I could not get worksheet.calculate or range.calculate to work. I have only found information on the .calculate method on VBA but not on Vb.net - VB.

This link

MS Range.Calculate Method - Microsoft.Office.Interop.Excel

implies that

Dim Retro As Object
Dim xlSheet As Excel.Worksheet
xlSheet = GetActiveSheet()
Retro = xlSheet.UsedRange.Calculate()

should work, but it does not.

So I assume that the .calculate method is VBA and not VB. Is this correct?

I tried to use worksheet.EnableCalculation = true. this did not help, but I found somewhere the information that in VB.net - VB when the worksheet.EnableCalculation changes from False to True, the worksheet will be recalculated. So this actually worked:

worksheet.EnableCalculation = False
worksheet.EnableCalculation = True

and the sheet is recalculated even without the worksheet.Calculate() method.

What is your experience on the above?

Thank you and kind regards

Have a nice Easter

Michael
Posted 31 Mar, 2018 10:42:20 Top
Andrei Smolin


Add-in Express team


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

Michael Kaden writes:
So I assume that the .calculate method is VBA and not VB. Is this correct?


No, this isn't. VBA and VB/C# use the very same COM objects that the Excel object model provides.

It looks like you describe the following issue. Your COM add-in modifies some cells on the worksheet, invokes sheet.UsedRange.Calculate() and this doesn't rigger the sheet to recalculate. Is this description correct?

I have two questions.

1) you call UsedRange.Calculate() in *every* call of your AddinModule_OnSendMessage event? Does this method work after the very last AddinModule_OnSendMessage event is handled?
2) Does sheet.UsedRange includes the cells that your add-in modifies *previous* to your add-in modifying these cells?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 02 Apr, 2018 01:23:02 Top
Michael Kaden




Posts: 251
Joined: 2017-11-15
Dear Andrei,

thank you very much for your post. My problems with the worksheet.calculate method are independent from OnSendMessage. I found 3 different worksheet.calculate on the MS DevNet and Dev center.

worksheet.calculate Method on MS Dev Center

Range.Calculate Method on MS DevNet

and also an worksheet.calculate event, which as far as I see has the same Syntax as the first method above.

Worksheet.calculate Event MS Dev Center

I made a sample project which I send to your support website.

It includes 2 Excel workbooks where TestCalc01.xlsm sheet 1 is the summary. Please open TestCal01 and TestCal 02 in one Excel Instance.

the UDF's will increment global integers, one separate one for each UDF. So one can see easily, whn any of te UDF's is calculated.

All UDF's have =[Testcalc01.xlsm]Sheet1!$A$11 as Input parameter to invoke calculation by input. I made some UDF's hidden and also protected, but the behaviour stays the same. The following can be observed.

The behaviours which I did not expect are highlighted in red.

Changing [Testcalc01.xlsm]Sheet1!$A$11 = all UDF's are calculated
Using keyboard F9 or Shift F9 = nothing happens
Using Keyboard Ctr Alt F9 = all UDF's are calculated
Using code: 'SendKeys.Send("{F9}") = Nothing happens
Using code: 'SendKeys.Send("^%" & "{F9}") = all UDF's are calculated

Using code:

'wks.EnableCalculation = False
'wks.EnableCalculation = True

= all UDF's are calculated but UDF's in [Testcalc01.xlsm]Sheet2 are calculated 2 x

Using code :
Dim CalcRes As Object
Dim xlRange As Excel.Range
xlRange = wks.UsedRange

alternatively
xlRange = wks.Range(\"A1:K30\")

CalcRes = xlRange.Calculate()

Or using code:

wks.UsedRange.Calculate()

Nothing happens


I have also added a ribbon "All zero" which sets all the global integers to 0 and makes it easier to see what is calculated.

Do you understand this behaviour?

Thank you very much for your support & kind regards

Michael
Posted 03 Apr, 2018 09:52:54 Top
Andrei Smolin


Add-in Express team


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

Michael Kaden writes:
Using keyboard F9 or Shift F9 = nothing happens


I suggest that you check the page describing how Excel calculates cells; see https://msdn.microsoft.com/en-us/library/office/bb687891.aspx. On this page, they mention Range.Dirty as a way to include a cell in the calculation chain.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Apr, 2018 08:16:58 Top
Michael Kaden




Posts: 251
Joined: 2017-11-15
Dear Andrei,

thank you for your message.

My scenario is as follows:

I have UDF's which do not have a dependency, they are not volatile and not dirty. For example:

=aleraTX("TX0550TXFUEL")

The function will retrieve a string from the "active" resource file with the index "0550" and write it into the cell. If we change the language of the add in, then another resource file is made the active resource file. To show the new language in the cell, the UDF calculation has to be forced. This should only happen when the "active" language resource file is change. To make the UDF volataile would therefore not be wise as the (Language text) UDF's would be calculated on every recalculation. I could use the Range.Dirty method, which would require that I find the cells which have to be changed.

We tried different methods:

The following works:

Keyboard = Keystroke Ctr Alt F9

Code = SendKeys.Send("^%" & "{F9}")

Code =
worksheet.EnableCalculation = False
worksheet.EnableCalculation = True
will work but calculates some of the UDF's multiple times

The following will not work:

Keyboard = Keystroke F9

Code = SendKeys.Send("{F9}")

any method with *.calculate, as these only look at UDF's in the dependency tree


This leaves us with the following:

This is the process:

The process is triggered by a ribbon button opening the language selection form.
The form let the user select a new language which will assign a different resource file.

Alternative 1:
SendKeys.Send("^%" & "{F9}")will then rewrite all the cells having formula =aleraTX...... with the new text

Alternative 2:
run a FIND process to search for Formula = aleraTX....
FIND will return the Range
we then apply Range.Dirty which will recalculate (on automatic calculation mode) the cell in the Find Range

I would use Alternative 2 as I think this gives better control of what is recalculated. However I am not sure, if the Range.Dirty command will not invoke an unintended recalculation sometimes later, the documentation is not clear for me if the Range.Dirty is cleared after the recalculation? My tests however indicate that the Range.Dirty includes Range in the calculation tree only once.

What do you think about that?

Thank you and best regards

Michael
Posted 07 Apr, 2018 08:00:50 Top
Michael Kaden




Posts: 251
Joined: 2017-11-15
Dear Andrei,

sorry for the long post.

Finally I will use


Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
xlSheet = ExcelApp.ActiveSheet
xlRange = xlSheet.UsedRange
xlRange.Dirty()

So I do not have to use FIND etc.. I tried it and it seems to work. See also my new post.

testing OnSendMessage & Range.Dirty

thank you & kind regards

Michael
Posted 09 Apr, 2018 08:18:36 Top