VBA Stack – Using the VBA Stack in Excel

The VBA Stack represents a simple last-in-first-out (LIFO) non-generic collection of objects. The VBA Stack is useful in situations when you want to first process items which were added last. The VBA Stack can be compared to a stack of cards – you can put cards on top in a certain order but then you are expected to pick them up in the reverse order.

VBA Stack example

Dim stack as Object
Set stack = CreateObject("System.Collections.Stack") 'Create Stack

stack.Push "Hello"
stack.Push "There"
stack.Push "Mr"
stack.Push "Smith"

peekAtTopOfStack = stack.Peek()
doesContain = stack.Contains("htrh") 'Result: False
doesContain = stack.Contains("Hello") 'Result: True
    
'Get item from the top of the stack (LIFO)
topStack = stack.Pop()  'Result: "Smith"

'Clear the Stack
stack.Clear

Set stack = Nothing

Stacks are useful when in need of dynamic data structure that changes size, while adding and removing elements from it in the opposite sequence with which they were placed on the stack. A stack is a LIFO queue (last in first out). For FIFO data structure check out the VBA Queue.