Solved: better Array support??? to avoid Dim, ReDim, Preserve etc

[callagga]

Hi guys,

Is there an add-on available that provides an easier way to do Dynamic Array type stuff? It seems in base VBA you need to be using Dim, ReDim, Preserve etc as the arrange grows/shrinks etc

What I'm really after is something like this:

Dim myArray() As String

myArray.add("first item")
myArray.add("second item")
myArray.add("third item")

if myArray.contains ("second item") Then
    Do XYZ...
    myArray.remove("second item")
end if

remainingSize = myArray.length

Thanks

[Bob Phillips]

You would need to write yourself a simple little class that does all of those things for you. Alan Beban has written an array add-in, but it has always looked inordinately complex to me. But remember, the main problem is knowing the size and so on, you will probably have to control that outside of an utility.

[Norie]

What 'stuff' do you actually want to do?

[callagga]

basically by this I meant I want to add/remove values from an in-memory list as part of the program, but I was really hoping not have to worry about all this ReDim'ing stuff. Really just want a basic collection object I can add/remove items from dynamically and let the language worry about size 'under the bonnet'.

Any hints/ideas welcome.

I'll google the array add-in that xld mentioned too.

PS. I wonder if there is a good Ruby interface to the Excel API's.

[Oorang]

Hi C,
In your visual basic editor go to the Tool Menu, Click "References" and Place a check mark next to "Microsoft Scripting Runtime" you can then use their "Dictionary Object" I think you will find this meets exactly the specification you laid out above.
[VBA]Public Sub ExampleUse()
Dim myCollection As Scripting.Dictionary

'If you don't want
myCollection "first item"
myCollection "second item"
myCollection "third item"

If myCollection.Exists("second item") Then
'Do XYZ...
myCollection.Remove ("second item")
End If
remainingSize = myCollection.Count
End Sub[/VBA]

[callagga]

this is excellent Oorang! Exactly what I need.

Thanks heaps for pointing this out!