Class Module w/Collection

[Guest]

I'm new to class modules. As an exercise, I thought It might be useful to
create a collection of properties in a class, so one could iterate through
the properties by number.

I tried varous combinations of the following:

Private Sub Class_Initialize()
Set Me.Properties = New Collection
Me.Properties.Add Me.BusinessAddress, "BusinessAddress"
Me.Properties.Add Me.BusinessAddressCity, "BusinessAddressCity"
Me.Properties.Add Me.BusinessAddressCountry, "BusinessAddressCountry"
End Sub

I expected to be able to create a pointer to the property as item in the
collection, such that setting the property would update the item in the
collection. But that does not work; instead the old value is retained.

I can create a subroutine to update the value of each item in the collection
as it's property is set, but I wonder:

Is it possible to add properties to a collection such that updating the
property's
value causes the collection's item (corresponding to the property) to
take the
new value of the property? Similarly, updating the collection item's
value would
update the corresponding property.

If I am completely misunderstanding what I'm supposed to be doing here, with
classes and collections, please enlighten me.
Thank you in advance for your time.

George

 

[Guest]

I'm new to class modules. As an exercise, I thought It might be useful to
create a collection of properties in a class, so one could iterate through
the properties by number.

I tried varous combinations of the following:

Private Sub Class_Initialize()
Set Me.Properties = New Collection
Me.Properties.Add Me.BusinessAddress, "BusinessAddress"
Me.Properties.Add Me.BusinessAddressCity, "BusinessAddressCity"
Me.Properties.Add Me.BusinessAddressCountry, "BusinessAddressCountry"
End Sub

I expected to be able to create a pointer to the property as item in the
collection, such that setting the property would update the item in the
collection. But that does not work; instead the old value is retained.

I can create a subroutine to update the value of each item in the collection
as it's property is set, but I wonder:

Is it possible to add properties to a collection such that updating the
property's
value causes the collection's item (corresponding to the property) to
take the
new value of the property? Similarly, updating the collection item's
value would
update the corresponding property.

Me.Properties only refers to the properties collection of the Access
database, not the class. To declare and use properties in a class module, you
need something like:

Dim m_strBusAddr As String
Public Property Let BusinessAddress(ByVal busAddress As String)
strBusAddr = busAddress
End Property

Public Property Get BusinessAddress() As String
BusinessAddress = strBusAddr
End Property

You collection class would need to store a collection of objects in a
similar way, except that you'd need methods to Add, Remove, etc. the
individual objects.

There are several good articles on the web about classes and collections.
http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part2/ch09.mspx
http://www.cpearson.com/excel/ClassModules.htm

Barry

 

[Tim Ferguson]

Me.Properties.Add Me.BusinessAddress, "BusinessAddress"

This line does not really make sense: the first parameter would be the
property that you are trying to create with the .Add method. If it
existed, then the .Add would have to fail; while if it did not exist,
then the call to Me.BusinessAddress would fail.

The way to add properties to a Class is to create the Property Let and
Property Get procedures. You can Dim a public member, but it's really not
safe.

You can create a custom collection class in VB but in VBA it's not easy
(i.e. I _think_ it's not possible) to make it implement the IEnumerable
interface, which is what gives you the For Each... functionality. Despite
that, there are still good reasons for creating one because it's the only
way to control what goes into the collection.

e.g.

Class MyCollection ' of MyType objects, whatever they are
Private Dim col As Collection

public function Add(AKey as String, AValue As MyType) As MyType
' return the newly added object
set Add = col.Add(AValue, AKey)

end sub

public property Get Item(AKey as String) As MyType
dim mt as MyType
On error resume next
Set mt = col.Item(AKey)
If err <> 0 Then Set mt = Nothing
Set Item = mt
End property

public property Set Item(AKey as string, AValue as MyType)
dim mt as MyType
On error resume next
Set mt = col.Item(AKey)
If err = 0 Then
Set col.Item(AKey) = AValue
else
me.Add AKey, AValue
End if

end property



Hope that helps


Tim F

 

[Ed Adamthwaite]

Hi George,
the following code will append a property let and get to the active code
window. You may have to fiddle around with line wraps inserted by your news
reader to get it to work:


Sub AddLetGet(sPropertyName As String, eType As DAO.DataTypeEnum)
Dim s As String
Dim sType As String
Dim sPrefix As String
sType = TypeString(eType, "type")
sPrefix = TypeString(eType, "pre")

s = vbCrLf & "Private m_" & sPropertyName & " As " & sType & " 'move to
declarations" & vbCrLf & vbCrLf _
& "Public Property Let " & sPropertyName & "(" & sPrefix & "NewValue As
" & sType & ")" & vbCrLf _
& " m_" & sPrefix & sPropertyName & " = " & sPrefix & "NewValue" &
vbCrLf _
& "End Property" & vbCrLf _
& "Public Property Get " & sPropertyName & "()" & vbCrLf _
& " " & sPropertyName & " = m_" & sPrefix & sPropertyName & vbCrLf _
& "End Property" & vbCrLf & vbCrLf
AppendToActiveWindow s
End Sub


Sub AppendToActiveWindow(sCode As String)
Dim mdl As Module
Set mdl = Modules(VBE.ActiveCodePane.CodeModule.Parent.Name)
mdl.InsertText sCode
Set mdl = Nothing
End Sub

Function TypeString(eDataType As DAO.DataTypeEnum, Pre_Or_Type As String) As
String
'DAO Data type enums
Dim sFieldType As String
Dim sVariablePrefix As String
Select Case eDataType
Case dbBoolean
sFieldType = "Boolean"
sVariablePrefix = "b"
Case dbByte
sFieldType = "Byte"
sVariablePrefix = "byt"
Case dbInteger
sFieldType = "Integer"
sVariablePrefix = "i"
Case dbLong
sFieldType = "Long"
sVariablePrefix = "lg"
Case dbCurrency
sFieldType = "Currency"
sVariablePrefix = "cur"
Case dbSingle
sFieldType = "Single"
sVariablePrefix = "sg"
Case dbDouble
sFieldType = "Double"
sVariablePrefix = "dbl"
Case dbDate
sFieldType = "Date"
sVariablePrefix = "d"
Case dbText
sFieldType = "String"
sVariablePrefix = "s"
Case dbMemo
sFieldType = "String" 'Memo
sVariablePrefix = "s"
Case dbDecimal
sFieldType = "Decimal"
sVariablePrefix = "dec"
End Select
Select Case UCase(Pre_Or_Type)
Case "type"
TypeString = sFieldType
Case "pre"
TypeString = sVariablePrefix
End Select
End Function

 

[onedaywhen]

You can create a custom collection class in VB but in VBA it's not easy
(i.e. I _think_ it's not possible) to make it implement the IEnumerable
interface, which is what gives you the For Each... functionality.

It _is_ possible. You have to add the 'hidden' Attribute lines manually
and outside of the Visual Basic Editor e.g. export and remove the class
module from the VBA project, open in a text editor (e.g. Notepad),
paste in the following magic lines:

Public Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
Attribute NewEnum.VB_MemberFlags = "40"
Set NewEnum = m_Class1s.[_NewEnum]
End Property

and import back into the VBA project. The Attribute lines will
invisible but will remain (unless carelessly edited in the VBE).

The other important one is making the class's Get the default member,
to be able to write the long-hand

m_Schema.Tables("Test").Columns("dec_col").DDL

as the short-hand

m_Schema.Tables.Item("Test").Columns.Item("dec_col").DDL

The magic Attribute line in ths case is:

Public Property Get Item(ByVal Index As Variant) As CTable
Attribute Item.VB_UserMemId = 0
Set Item = m_colTables.Item(Index)
End Property

Jamie.

--

 

[Tim Ferguson]

It _is_ possible. You have to add the 'hidden' Attribute lines manually

Very cool. Thanks Jamie.


Tim F