Can an array hold object variables?

[Glory]

I've got some code working that links controls with code, but I need it to work dynamically. Arrays have to be type Variant. Is there a way to fill an array with objects, some kind of type conversion that will work?

 

[Colin Legg]

Hi Glory,

Arrays have to be type Variant. Is there a way to fill an array with objects, some kind of type conversion that will work?

VBA Arrays don't have to be of type Variant. You can have an array of whatever you want. For example, here's a dynamic String array:

Sub foo()
 
    Dim strarrNames() As String
 
    ReDim strarrNames(0 To 1)
 
    strarrNames(0) = "Glory"
    strarrNames(1) = "Colin"
 
End Sub

And here's a dynamic Object array:

Sub foo()
 
    Dim objarrNames() As Object
 
    ReDim objarrNames(0 To 1)
 
    Set objarrNames(0) = Worksheets(1)
    Set objarrNames(1) = Workbooks(2)
 
End Sub

Perhaps you could give us a specific example to work with?

 

[tusharm]

As has already been pointed out, an array doesn't have to be of type variant. And, if it is, variant means it can contain data of any type include an object!

Sub showArr()
    Dim Arr(2)
    Arr(0) = 1
    Arr(1) = "a string"
    Set Arr(2) = Application
    Debug.Print Arr(0) & ", " & Arr(1) & ", " & Arr(2).Caption
    End Sub

I've got some code working that links controls with code, but I need it to work dynamically. Arrays have to be type Variant. Is there a way to fill an array with objects, some kind of type conversion that will work?

 

[Glory]

Colin: I want to try to do the work myself if I can, but I'll be coming back here if I hit a wall, rest assured.

Great examples from both of you, that should be plenty to work with.

 

[Glory]

ADVERTISEMENT

I'm having problems getting the array to survive between separate subs in a class module because I can't declare the array at module-level (compile error) and I have no idea how to make it into a property.

Could anybody help me out with that?

 

[tusharm]

OK, you need to share your code because an array can definitely be declared at the module level.

I'm having problems getting the array to survive between separate subs in a class module because I can't declare the array at module-level (compile error) and I have no idea how to make it into a property.

Could anybody help me out with that?

 

[Glory]

ADVERTISEMENT

It looks like they can't be public: "Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules".

 

[Glory]

And that's the problem, see; I need the obejct variable to survive between procedures in my class module. So I've wound up using a collection that's dimensioned outside the userform object module that's using the class module.

eg:

Public MyCollection As New Collection
 
Public Sub Test()
 
Userform1 Show
 
End Sub

 

[Colin Legg]

Hi,

It can be declared as Private at a module level in the class module. It will then retain its value as long as the object (instance of the class) survives and it can be accessed by all members of the class. If it needs to be accessed from outside of the class module then you can create a Property which exposes it.

If you need to hold the value even after the the object has been destroyed then it would have to be declared outside of the class module as you have done.

I hope that makes sense but if an example would also help then just ask.

 

[Glory]

Colin,

I have a feeling that I'm trying to make untraditional use of a class module. Here's what I'm trying to do, maybe you can let me know if I'm off on the wrong track with this.

I have a userform which I've left blank. I've chosen to populate it from a series of class modules at runtime using the Userform Initialize event. Each control that eventually appears on the userform has its own class module.

The controls are designed to affect one another (visible true/false, enabled true/false, value true/false, etc). The problem is that all the controls appearing at runtime.

I've used DoEvents to get the controls to execute code, but I can't get the code from one class module to recognize objects variable (the control) created in other class modules... without assigning all the object variables to public variables dimensioned externally (in the module containing the Show statement, for instance, like I posted above).

Instead of drowning myself in public variables, I've used collections to consolidate like controls. That's the only solution I've found so far, but I'm wide open to suggestions.