在Excel VBA中如何通过“状态丢失”来保存关键变量（不写入单元格或文件）？ [英] In Excel VBA how to persist key variables over a 'state loss' (without writing to a cell or a file)?

问题描述

Excel VBA是一个灵活的开发环境。它是pesudo编译的。然而，有时在开发过程中可能会发生国家损失。所有变量都被拆除时，状态损失。事实上，VBA有一个选项通知状态损失选项进行分类。在所有情况下都不能编辑和继续代码是不足为奇的。
然而，有时国营损失在生产中运行时发生，因为打开其他工作簿可能会导致您的应用程序会话的创伤（相信我，发生！）

我知道一个人可以将数据保存到一个工作表单元格或一个文件中，但这不适合尝试保留一个类的实例，特别是如果这是整个对象图的锚点。

所以在一个人坚持一个记忆保持变量的情况下你如何坚持状态在一个国家的损失？

解决方案

在Excel的生命周期中保持数据持久的一种方法是将它们存储在附加到实例的默认的.Net域中：

  Sub Usage（）
 Dim dict As Object 
 Set dict = GetPersistentDictionary（）
 End Sub 
  pre> 
 
 
 公共函数GetPersistentDictionary（）As Object 
 '参考：
'mscorlib.dll 
'通用语言运行时执行引擎
 
 Const name =weak-data
静态dict作为对象
 
如果dict is Nothing Then 
 Dim host As New mscoree.CorRuntimeHost 
 Dim domain As mscorlib.AppDomain 
 host.Start 
 host.GetDefaultDomain domain 
 
如果IsObject（domain.GetData（name））然后
设置dict = domain.GetData（name）
 Else 
设置dict = CreateObject（Scripting.Dictionary）
 domain.SetData name，dict 
 End If 
 End If 
 
设置GetPersistentDictionary = dict 
结束函数
  
 
Excel VBA is a flexible development environment.  It is pesudo-compiled.  However, sometimes during development a "state loss" can occur.  A "state loss" is when all variables are torn down. Indeed, VBA has an option "Notify before state loss" option for triage.  It is  unsurprising that one cannot Edit and Continue code in all cases.
However, sometimes state losses happen whilst running in production because opening some other workbook may cause trauma to your application session (trust me, it happens!) 

I know one can persist data to a worksheet cell or even a file but this is inappropriate for trying to retain an instance of a class, especially if that is the anchor for a whole object graph.

So in the case where one insists on a memory held variable how do you persist state over a state loss?
解决方案
One way to keep the data persistent during the lifetime of Excel is to store them on the default .Net domain attached to the instance:
Sub Usage()
    Dim dict As Object
    Set dict = GetPersistentDictionary()
End Sub


Public Function GetPersistentDictionary() As Object
    ' References:
    '  mscorlib.dll
    '  Common Language Runtime Execution Engine

    Const name = "weak-data"
    Static dict As Object

    If dict Is Nothing Then
      Dim host As New mscoree.CorRuntimeHost
      Dim domain As mscorlib.AppDomain
      host.Start
      host.GetDefaultDomain domain

      If IsObject(domain.GetData(name)) Then
        Set dict = domain.GetData(name)
      Else
        Set dict = CreateObject("Scripting.Dictionary")
        domain.SetData name, dict
      End If
    End If

    Set GetPersistentDictionary = dict
End Function


                        
这篇关于在Excel VBA中如何通过“状态丢失”来保存关键变量（不写入单元格或文件）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！