LIFO (Stack) Algorithm/Class for Excel VBA

问题:

I'm looking to implement a "Stack" Class in VBA for Excel. I want to use a Last In First Out structure. Does anyone came across this problem before ? Do you know external libraries handling structure such as Stack, Hastable, Vector... (apart the original Excel Collection etc...)

Thanks

回答1:

Here is a very simple stack class.

Option Explicit
Dim pStack As Collection
Public Function Pop() As Variant
    With pStack
        If .Count > 0 Then
            Pop = .Item(.Count)
            .Remove .Count
        End If
    End With
End Function
Public Function Push(newItem As Variant) As Variant
    With pStack
        .Add newItem
        Push = .Item(.Count)
    End With

End Function
Public Sub init()
    Set pStack = New Collection
End Sub

Test it

Option Explicit
Sub test()
    Dim cs As New cStack
    Dim i As Long
    Set cs = New cStack
    With cs
        .init

        For i = 1 To 10
            Debug.Print CStr(.Push(i))
        Next i

        For i = 1 To 10
            Debug.Print CStr(.Pop)
        Next i
    End With
End Sub

Bruce

回答2:

Bruce McKinney provided code for a Stack, List, and Vector in this book (it was VB5(!), but that probably doesn't matter much):

http://www.amazon.com/Hardcore-Visual-Basic-Bruce-McKinney/dp/1572314222

(It's out of print, but used copies are cheap.)

The source code appears to be available here:

http://vb.mvps.org/hardweb/mckinney2a.htm#2

(Caveat - I've never used any of his code, but I know he's a highly regarded, long-time VB expert, and his book was included on MSDN for a long time.)

I'm sure there are also many different implementations for these things floating around the internet, but I don't know if any of them are widely used by anybody but their authors.

Of course, none of this stuff is that hard to write your own code for, given that VBA supports resizeable arrays (most of the way to a vector) and provides a built-in Collection class (most of the way to a list). Charles William's answer for a stack is about all the info you need. Just provide your own wrapper around either an array or a Collection, but the code inside can be relatively trivial.

For a hashtable, the MS Scripting Runtime includes a Dictionary class that basically is one. See:

Hash Table/Associative Array in VBA

回答3:

I do not know of any external VBA libraries for these structures. For my procedure-call stack I just use a global array and array pointer with Push and Pop methods.

回答4:

You can use the class Stack in System.Collections, as you can use Queue and others. Just search for vb.net stack for documentation. I have not tried all methods (e.g. Getenumerator - I don't know how to use an iterator, if at all possible in VBA). Using a stack or a queue gives you some nice benefits, normally not so easy in VBA. You can use

anArray = myStack.ToArray

EVEN if the stack is empty (Returns an array of size 0 to -1).

Using a custom Collections Object, it works very fast due to its simplicity and can easily be rewritten (e.g. to only handle strongly typed varibles). You might want to make a check for empty stack. If you try to use Pop on an empty stack, VBA will not handle it gracefully, as all null-objects. I found it more reasonable to use:

If myStack.Count > 0 Then

from the function using the stack, instead of baking it into clsStack.Pop. If you bake it into the class, a call to Pop can return a value of chosen type - of course you can use this to handle empty values, but you get much more grief that way.

An example of use:

Private Sub TestStack()
    Dim i as long
    Dim myStack as clsStack

    Set myStack = New clsStack
    For i = 1 to 2
        myStack.Push i
    Next

    For i = 1 to 3
        If myStack.Count > 0 Then
            Debug.Print myStack.Pop
        Else
            Debug.Print "Stack is empty"
        End If
    Next

    Set myStack = Nothing
End Sub

Using a LIFO-stack can be extremely helpful!

Class clsStack

Dim pStack as Object
Private Sub Class_Initialize()
    set pStack = CreateObject("System.Collections.Stack")
End Sub
Public Function Push(Value as Variant)
    pStack.Push Value
End Function
Public Function Pop() As Variant
    Pop = pStack.Pop
End Function
Public Function Count() as long
    Count = pstack.Count
End Function
Public Function ToArray() As Variant()
    ToArray = pStack.ToArray()
End Function
Public Function GetHashCode() As Integer
    GetHashCode = pStack.GetHashCode
End Function
Public Function Clear()
    pStack.Clear
End Function
Private Sub Class_terminate()
    If (Not pStack Is Nothing) Then
        pStack.Clear
    End If
    Set pStack = Nothing
End Sub