Destroying VBA object - memory error
I have a class object that I am creating with references to other classes (none of the other classes reference each other). I have a memory issue that gives an out of memory error when looping through and instantiating the class. Below is a simplified code snippet for a class and a subroutine:
AclsWell Class
Option Explicit
Option Compare Text
Option Base 1
Private zclsSettings As bclsSettings
Private zclsInfo As bclsInfo
Private zclsProduction As bclsProduction
Private Sub Class_Initialize()
Set zclsSettings = New bclsSettings: Set zclsSettings.Parent = Me
Set zclsInfo = New bclsInfo: Set zclsInfo.Parent = Me
Set zclsProduction = New bclsProduction: Set zclsProduction.Parent = Me
End Sub
Private Sub Class_Terminate()
Set zclsSettings.Parent = Nothing: Set zclsSettings = Nothing
Set zclsInfo.Parent = Nothing: Set zclsInfo = Nothing
Set zclsProduction.Parent = Nothing: Set zclsProduction = Nothing
End Sub
Module:
Sub Test1()
Dim zwell As aclsWell
For i = 1 To 2000
Set zwell = New aclsWell
Set zWell = Nothing
Next i
End sub
Upon completion of test1, excel is using approximately 1GB of data, and if I run again, I get an error. However, if I hit the stop button in the VBA window, the memory is cleared. Is there a way to simulate pressing the stop button using VBA (like Application.stopmacro or something similar). Or do I have a fundamental problem with how I close objects? Thanks a lot for any insight.
source to share
It's tricky when you have links in both directions. The terminate event never fires because the reference count is not zero when you set your object to Nothing. Thus, you cannot clear your links in the completion event.
One option is to create your own completion method.
Public Sub Terminate()
Set zclsSettings.Parent = Nothing: Set zclsSettings = Nothing
Set zclsInfo.Parent = Nothing: Set zclsInfo = Nothing
Set zclsProduction.Parent = Nothing: Set zclsProduction = Nothing
End Sub
Sub Test1()
Dim zwell As aclsWell
Dim i As Long
For i = 1 To 2000
Set zwell = New aclsWell
zwell.Terminate
Set zwell = Nothing
Next i
End Sub
Now when you step through the code, your Class_Terminate event will fire because the Terminate method took the reference count down to zero and VBA knew it could clean up the object.
The method I use is to store the parent's memory location in the child and as Long (or LongPtr in 64 bits). Read this post and especially Rob Bruce's comment in the comment section.
' In your child class
Private m_lngParentPtr As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(dest As Any, Source As Any, ByVal bytes As Long)
' The Parent property
Public Property Get Parent() As Class1
Set Parent = ObjFromPtr(m_lngParentPtr)
End Property
Public Property Set Parent(obj As Class1)
m_lngParentPtr = ObjPtr(obj)
End Property
'Returns an object given its pointer.
'This function reverses the effect of the ObjPtr function.
Private Function ObjFromPtr(ByVal pObj As Long) As Object
Dim obj As Object
' force the value of the pointer into the temporary object variable
CopyMemory obj, pObj, 4
' assign to the result (this increments the ref counter)
Set ObjFromPtr = obj
' manually destroy the temporary object variable
' (if you omit this step you’ll get a GPF!)
CopyMemory obj, 0&, 4
End Function
source to share