StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Different tack=new answer.</p> <p>A few things I've discovered the hard way, that you might find useful:</p> <p><strong>1.</strong> In a UDF, returning the RTD call like this</p> <pre><code>' excel equivalent: =RTD("GeodesiX.RTD",,"status","Tokyo") result = excel.WorksheetFunction.rtd( _ "GeodesiX.RTD", _ Nothing, _ "geocode", _ request, _ location) </code></pre> <p>behaves as if you'd inserted the commented function in the cell, and NOT the value returned by the RTD. In other words, "result" is an object of type "RTD-function-call" and not the RTD's answer. Conversely, doing this:</p> <pre><code>' excel equivalent: =RTD("GeodesiX.RTD",,"status","Tokyo") result = excel.WorksheetFunction.rtd( _ "GeodesiX.RTD", _ Nothing, _ "geocode", _ request, _ location).ToDouble ' or ToString or whetever </code></pre> <p>returns the actual value, equivalent to typing "3.1418" in the cell. This is an important difference; in the first case the cell continues to participate in RTD feeding, in the second case it just gets a constant value. This might be a solution for you.</p> <p><strong>2.</strong> MS VSTO makes it look as though writing an Office Addin is a piece of cake... until you actually try to build an industrial, distributable solution. Getting all the privileges and authorities right for a Setup is a nightmare, and it gets exponentially worse if you have the bright idea of supporting more than one version of Excel. I've been using <a href="http://www.add-in-express.com/add-in-net/" rel="nofollow">Addin Express</a> for some years. It hides all this MS nastiness and let's me focus on coding my addin. Their support is first-rate too, worth a look. (No, I am not affiliated or anything like that).</p> <p><strong>3.</strong> Be aware that Excel can and will call Connect / RefreshData / RTD at any time, even when you're in the middle of something - there's some subtle multi-tasking going on behind the scenes. You'll need to decorate your code with the appropriate Synclock blocks to protect your data structures.</p> <p><strong>4.</strong> When you receive data (presumably asynchronously on a separate thread) you <strong>absolutely MUST</strong> callback Excel on the thread on which you were intially called (by Excel). If you don't, it'll work fine for a while and then you'll start getting mysterious, unsolvable crashes and worse, orphan Excels in the background. Here's an example of the relevant code to do this:</p> <pre><code> Imports System.Threading ... Private _Context As SynchronizationContext = Nothing ... Sub New _Context = SynchronizationContext.Current If _Context Is Nothing Then _Context = New SynchronizationContext ' try valiantly to continue End If ... Private Delegate Sub CallBackDelegate(ByVal GeodesicCompleted) Private Sub GeodesicComplete(ByVal query As Query) _ Handles geodesic.Completed ' Called by asynchronous thread Dim cbd As New CallBackDelegate(AddressOf GeodesicCompleted) _Context.Post(Function() cbd.DynamicInvoke(query), Nothing) End Sub Private Sub GeodesicCompleted(ByVal query As Query) SyncLock query If query.Status = "OK" Then Select Case query.Type Case Geodesics.Query.QueryType.Directions GeodesicCompletedTravel(query) Case Geodesics.Query.QueryType.Geocode GeodesicCompletedGeocode(query) End Select End If ' If it's not resolved, it stays "queued", ' so as never to enter the queue again in this session query.Queued = Not query.Resolved End SyncLock For Each topic As AddinExpress.RTD.ADXRTDTopic In query.Topics AddinExpress.RTD.ADXRTDServerModule.CurrentInstance.UpdateTopic(topic) Next End Sub </code></pre> <p><strong>5.</strong> I've done something apparently akin to what you're asking in <a href="http://www.calvert.ch/geodesix" rel="nofollow">this addin</a>. There, I asynchronously fetch geocode data from Google and serve it up with an RTD shadowed by a UDF. As the call to GoogleMaps is very expensive, I tried 101 ways and several month's of evenings to keep the value in the cell, like what you're attempting, without success. I haven't timed anything, but my gut feeling is that a call to Excel like "Application.Caller.Value" is an order of magnitude slower than a dictionary lookup.</p> <p>In the end I created a cache component which saves and re-loads values already obtained from a very-hidden spreadsheet which I create on the fly in Workbook OnSave. The data is stored in a Dictionary(of string, myQuery), where each myQuery holds all the relevant info.</p> <p>It works well, fulfils the requirement for working offline and even for 20'000+ formulas it appears instantaneous.</p> <p>HTH.</p> <hr> <p>Edit: Out of curiosity, I tested my hunch that calling Excel is much more expensive than doing a dictionary lookup. It turns out that not only was the hunch correct, but frighteningly so.</p> <pre><code>Public Sub TimeTest() Dim sw As New Stopwatch Dim row As Integer Dim val As Object Dim sheet As Microsoft.Office.Interop.Excel.Worksheet Dim dict As New Dictionary(Of Integer, Integer) Const iterations As Integer = 100000 Const elements As Integer = 10000 For i = 1 To elements + 1 dict.Add(i, i) Next sheet = _ExcelWorkbook.ActiveSheet sw.Reset() sw.Start() For i As Integer = 1 To iterations row = 1 + Rnd() * elements Next sw.Stop() Debug.WriteLine("Empty loop " & (sw.ElapsedMilliseconds * 1000) / iterations & " uS") sw.Reset() sw.Start() For i As Integer = 1 To iterations row = 1 + Rnd() * elements val = sheet.Cells(row, 1).value Next sw.Stop() Debug.WriteLine("Get cell value " & (sw.ElapsedMilliseconds * 1000) / iterations & " uS") sw.Reset() sw.Start() For i As Integer = 1 To iterations row = 1 + Rnd() * elements val = dict(row) Next sw.Stop() Debug.WriteLine("Get dict value " & (sw.ElapsedMilliseconds * 1000) / iterations & " uS") End Sub </code></pre> <p>Results:</p> <pre><code>Empty loop 0.07 uS Get cell value 899.77 uS Get dict value 0.15 uS </code></pre> <p>Looking up a value in a 10'000 element Dictionary(Of Integer, Integer) is <strong>over 11'000 times faster</strong> than fetching a cell value from Excel.</p> <p>Q.E.D.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload