VBA Programmers: Passing Arrays?

[John McGraw]

I am trying to write a routine that will allow me to manipulate an array as a "Stack". I want to have a fixed size array that "pushes" all the elements to the left when a new element is added to the "top of the stack". So if I have an array with 8 elements, and I add a new element to the top of the "stack", all the 8 elements will move left one, and the 1st element will "pop off" as it will no longer be needed.

I wrote a function, but it does not work, because I do not seem to be able to pass a declared array to a sub. Here is the code:

Sub AddToStack(ItemToAdd As Variant, ParamArray StackArray() As Variant)

    Dim i As Long, Top As Long, Bottom As Long

    

    Bottom = LBound(StackArray())

    Top = UBound(StackArray())

    

    

    For i = Bottom To (Top - 1)

        StackArray(i) = StackArray(i + 1)  'move each item 1 position to the left

    Next i

    

    StackArray(Top) = ItemToAdd  ' add the "item to add" to the last position in the array

    

End Sub

The problem is that the Ubound and Lbound functions return 0, even though the array is obviously larger.
I think I am completely misunderstanding how to pass arrays to a procedure. VBA help is not helping either. So if anyone can help me out, I'd really appreciate it.

Here is the "test" sub that I use to test out the "addtostack" sub.

Sub test()

Dim tester(8) As Integer

Dim i As Integer



For i = 1 To 8

    tester(i) = i

Next i



AddToStack 9, tester()



End Sub

I get no errors, but it just treats the array that I pass as if it has no elements.

Thanks for any help!!!! This is driving me nuts.

Also, if there is any built-in functionality in VBA to manipulate an array as a stack, please let me know. I have a feeling I may be re-inventing the wheel on this one.

[ This Message was edited by: John McGraw on 2002-05-21 00:46 ]

[walding]

I can see a couple of problems:

1. Don't declare an array as an integer!

dim tester(8)

2. Cut out 'ParamArray'

Sub AddToStack(ItemToAdd As Variant, StackArray() As Variant)

Should work if you do that.

AW

[Russell Hauf]

First, to reply to the previous post, it's fine to declare your array as integer (in fact, if you know what the contents will be, it's preferred, as it will save space).

And you can also pass your array as integer:

Sub AddToStack(ItemToAdd As Integer, StackArray() As Integer)

-rh

[ChipPearson]

Don't declare the argument as a ParamArray. You use ParamArray (only as the last argument to a procedure) to indicate that the procedure may take an unspecified number of arguments. For example, if the SUM worksheet function were written in VBA, it would be declared having a ParamArray argument because SUM can take up to 29 arguments. When you pass an array (of any size) to a procedure, you are simply passing a single argument, even though that single argument happens to be an array of many elements. (When you pass an array to a function, it is always ByRef, and under the hood, VBA is passing the address of the SAFEARRAY header to the procedure -- the actual array itself is never passed.)

Your UBound and LBound functions returned 0, because the ParamArray contained only a single element, and that single element was an array-type variable of integers. An array with a single element has both an LBound and UBound equal to 0.

When you pass an array, you are still passing only a single parameter to the procedure, even though that single parameter may contain many elements.

If you want to implement a Stack data structure in VB/VBA, you can avoid "reinventing the wheel" and simply use a Collection object. The Add method is the equivalent of a "push" to the stack, and the Remove 1 method is the equivalent of a "pop" to the stack. Depending on the size of your arrays, and the data type of the array, you may take a performance hit with a Collection. Real arrays are more efficient for "small" stacks, but Collections are more efficient for "large" stacks. E.g.,

' Declare and initialize the stack
Dim Stack As Collection
Set Stack = New Collection

' Push something on to the stack
Stack.Add 1234

' Pop from the stack
Stack.Remove 1

[Anne Troy]

Hi, Chip!

[John McGraw]

Thanks for the help everyone!

I got it working, but only if I declare the array I am passing and the array in the argument list as the same data type.

I would like to create a more versitale "addtostack" routine, that can accept ANY kind of array. That is why I used "variant". But if I pass an integer array to a routine with a variant array as an argument, I get a "type mismatch" error.
(This suprises me because I thought a variant would never cause I type mismatch with any data type)

How could I fix this???

Thanks again.

[Russell Hauf]

You can fix it by using a collection - read Chip's post above.

[ChipPearson]

John,

As Russell Hauf and I indictated, you can implement a generic stack data structure using the Collection object rather than an array. In this case, you would pass the Stack collection object to the AddToStack and PopFromStack procedures. For example, something along the lines of the following:

Dim Stack As New Collection

Sub AddToStack(WhatStack As Collection, WhatItem As Variant)
WhatStack.Add WhatItem
End Sub

Sub PopFromStack(WhatStack As Collection)
WhatStack.Remove 1
End Sub

You would then call these procedures with code like

AddToStack Stack,12345
' and
PopFromStack Stack

In the 2 lines of code above, "Stack" is the name of the variable that is declared "As [New] Collection". You then pass that "Stack" variable to AddToStack and PopFromStack, indicating which stack you want to manipulate. If you application used several stack data structures, both the AddToStack and PopFromStack procedures would work for all the stack structures. You would just pass the appropriate variable to the procedure.

In this code, you are never dealing with arrays at all. The "array-like" functionality is all wrapped up inside the Collection object, and you never have to worry about it. VBA does it all internally for you.

The advantage of using a Collection object as opposed to an array is that 1) each item or element within a collection can be a diffrent data type (i.e, a Collection can contain strings, longs, doubles, objects, and so on), and 2) the internal code that actually makes a Collection "work" is highly optimized (using something called "doubly linked lists" if you are interested) and runs much faster than any VB/VBA code you or I could ever write, and 3) you can use a For Each loop against a Collection (yes, you can use For Each against an array, too, but it is extremely inefficient).

In your follow-up message, you wrote that
>>
I got it working, but only if I declare the array I am passing and the array in the argument list as the same data type.
<<

That is the expected and "by-design" behavior. If, for example, you declared an array "As Interger" and attempted to pass it to a procedure that expected an array "As Long" the arithemetic that is used internally to determine where, say, the 5th element in the array actually resides, would be "out of whack". This is because Longs are bigger than Integers. When you pass around arrays, the data type of the elements must match.

You could implement your "array-based" stack by declaring the array "As Variant". Because a Variant can contain any type of data (integers, longs, doubles, object pointers, etc), you would have a generic "stack" that could hold any type of data. But if you are going to declare an array of Variants to implement your Stack structure, you might as well use the Collection object (since this is precisely what Collection does, internally) and let VBA do the work for you. The Collection will do it much more efficiently than you or I code ever code for.

I hate to ramble on (although I am wont to do that from time to time), but it is worth noting, just for completeness, that there is a difference between an "Array Of Variants" and a "Variant Containing An Array". It is easy to confuse the two (and I've blown up my share code confusing them), but they are fundementally different. For example, if you declare a variable "Dim V As Variant" the variable V can contain an array, and you might assign an array to that variable with something like "V = Array(1,2,3)". In this case, V is a Variant that contains an array (actually, a reference to an array), but V is itself not an array. The variable V is a Variant, not an array. This is an example of a "Variant Containing An Array".

The converse, so to speak, of this is an "Array Of Variants". Here, you would declare the variable as "Dim Arr(0 To 2) As Variant". The variable Arr is itself indeed an array, not a Variant, and each element of the Arr array is a Variant type variable.

The bottom line, I guess, is that if you want to implement a "stack" data structure in your code, forget about arrays entirely. Use a Collection object and be done with it, let VBA do the work for you. If VBA can do the work for you, let it.

This is only my second post to the MrExcel message boards, so I hope I didn't step on anyone's toes. I'll leave it to one of the MrExcel MVPs to discuss the difference between an array and a SAFEARRAY.

Chip

[Russell Hauf]

Don't anyone take Chip's comment "This is only my second post..." seriously. He is one serious Excel Guru.

I've learned much from his website.

And Chip, feel free to ramble anytime...

[John McGraw]

Chip,

Thanks for the thoughtful explenation. I can see that I was confused between a "variant array", and an "array of variants". If I do this:

Sub AddToStack(ItemToAdd As Variant, StackArray As Variant)

Without the "()", I find I can pass any type of array.

Would the above be more effeciant than using a collection? (I am guessing that it would be, since it is not an array of variants... But what do I know. )

Thanks again for the clear explenation... I really appreciate it.

[ This Message was edited by: John McGraw on 2002-05-21 13:10 ]