Can an array hold object variables?

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
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?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
Code:
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:
Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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!
Code:
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

Well-known Member
Joined
Mar 16, 2011
Messages
640
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

Well-known Member
Joined
Mar 16, 2011
Messages
640

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Well-known Member
Joined
Mar 16, 2011
Messages
640

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

Well-known Member
Joined
Mar 16, 2011
Messages
640
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:

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

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 16, 2011
Messages
640
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top