Excel UDF 计算应返回 'original' 值

Question

我已经使用自己的 RTD 实现创建了一个 VSTO 插件，我从 Excel 工作表中调用该插件。为了避免在单元格中使用成熟的 RTD 语法，我创建了一个 UDF，从工作表中隐藏该 API。我创建的 RTD 服务器可以通过自定义功能区组件中的按钮启用和禁用。

我想要实现的行为如下:

• 如果服务器被禁用，并且在单元格中输入了对我的函数的引用，我希望该单元格显示 Disabled .
• 如果服务器已禁用，但该函数在启用时已在单元格中输入(因此单元格显示一个值)，我希望单元格继续显示该值。
• 如果服务器已启用，我希望单元格显示 Loading .

听起来很简单。以下是非功能代码的示例:

Public Function RetrieveData(id as Long)
  Dim result as String

  // This returns either 'Disabled' or 'Loading'
  result = Application.Worksheet.Function.RTD("SERVERNAME", "", id)
  RetrieveData = result

  If(result = "Disabled") Then

    // Obviously, this recurses (and fails), so that's not an option
    If(Not IsEmpty(Application.Caller.Value2)) Then

      // So does this
      RetrieveData = Application.Caller.Value2

    End If
  End If
End Function

该函数将在数千个单元格中被调用，因此将“原始”值存储在另一个数据结构中将是一项主要开销，我想避免它。此外，RTD 服务器不知道这些值，因为它也不保留其历史记录，或多或少出于相同的原因。

我在想可能有某种方法可以退出该函数，迫使它不改变显示的值，但到目前为止我还没有找到类似的东西。

编辑:
由于大众的需求，我想提供一些关于为什么我要做这一切的附加信息:正如我所说，该函数将在数千个单元格中被调用，并且 RTD 服务器需要检索相当多的信息。这对于网络和 CPU 来说都是相当困难的。为了让用户自己决定是否希望在自己的计算机上加载此负载，他们可以禁用服务器的更新。在这种情况下，他们仍然应该能够使用字段中当前的值来计算工作表，但不会将任何更新推送到其中。一旦需要新数据，就可以启用服务器并更新字段。

再说一次，由于我们在这里讨论相当多的数据，我宁愿不将其存储在工作表中的某个位置。另外，即使工作簿关闭并再次加载，数据也应该可用。

Answer 1

不同的策略=新答案。

我费尽心思才发现的一些东西，可能对你有用:

1.在 UDF 中，像这样返回 RTD 调用

' excel equivalent: =RTD("GeodesiX.RTD",,"status","Tokyo")
result = excel.WorksheetFunction.rtd( _
    "GeodesiX.RTD", _
    Nothing, _
    "geocode", _
    request, _
    location)

的行为就好像您在单元格中插入了注释函数，而不是 RTD 返回的值。换句话说，“结果”是“RTD-函数调用”类型的对象，而不是 RTD 的答案。相反，这样做:

' excel equivalent: =RTD("GeodesiX.RTD",,"status","Tokyo")
result = excel.WorksheetFunction.rtd( _
    "GeodesiX.RTD", _
    Nothing, _
    "geocode", _
    request, _
    location).ToDouble ' or ToString or whetever

返回实际值，相当于在单元格中键入“3.1418”。这是一个重要的区别；在第一种情况下，细胞继续参与 RTD 馈送，在第二种情况下，它仅获得恒定值。这可能是适合您的解决方案。

2.MS VSTO 看起来编写 Office Addin 似乎是小菜一碟……直到您真正尝试构建工业、可分发的解决方案。获得安装程序的所有特权和权限是一场噩梦，如果您有支持多个 Excel 版本的好主意，情况会变得更加糟糕。我一直在使用Addin Express已有几年了。它隐藏了所有这些 MS 的肮脏之处，让我专注于编写我的插件。他们的支持也是一流的，值得一看。 (不，我没有附属机构或类似的东西)。

3.请注意，Excel 可以随时调用 Connect/RefreshData/RTD，即使您正在处理某些事情 - 幕后正在进行一些微妙的多任务处理。您需要使用适当的同步块(synchronized block)来装饰您的代码，以保护您的数据结构。

4.当您接收数据(可能在单独的线程上异步接收)时，您绝对必须在最初(由 Excel)调用您的线程上回调 Excel。如果你不这样做，它会工作一段时间，然后你就会开始遇到神秘的、无法解决的崩溃，更糟糕的是，后台会出现孤立的 Excel。以下是执行此操作的相关代码示例:

    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

5.我做了一些显然类似于您在this addin中要求的事情。在那里，我从 Google 异步获取地理编码数据，并通过 UDF 阴影的 RTD 提供该数据。由于对 GoogleMaps 的调用非常昂贵，我尝试了 101 种方法和几个月的夜晚来将值保留在单元格中，就像您正在尝试的那样，但没有成功。我没有计时任何东西，但我的直觉是像“Application.Caller.Value”这样对 Excel 的调用比字典查找慢一个数量级。

最后，我创建了一个缓存组件，用于保存和重新加载从非常隐藏的电子表格中获取的值，该电子表格是我在 Workbook OnSave 中动态创建的。数据存储在字典(字符串，myQuery)中，其中每个 myQuery 保存所有相关信息。

它运行良好，满足离线工作的要求，甚至对于 20'000 多个公式，它看起来也是即时的。

HTH。

<小时/>

编辑:出于好奇，我测试了我的预感:调用 Excel 比进行字典查找要昂贵得多。事实证明，这种预感不仅是正确的，而且是可怕的。

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

结果:

Empty loop     0.07 uS
Get cell value 899.77 uS
Get dict value 0.15 uS

在 10,000 个元素的字典(整数、整数)中查找值比从 Excel 中获取单元格值快 11,000 倍以上。

Q.E.D.