search

Excel-DNA / Samples

Various sample projects and snippets related to Excel-DNA
MIT License
124 stars 80 forks source link

Update speed problem? #10

Closed ICTAL-GW closed 4 years ago

ICTAL-GW commented 5 years ago

As an Excel-DNA Newbie, I carefully went through the notes and examples given on the official site before attempting 3 of your examples for various ways to implement RTD functions (I did not look at RtdClock-IExcelObservable). After using Nuget to install Excel-DNA version 0.34.6 (targetframewok=net461), everything compiled and worked but I had an issue with the speed of updates in all 3 cases – the clock only updated every 2 seconds. Given the simplicity of the projects, this suggests something is wrong.

Focusing on RtdClock-ExcelRtdServer, I tested it on Excel 2016 MSO 32-bit and on an older version of Excel, 2007 SP3, both on my PC. Further, I tried it on a colleague’s PC, again Excel 2016 MSO 32-bit. Each time the results were the same.

As I have not had update problems with third party RTD services in the past (e.g. Bloomberg), I am loath to start stripping down my Excel before asking you a couple of questions.

Firstly, can you confirm that you do not get these update issues when you run RtdClock-ExcelRtdServer within the same Excel environments?

Finally, I note that you choose not to override the Heartbeat() method. This means I cannot see it and analyse it. Can you explain what the default method is as it might help to provide some insight to help resolve my issues?

govert commented 5 years ago

This is the expected behaviour under the Excel default settings. The RTD update notification indicates to Excel (once or many times) that new data is available, but Excel will wait a little while before retrieving the new data. Excel will not read updated values from an RTD server at an interval smaller then the configured RTD ThrottleInterval. This helps Excel to not be overwhelmed by frequent updates.

The 2 second RTD update speed is the default setting for the RTD ThrottleInterval in Excel. You can change it by setting the value of Application.RTD.ThrottleInterval to a value lower than the default of 2000 (milliseconds) (for example by going into the Immediate window in the VBA IDE, and typing in Application.RTD.ThrottelInterval = 500.

Note that the ThrottleInterval:

I would recommend a value of 500 for modern machines that are not under much load.

You can learn more in this RTD FAQ from 2001: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa140060(v=office.10)

ICTAL-GW commented 5 years ago

That was easy - ThrottleInterval reset and the clock is ticking away merrily and correctly. Thank you for your prompt reply.

As a postscript, is it possible to access this Excel property via C#? It would be tidy coding to be able to choose an appropriate level for my app and then return it to the Default Value of 2000 on session close e.g. via ServerTerminate().

govert commented 5 years ago

One snag is that the value you set (or reset after you're done to the initial value you found before setting) does not only apply to your app, but is shared by all RTD servers running in Excel and is persisted in the future. How would different add-ins negotiate between themselves and the user about this setting? So it needs some caution if setting it automatically, and at least some way for the user to opt out of the interference with this global setting that might have serious side effects.

ICTAL-GW commented 5 years ago

Your caveats had already occurred to me - and I have now put them in the project notes and will address them if any problems arise.

My question was about the exact C# syntax for accessing the ThrottleInterval and which packages, if any, I needed to load. MS docs point me to Microsoft.Office.Interop.Excel which appears to be for Excel 2013. Is this also OK for later versions?

govert commented 5 years ago

Yes - that version is fine for earlier and later versions of Excel too. The C# compiler will embed the parts you need into your assembly (using the "Embed Interop Types" option) so you need no redistribute anything extra. You can also install the required assembly in your project by installing the "ExcelDna.Interop" package from NuGet.

ICTAL-GW commented 5 years ago

I am happy to try to get to know your ExcelDNA better, so I have installed Excel-DNA.Interop" version="14.0.1.

I now need the namespace required and the full syntax for setting the ThrottleInterval, that is, exactly as I need to write it into my code. I am not sure how to address Excel from within C#, so I need a simpleton's guide. Thank you.