Excel UDF 计算应返回“原始”价值

Question

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

我想要实现的行为如下：

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

听起来很容易。这是非功能代码的示例：

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，即使您正在处理某些事情 - 幕后正在进行一些微妙的多任务处理。您需要使用适当的同步块来装饰您的代码以保护您的数据结构。

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.
我所做的事情显然类似于您在此插件中提出的要求。在那里，我从 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 个元素的字典（Integer, Integer）中查找值比从 Excel 中获取单元格值快 11,000 倍以上。

量子电动力学

Answer 2

也许...尝试使您的 UDF 包装函数成为非易失性的，这样除非其参数之一发生更改，否则它不会被调用。

当您启用服务器时，这可能会出现问题，您必须欺骗 Excel 再次调用您的 UDF，这取决于您想要执行的操作。

也许解释一下您要实现的完整功能？

Answer 3

您可以尝试 Application.Caller.Text
这具有从渲染层作为文本返回格式化值的缺点，但似乎避免了循环引用问题。
注意：我尚未在所有可能的情况下测试此 hack情况 ...