VB and collections

[Monty Python]

How do I create a collection of items in VB? I want to create a collection of items (cells, form controls, charts) and set a particular property uniformly among them.

I had imagined something like this:

Dim Something as Collection

Something.Add (Label1)
Something.Add(Label2)
Something.Add(Label3)

For each Thingy in Something
Thingy.caption = "Genetics Experiment"
next Thingy

Any VB professionals out there can help me?

[NoName]

{Edited because I'd rather pretend I never said anything here}

[Brad Gile]

Suppose I have a userform (imaginatively named Userform1) with three textboxes and a command button:

Private Sub UserForm_Click()

Dim C As Object
Dim ctl As Object

Set C = UserForm1.Controls

For Each ctl In C
If IsTextbox(ctl) Then
ctl.Text = "Hi"
End If
Next ctl

End Sub

Private Function IsTextbox(ByVal obj As Object) As Boolean
Dim s As String

On Error GoTo Finish
s = obj.Text
IsTextbox = True
Exit Function

Finish:
IsTextbox = False

End Function

In this case, I have looped through all controls on the form and assigned the text property of each Textbox on the form="Hi". I need the private IsTextbox function to weed out those controls in the (built in) collection Controls (which has to be dimmed as Object, not Collection) that are NOT textboxes (e.g., the command button!).
It is also possible to create a collection consisting of the 3 textboxes, but this really isn't necessary.

Brad

[Monty Python]

I like your solution, Mr. Gile.

Now imagine that I have 10 text boxes, but only want to set attributes for 8 of them?

I would also like to see how a collection is defiend because I have other applications for its use. I used the example of text boxes because it was easier to convey.

[snafu]

A rather simple example but one that will hopefully help get you started.
I used excel because it was a little easier but the collection object functions essentially the same for Excel VBA, Access VBA, and pure VB.


Public Sub Test()

Dim TestCollection As New Collection
Dim msg As String
Dim strCellName As String
Dim rng As Range

msg = "Enter a cell address (i.e. B10)." & vbCrLf
msg = msg & "Press Cancel when done entering addresses."

Do While True
'NOTE: No error checking for cell address validity
'i.e. If you enter an invalid cell address
'such as "Emperor Zurg", you will crash the program

strCellName = InputBox(msg, "Cell Addressed for Collection Example")
If strCellName = "" Then
Exit Do
Else
TestCollection.Add Range(strCellName)
End If
Loop

For Each rng In TestCollection
rng.Value = rng.Address(ReferenceStyle:=xlA1)
Next rng

End Sub

[Brad Gile]

I like your solution, Mr. Gile.

Now imagine that I have 10 text boxes, but only want to set attributes for 8 of them?

I would also like to see how a collection is defiend because I have other applications for its use. I used the example of text boxes because it was easier to convey.

In the 10 text box case, you probably want to use a control array for the text boxes, indexed 0 to 9. Have it so that boxes 0 to 7 will have the property you want.

Generally speaking, collections are a generalized type of array in that (a) the elements can be mixed types (any value or object data type) and (b) they do not have to be dimmed to a constant size. Here's a simple (Excel again, but that's unimportant) example of a collection:

Sub CollectionDemo()
Dim C As Collection
Dim Rng As Range
Dim dbl As Double
Dim str1 As String
Dim NumberOfElements As Integer

Set C = New Collection

dbl = 3.1416
str1 = "Rebel Outpost Forum"
C.Add Range("h4:h6")
C.Add dbl
C.Add str1
NumberOfElements = C.Count

Set Rng = C.Item(1)
'Put information about the elements in the cells
'of Range("h4:h6")=Item 1 of C.

Rng(1).Value = C.Item(1).Address
Rng(2).Value = C.Item(2)
Rng(3).Value = C.Item(3)

End Sub

Note that a peculiarity of collections is that their elements are ALWAYS indexed starting with 1 rather than zero! Go figure. :D


Brad

[Pi Man]

dbl = 3.1416
Brad Gile = Pi Man!!! pass it on!

[Wino In Training]

Tan: Collections sound like APL enclosed arrays, in terms of their ability to be of mixed types/sizes. Powerful stuff.

[Cho Da]

Tan: Collections sound like APL enclosed arrays, in terms of their ability to be of mixed types/sizes. Powerful stuff.Actually it's a Smalltalk (http://www.smalltalk.org/) concept. Everything is an Object.

[PK]

the collection object functions essentially the same for Excel VBA, Access VBA, and pure VB.

I am particularly angry with Microsoft after struggling with a different version of Excel that I recently used where nothing seemed to work the same or be in the same place as the version I was used to using, so I had to comment.

Only Microsoft appears to arrogant enough to think that there should be different versions of VB for MS than exist for the rest of the world.

Please feel free to substitute HTML or Java in the sentence above for VB.

P

[Cho Da]

Only Microsoft appears to arrogant enough to think that there should be different versions of VB for MS than exist for the rest of the world.

Please feel free to substitute HTML or Java in the sentence above for VB.

PUh, when it comes to VB M$ is the end of the story. It's their language. There is no standard. the rest of the world doesn't care.

[Brad Gile]

dbl = 3.1416
Brad Gile = Pi Man!!! pass it on!
ROFL!
Brad Gile = Pi Man
Brad Gile = Bill Gates
Sooo,
Pi Man = Bill Gates?

Brad<---sorry, PM, I'm NOT sharing Bill's/my money! :D

[PK]

Uh, when it comes to VB M$ is the end of the story. It's their language. There is no standard. the rest of the world doesn't care.

I will admit to very little experience with VB, but what I picked up from the original post was that Excel VB is different from Access VB which is different from raw VB. The fact that these are all Microsoft products does not excuse them from willfully not following any standards -- whether they be in-house standards or external standards.

[Cho Da]

Uh, when it comes to VB M$ is the end of the story. It's their language. There is no standard. the rest of the world doesn't care.

I will admit to very little experience with VB, but what I picked up from the original post was that Excel VB is different from Access VB which is different from raw VB. The fact that these are all Microsoft products does not excuse them from willfully not following any standards -- whether they be in-house standards or external standards.The language is not so much different as is the object model to which it applies. Access != Excel. Given that M$ did make some significant changes to VB the last time they incremented the version number. Those changes (enhancements?) did not get into the VB for applications in a consistent timeframe: Excel was first (even before VB6).