Hi,

This is hard to explain. Let me describe the situation:

Suppose I have a set of custom classes clsAbstract, cls1, cls2, and
cls3, where the last three classes implement clsAbstract, and then a
custom collection that only takes objects of type clsAbstract.

On a worksheet, in the Activate event, I set a global variable that
stores the locations of some "anchor" ranges which form the top left
corner of the areas containing information that cls1, cls2, and cls3
are going to need. The locations are stored as a string so I can use
Split to get the addresses. I know that cls1 will always be anchored
at the first address in the array, cls2 at the second, etc.

Now, what would be nice is if I could pass the "anchor" address to the
collection and let it decide which object it's going to use. I thought
that maybe I could have it looking at a table in another worksheet or
something, where you have, say:

A32 cls1
A56 cls2
A100 cls3

and then it uses the text in column 2 and sets the object reference to
a new object of type (whatever it looked up). Can I do this? I've
searched around a lot, but I can't figure out what to search for, much
less whether this is possible.

Jeremy,
If I understand correctly, you have a collection which controls access to a
number of classes (cls1, cls2, cls3, etc) that all implement the interface
clsAbstract (IAbstact would be a better name to indicate it is an
interface).

I was thinking that a loop through the array returned from Split and use
CreateObject, like:
Set clsTest = CreateObject("cls" & i)

But this will not work as I'm not sure how you would/could specify the
Application in this situation. If your classes were compiled into a DLL,
then
Set clsTest = CreateObject("YouDLL.cls" & i)
would work, but that is not the case here.
Maybe someone else can answer that.

So if you cannot use a string then hardcoding the sequence with New
statements is an alternative. Create an Init routine in your collection:

'<Collection code'local variable to hold collection
Private mCol As Collection

Public Function Init(RangeAddresses As String) As Long
Dim objNewMember As IAbstract

Dim Addresses As Variant
Dim i As Long

Addresses = Split(RangeAddresses, ",")

For i = 0 To UBound(Addresses)
Select Case i
Case 0
Set objNewMember = New cls1
Case 1
Set objNewMember = New cls2
Case 2
Set objNewMember = New cls3
Case Else
MsgBox "cls" & i & ": not available"
Exit For
End Select

objNewMember.AnchorRange = Addresses(i)
mCol.Add objNewMember
Next

Set objNewMember = Nothing

End Function
'Add your other normal Collection routines, although an .Add method may not
be needed.
'<Collection code

NickHK

"Jeremy" wrote in message
oups.com...
Hi,

This is hard to explain. Let me describe the situation:

Suppose I have a set of custom classes clsAbstract, cls1, cls2, and
cls3, where the last three classes implement clsAbstract, and then a
custom collection that only takes objects of type clsAbstract.

On a worksheet, in the Activate event, I set a global variable that
stores the locations of some "anchor" ranges which form the top left
corner of the areas containing information that cls1, cls2, and cls3
are going to need. The locations are stored as a string so I can use
Split to get the addresses. I know that cls1 will always be anchored
at the first address in the array, cls2 at the second, etc.

Now, what would be nice is if I could pass the "anchor" address to the
collection and let it decide which object it's going to use. I thought
that maybe I could have it looking at a table in another worksheet or
something, where you have, say:

A32 cls1
A56 cls2
A100 cls3

and then it uses the text in column 2 and sets the object reference to
a new object of type (whatever it looked up). Can I do this? I've
searched around a lot, but I can't figure out what to search for, much
less whether this is possible.

Actually (and this complicates matters), the classes are named
according to what they represent, so there's not an easy numbering
scheme (that's why I was thinking that, if I could set an object
reference using a string to refer to the object name, maybe I could
look it up in a table, return the string, and set the reference).

It's too bad CreateObject won't do what I need, but that's the type of
function I was hoping to find (and it didn't even occur to me to try
it, even though I know perfectly well it takes a string!). Does anyone
know of any other functions that could convert a string into an object
reference? I'm not really very experienced or advanced (it's a miracle
that I'm doing what I'm doing, in fact), and I'm fresh out of ideas.

I was hoping not to have to use a select case, but that's my fallback
option if there's not a more elegant way of doing it. I guess select
case isn't THAT bad, since there are only 10 possible classes in the
actual program, but I'm just so lazy.

NickHK wrote:
Jeremy,
If I understand correctly, you have a collection which controls access to a
number of classes (cls1, cls2, cls3, etc) that all implement the interface
clsAbstract (IAbstact would be a better name to indicate it is an
interface).

I was thinking that a loop through the array returned from Split and use
CreateObject, like:
Set clsTest = CreateObject("cls" & i)

But this will not work as I'm not sure how you would/could specify the
Application in this situation. If your classes were compiled into a DLL,
then
Set clsTest = CreateObject("YouDLL.cls" & i)
would work, but that is not the case here.
Maybe someone else can answer that.

So if you cannot use a string then hardcoding the sequence with New
statements is an alternative. Create an Init routine in your collection:

'<Collection code'local variable to hold collection
Private mCol As Collection

Public Function Init(RangeAddresses As String) As Long
Dim objNewMember As IAbstract

Dim Addresses As Variant
Dim i As Long

Addresses = Split(RangeAddresses, ",")

For i = 0 To UBound(Addresses)
Select Case i
Case 0
Set objNewMember = New cls1
Case 1
Set objNewMember = New cls2
Case 2
Set objNewMember = New cls3
Case Else
MsgBox "cls" & i & ": not available"
Exit For
End Select

objNewMember.AnchorRange = Addresses(i)
mCol.Add objNewMember
Next

Set objNewMember = Nothing

End Function
'Add your other normal Collection routines, although an .Add method may not
be needed.
'<Collection code

NickHK

"Jeremy" wrote in message
oups.com...
Hi,

This is hard to explain. Let me describe the situation:

Suppose I have a set of custom classes clsAbstract, cls1, cls2, and
cls3, where the last three classes implement clsAbstract, and then a
custom collection that only takes objects of type clsAbstract.

On a worksheet, in the Activate event, I set a global variable that
stores the locations of some "anchor" ranges which form the top left
corner of the areas containing information that cls1, cls2, and cls3
are going to need. The locations are stored as a string so I can use
Split to get the addresses. I know that cls1 will always be anchored
at the first address in the array, cls2 at the second, etc.

Now, what would be nice is if I could pass the "anchor" address to the
collection and let it decide which object it's going to use. I thought
that maybe I could have it looking at a table in another worksheet or
something, where you have, say:

A32 cls1
A56 cls2
A100 cls3

and then it uses the text in column 2 and sets the object reference to
a new object of type (whatever it looked up). Can I do this? I've
searched around a lot, but I can't figure out what to search for, much
less whether this is possible.