Why can't I get properties from members of this collection?
I have added some form elements to the collection and can get their properties when I reference the members by index.
However, when I try to use any properties by referencing the members of the collection, I see "Cannot set the ControlSource property. Member not found. Error in the Locales window."
Here's a simplified version of the code:
'Add controls to collection'
For x = 0 To UBound(tabs)
activeTabs.Add Item:=Form.MultiPage.Pages(Val(tabs(x, 1))), _
key:=Form.MultiPage.Pages(Val(tabs(x, 1))).Caption
Next x
'Check name using collection index'
For x = 0 To UBound(tabs)
Debug.Print "Tab name from index: " & activeTabs(x + 1).Caption
Next x
'Check name using collection members'
For Each formTab In activeTabs
Debug.Print "Tab name from collection: " & formTab.Caption
Next formTab
Results in the Immediate window:
Tab name from index: Caption1
Tab name from index: Caption2
Tab name from collection:
Tab name from collection:
Why does one method work and the other not?
This is in the standard code module, but I have similar code that works great with form modules. Could this have anything to do with this?
Edited to add
formTab was declared as a control, but I found that if it is declared as an object, then the code works.
This will probably solve my problem, but in the interest of further developing my knowledge, I would be grateful for any explanation of this behavior, especially in relation to the difference in how the code works in different types of modules.
a source to share
This is a really big question. Your edit at the end of the post reveals a lot about how VBA works and what's going on here. I'm not 100% what's going on, but I'll explain what I think is going on.
A Collection
in VBA (and VB6, for that matter, the same code base) is not strongly typed. This means that everything in the collection is technically an "object". In the .NET world (since .NET 2.0) it is possible to have strongly typed collections so that you can say "everything in this collection is an object Control
". In VBA, this is not possible with Collection
.
In your first iteration, where you are referencing an item that is indexed in the collection activeTabs
is activeTabs(x + 1)
referencing object
. When you tell VBA to look for .Caption
that object, it doesn't know what the base type is (I think), so it should just see if there is a base object type that contains a property or method called Caption
. As you can see, Tab controls actually contain a property Caption
.
In your second interaction where you are looping For Each
, I think the problem is that the type Control
probably doesn't have a property called Caption
, although different types of controls probably do. For example, a text box control probably doesn't have a property Caption
, whereas a label control has a property Caption
.
You have several options for fixing the second loop. 1) You can declare formTab
as a Tab control (I don't know exactly what it called). The Tab control must have a property Caption
. 2) If each control in is activeTabs
not a special Tab control (in which case you should probably call it activeControls
instead activeTabs
), you can check inside your loop to see if formTab
the tab control is valid . If so, add it as a Tab control and then call .Caption
. Until you draw it as a Tab control, VBA will not know that it has a property Caption
, since a normal object Control
does not have a subtitle property.
In the end, you can get rid of using objects like in the first loop and let the runtime figure out what to do, but this can give very poor performance. In general, it is better to work with your concrete types in a strongly typed language. It also helps show in your code that you know exactly what you are working with, rather than leaving it at runtime to decide which properties and methods you can work with.
a source to share