VBA: Out of memory Error due to memory leak.... Can I manually call Garbage Collection?

Novice programmer here. I have a code that copies 12 hours worth of data from a server and displays it in excel. My code takes the displayed code and exports it into a file, appending each 12 hours so that I can have a months worth of data.

My problem is that after 20 days or so I run out of memory. Theoretically, it shouldn't take much more data than the original program and it running out of memory after 20 days says to me memory leak. In an old java program I had I just called the garbage collector with some frequency and the problem went away. Is there some way to do this in excel-vba? I've read about setting variables to nothing, but I have a lot of variables and I think th开发者_如何学Pythone real issue has to do with it storing ALL of the read-in data as ram, and I don't know how to set that to zero.

Other curious bit - after it crashed due to memory I cannot begin the program again without shutting down excel. So after crashing it doesn't delete things in memory?

Thanks for any help

As far as I understand your question, your Excel program is still running and remains open every day (that's what I've understood from after 20 days of running).

Using a Set myVar = Nothing is still a best practice but let say you don't want to do this.

What I can think of is to create a new Excel instance to run your code within and close your application at the end of your running code.

Something like:

'Don't forget to add the reference ..  
'Microsoft Excel X,X object library 

Sub myTest()
Dim xlAPp As New Application 

' your code

Set xlApp = Nothing
End Sub

VBA doesn't have garbage collection in the traditional sense -- so the answer to your particular query is no -- but keeps a count of referencing. As such, to free memory, you need to do as you suggest and dereference your objects, whenever they're no longer needed.