Linked List Garbage Collection

[MalcSmith]

Hello all, my first post here and I hope that someone can help me.

In one of my Excel applications (well it may as well be Word, Access as it's mostly pure VBA but I am using Excel as I can give away the tool for others to use) I do a lot of number crunching.

And because of the amount of data and the fact that it won't fit into into a pre-2007 Excel spreadsheet (and this would be too slow anyway) I am doing the number crunching in VBA.

Now I am doing this by holding all the data in a linked list. The linked list can be rather large with each element in the list (a Class) containing about 120 variables or so and a pointer to the next list element.

Now, there is no doubt that the list works. I can create items at the end of the list and run up and down the thing as fast as I like.

I decided to use my own linked list rather than a Collection class (which is effectively the same thing but using a class which Microsoft has rolled rather than my own). The one drawback with a Collection is that it's too slow. With my linked list data structure I can do my analysis in ten minutes. On the same machine, with the same data it takes about twelve hours with the Collection. I have even put the data into an array in memory and that's also too slow.

So, what's then the problem? Well, the issue with VB/VBA is that there is no real mechanism for the programmer to force a dispose of an element. Instead we have to rely on Excel or the operating system to clear up after ourselves.

Now if the linked list were small this isn't an issue but this particular list that I have makes Excel crash when it tries to do its own data cleaning. The list works fine; the data is good and the analysis is good but I don't want Excel to crash each time I do a run. In this case there could be up to about 300,000 elements in the list.

Now, if I use a Collection or an array then I don't have this problem but Excel has a problem with its own garbage collection when I am trying to dispose of something this large.

So, any ideas? Is there any way that I can force Excel to actually dispose of an instanced object properly without it going Phut! on me?

Sorry for the long winded message but this is driving me nuts and I wondered if someone can spot The Really Obvious answer (othe than using C or C++).

Cheers
Malc

 

[shg]

Set the class instance to Nothing?

 

[MalcSmith]

I've set the main class which holds the root of the linked list to nothing and that still goes bang. I've even written code which goes through the list and sets the last item to nothing in a recursive fashion and Bang! is still the result.

When I do this in VB6 there seems to be no problems whatsoever, it just seems to be Excel. With VB6 I've done very large and complex linked list 'nets' and I can set the object holding the root of this list/net to Nothing and that clears up fine.

But, Excel can't seem to clear a long and simple linked list without killing itself which is not ideal.

I need to think more about this. Thanks for the feedback.

Diolch yn fawr
Malc

 

[antman3351]

Hello, i know this thread is old but i recently had the same problem on closing the application Excel would hang using up CPU. Seeing as i didn't find much help on the internet i posted what worked for me.
Mine was an array -> Linked list (using a class) -> 2nd linked list (using another class).
I found that is would create problems:
Private Type MainArr
ArrTypeList As New TypeList
End Type

Instead:
Private Type MainArr
ArrTypeList As TypeList
End Type



and then when i go to create the 1st list ("anchor" in the array):
Set ItemArr(i).ArrTypeList = New TypeList

and in Private Sub UserForm_Terminate()
I clear the lists starting from the bottom

 

[MalcSmith]

I got my code working in the end and it can handle massive linked lists; I sometimes have twenty odd thousand elements in my linked lists with about 100 items of data in each node.

Each node has two pointers; one to the next element in the list and also one to the previous element in the list. But for cleaning purposes I start at the end of the linked list and work backwards.


Private Sub CleanUpLinkedList()

' Walks through the linked list to clear it.

Dim pNode As zzzLinkedListNode
Dim pNextNode As zzzLinkedListNode


Sheets("Control").Range("Status") = "Cleaning Link List..."
DoEvents


Do While Not pLastNode Is Nothing

Set pNode = pLastNode.zPrevious
If Not pNode Is Nothing Then
Set pNode.zNext = Nothing
Set pLastNode = Nothing
Set pLastNode = pNode
Else
Set pLastNode = Nothing
Set pHeadNode = Nothing
End If
Loop


Set pHeadNode = Nothing

End Sub

zzzLinkedListNode is the class of the node of which the list is made of. In that class is a zPrevious and a zNext which point to the previous and next elements in the linked list. I prefix these with a z rather than a p because I want these to appear at the end of the AutoIntelligent list - this is my own style.

pLastNode and pHeadNode are pointers to the start and end of the linked list and these are maintained elsewhere.

Anyway, what happens here is that I start at the end of the linked list and then have a temporary pointer, pNode, point to the previous element in the list with Set pNode = pLastNode.zPrevious and then I break the connection to the last node with Set pNode.zNext = Nothing

I then set the last node, which is now almost orphaned with Set pLastNode = Nothing which now sets it for clearance

Finally I then set the pLastNode to the pNode and do it again until I get to the top of the list and then I make sure that the pHeadNode and the pLastNode are set to Nothing.

This rattles through the list in no time at all. On my machine I can clear a list of twenty five thousand items in a fraction of a second and I am not leaking any memory.

Hope that this is useful to someone.

- Malc