Loop Through Properties of a Custom Class

[mattdrinks]

Hi All,

I have a Class module in Excel:
(Short Version)

Dim strPartNumber As String
Dim strSupplierCode As String
Dim dblNPP As Double

'***PART NUMBER***
Property Get PartNumber() As String
    PartNumber = strPartNumber
End Property

Property Let PartNumber(Part As String)
    strPartNumber = Part
End Property

'***SUPPLIER CODE***
Property Get SupplierCode() As String
    SupplierCode = strSupplierCode
End Property

Property Let SupplierCode(Supplier As String)
    strSupplierCode = Supplier
End Property

'***NPP***
Property Get NPP() As Double
    NPP = dblNPP
End Property

Property Let NPP(Price As Double)
    dblNPP = Price
End Property

I can not seem to find a way to loop through all the properties of the class, I have tried:

Dim proItem As Property
Dim STDTemp As STDProduct 'My Class

Set STDTemp = New STDProduct
For Each proItem In STDTemp 'Error here
    MsgBox proItem.Name
Next

Error: Object does not support this property or method.
and

Dim proItem As Property
Dim STDTemp As STDProduct 'My Class

Set STDTemp = New STDProduct
For Each proItem In STDTemp.Properties 'Error here
    MsgBox proItem.Name
Next

Error: Method or data member not found

Does anyone know if there is a way I can loop through all the properties of a class, or another way I can achieve the same thing?
Thanks,
Matt

 

[Chance1234]

GOD DAM IT i jsut got this to work and my machine crashed

but this is what i did,

I set up in your class a funciton called GetPropertys

And i declared strArr(3) as string

Then added strArr(1) = NPP
strArr(2) = Supplier blah blah

Getpropertys = strArr


then in your sub all you have to do is loop through the array getpropertys

Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[mattdrinks]

Hi Chance,

Great minds think alike, this is what i have been working on:

Property Get PropertyNames() As String()

Dim AllProperties(21) As String

AllProperties(1) = "PartNumber"
AllProperties(2) = "SupplierCode"
AllProperties(3) = "SupplierPart"
AllProperties(4) = "NPP"
AllProperties(5) = "NPD"
AllProperties(6) = "RRP"
AllProperties(7) = "BoxQty"
AllProperties(8) = "PUOM"
AllProperties(9) = "Description"
AllProperties(10) = "ProductGroup"
AllProperties(11) = "Range"
AllProperties(12) = "MkOnRetail"
AllProperties(13) = "MkOnTrade"
AllProperties(14) = "MkOnWholesale"
AllProperties(15) = "SurchargeIn"
AllProperties(16) = "SurchargeOut"
AllProperties(17) = "StockFree"
AllProperties(18) = "MinStock"
AllProperties(19) = "MaxStock"
AllProperties(20) = "Condition"
AllProperties(21) = "OtherInfo"

PropertyNames = AllProperties

End Property

It all appears to work, its just that if at a later date I need to add other properties to the class then they I'll will also have to add a line of code to this method/property. I was just hoping there might be a way to loop through all properties without having to Hardcode their names into a new method. I am sure you can do it in .NET but it does not seem possible in VBA.
I really appricate the help,
Thanks
Matt