1

Following the idea form this answer I'm trying to create a universal persistent storage in Excel using VBA but am having issues with the retrieval of stored object from the storage after VBA reset.

As a simplified example let's say I want to persist an instance of custom class 'MyClass':

'MyClass
Public someText as String

A simplified version of the storage:

Public Sub Save()
  Dim x As MyClass: Set x = New MyClass
  x.initialized = True
  Dim domain As mscorlib.AppDomain
  Set domain = GetAppDomain()
  domain.SetData "x", x
End Sub

Public Sub Load()
  Dim y As MyClass 'alternative: Object
  Dim domain As mscorlib.AppDomain
  Set domain = GetAppDomain
  If IsObject(domain.GetData("x")) Then
    Set y = domain.GetData("x") 'Type mismatch error
  End If
  Debug.Print y.initialized 'Run-time error '-2147418105 (80010007)'
End Sub

Public Function GetAppDomain() As mscorlib.AppDomain
  Dim host As New mscoree.CorRuntimeHost
  host.Start
  Dim Unk As IUnknown
  host.GetDefaultDomain Unk
  Set GetAppDomain = Unk
End Function

When I run Save and Load subs consecutively I get the desired output ("true"). However if I reset the application (Run > Reset in VBE) I get a Type mismatch error. When I change the type of y to Object I get an Automation error (Run-time error '-2147418105 (80010007)') when trying to print y.initialized

Originally I've tried to have a persisted Dictionary as in the link above, in that case I was able to get the Dictionary back from domain, the key was still there, but I was not able to cast the contained object back to it's original class.

Any ideas how to fix that?

Lukas Kavicky
  • 64
  • 6
  • Can you give us some more details on `MyClass`? What is its base-class, is it serializable? There are some restrictions about what kind of objects are allowed to cross app-domain boundaries. – Bradley Uffner Jul 10 '17 at 15:57
  • Yeh, I'd imagine that a custom class will not be retrievable. Can it be stringified? can Also be put into CustomXMLParts as well... – MacroMarc Jul 11 '17 at 11:00

0 Answers0