How to release an object and clear memory in VBA

2019-06-27 23:19发布

问题:

thats the first time i post plus im an amateur programmer, so let me know if you need any additional information. i have the following problem:

with excel VBA i connect to another programm (namely Aspen EDR). For that purpose I have an according Add-In installed. to access Aspen EDR i need to add an object. after im done i want to release the object to save some memory. first thing i tried is this:

Dim ObjEDR      As BJACApp
Dim Path        As String

Path = 'assume this is the correct path to the file i want to open
Set ObjEDR = New BJACApp ' Create the BJAC object

If Not ObjEDR.FileOpen(Path) Then
    MsgBox "Can't open file!"
End If

'...

Set ObjEDR = Nothing

My Problem is: after i set the object nothing, excel does not release the memory (as i can see in my task manager). of course after a few hundred iterations (i have to open a lot of these files) i get an error message, that excel is out of memory. i read a few threads and apparently nothing only deletes some kind of reference to the object but not the object itself, so i tried adding fileclose

'...

ObjEDR.FileClose
Set ObjEDR = Nothing

when executing the FileClose i can see that a little memory is released (0,5 of 3MB) but still there is a lot of memory accumulating.

Thanks for the help :)

回答1:

Remove the New keyword, there is no need in it.

Dim ObjEDR      As BJACApp
Dim Path        As String

Path = 'assume this is the correct path to the file i want to open
Set ObjEDR = BJACApp ' Create the BJAC object

If Not ObjEDR.FileOpen(Path) Then
    MsgBox "Can't open file!"
End If

'...

ObjEDR.FileClose
Set ObjEDR = Nothing