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:<pre/>
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</pre>

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.<pre/>

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</pre>

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

 

[John McGraw]

Thanks again for the help.

Also, Russel mentioned a website. What would the URL be? Hopefuly I can learn something as well.

 

[ChipPearson]

Hi John,

You wrote in your follow up message:
>>
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. )
<<

When you use syntax like

Sub Procedure(X() )
'or
Sub Procedure(X() As type )

the pair of opening and closing parens immediately following the 'X' tell the compiler that it is expecting an actual array. If you omit the "As type", the compiler treats this as "As Variant" and so expects an "Array Of Variants". But in either case, the procedure expects an array (comprised of some sort of data) to be passed. This is the "Array Of Variants" case.

However, when you use syntax like

Sub Procedure(X )
' or
Sub Procedure(X As Variant)

the variable X can accept any type of variable, including an array. This is the "Variant Containing An Array" case.

Confused? I sure was when I started in with VB/VBA. It gets confusing because a Variant can be both an element of an array, and can contain within itself an array. (And, yes, you can certainly get yourself deeper into trouble by having a Varaint containing an Array, each element of which is a Variant containing an array of variants, and so on.)

Now remember, just because something is quick and easy to type doesn't mean that it is quick and easy to execute. I tend to avoid Variants like the plague, because when your code is actually executed, there is a great deal of "real" code that must run "figure out" just what is really in that Variant.

As far as efficiency (CPU time and memory usage) goes, Variants are always expensive. A single variant is expensive, and an array of variants only compounds the problem. A variant containing an array compounds that even more so.

The Collection object is very highly optimized (first, it was designed that way, and second, the actual Collection object is written in the C language, which supports things like memory pointers, and so is extremely fast).

If you really want my advice, I would do any "stack" data structure entirely with Collection objects, and forget about actual arrays. The slight performance hit you may take on the Collection is more than outweighed by ease-of-use factor and the stability of a Collection. At the bottom line, the only performance issue that really matters is what the user sees on his screen. Beyond that, it is all theoretical.

Suppose you have one method that is 1000 times faster than another. That is a big deal if you are comparing 1000 seconds to 1 second. It is meaningless if you are comparing 1/1000000 second to 1/1000 second.

Let the language work for you, don't work for the language. In your case, just use a Collection object and be done with it.

Also, you wrote..
>>
Also, Russel mentioned a website. What would the URL be? Hopefuly I can learn something as well.
<<

That URL would be http://www.cpearson.com. I have a few pages related to Excel there. I hope you find something useful.

Chip

 

[Juan Pablo González]

Chip, it's great to have you around. You can use BBCode to make your posts look a little prettier... I know you like to quote a lot using <<, well, with BBCode you can quote, and it looks much better. Just like this:

Quoted text here

Again, welcome to the board

 

[Mark O'Brien]

Since there's no point reinventing the wheel if you want to know more about arrays, here's a decent article:

[Edit: URL removed as the site linked to is now of a completely different, and inappropriate, nature!]

(yeah, I'm a bit slow to post this, sorry, I was informed of the presence of greatness in this thread. :wink: )

 

[Grothaal]

Hi,

I liked the Stack, but have a doubt:

How would you pop from it an determined element.

Lets say I add to it: "Apple", "Orange" , "Dog" as i am cheching for the state of some checkboxes.

Then when the user unchecks the "Dog" checkbox, I want to pop from the Stack the "Dog" element. But since the order the elements will be included will be randomic, how I determine wich element in the stack is housing the "Dog" element, to then use the Pop function?

I hope I was clear enough