VBA collections question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 08 Feb 2006 18:54 GMT

Hello.

If I create a collection in VBA and populate it with entries
indexed by text keys, is there a way to go back and enumerate
the actual key values used?

For example:

'Create collection of really weak passwords, indexed by username
public cPW as new collection
cPW.add "dogs_name", "mark"
cPW.add "daughters_birthday", "john"
cPW.add "mypassword", "sally"

sub enum_users_from_collection(coll as collection)
dim sName as string
dim sMsg as string
'Collections have no "keys" property, of course
for each sName in coll.keys
sMsg = sMsg & vbcrlf & sName
next
msgbox sMsg,,"User Name List"
end sub

Thanks
-Mark

Reply to this Message

Anthony England - 08 Feb 2006 21:02 GMT

> Hello.
>
[quoted text clipped - 22 lines]
> Thanks
> -Mark

No there is no way with a plain old vba collection. You could create your
own class to do this sort of thing or you could use something like the
Scripting.Dictionary object which is built for this sort of thing.
But why do you not simply want to use an array? If all you need to do is
build up a list of username/password pairs and then run through the list,
then an array would be fine. However, perhaps there are some extra features
you require...

Reply to this Message

Mark - 08 Feb 2006 22:08 GMT

> No there is no way with a plain old vba collection. You could create your
> own class to do this sort of thing or you could use something like the
[quoted text clipped - 3 lines]
> then an array would be fine. However, perhaps there are some extra
> features you require...

It's really a hypothetical, although Perl will give you the names of your
hash
keys if you know how to ask, so it isn't unprecedented.

Reply to this Message

Tom van Stiphout - 09 Feb 2006 05:12 GMT

VBA doesn't expose this property, but "if you know how to ask" there
probably is a way. It would involve a lengthy low-level debugging
session, and several Windows API calls, I'm sure.
Scripting.Dictionary is a good alternative, though I have heard some
people argue against it because they consider scrrun.dll a security
risk. I'm not up to speed on all the ins and outs of that.

-Tom.

>> No there is no way with a plain old vba collection. You could create your
>> own class to do this sort of thing or you could use something like the
[quoted text clipped - 7 lines]
>hash
>keys if you know how to ask, so it isn't unprecedented.

Reply to this Message

Terry Kreft - 09 Feb 2006 10:15 GMT

No, the easy way to handle this would be to create a collection of class
objects each object of which exposes it's own key.

e,g,
class module cPeople

' **********************
Option Explicit

Private Type MeProps
Value As String
Key As String
End Type

Private MP As MeProps

Property Let Value(RHS As String)
MP.Value = RHS
End Property

Property Get Value() As String
Value = MP.Value
End Property

Property Let Key(RHS As String)
MP.Key = RHS
End Property

Property Get Key() As String
Key = MP.Key
End Property
' **********************

Then fill the collection
' **********************
public cPW as collection

Dim cP as cPeople

Set cPW as New Colloection

set cp = new cpeople
with cp
.Value = "dogs_name"
.Key = "mark"
End With
cpw.add cp, cp.Key

set cp = new cpeople
with cp
.Value = "daughters_birthday"
.Key = "john"
End With
cpw.add cp, cp.Key

set cp = new cpeople
with cp
.Value = "mypassword"
.Key = "sally"
End With
cpw.add cp, cp.Key
set cp = nothing
' **********************

Then enumerate
' **********************
Dim o as Object
dim cP as cPeople

For each o in cPW
If TypeOf o is cPeople Then
Set cP = o
Debug.Print cP.Key
End if
Next
Set cP = nothing
Set o = nothing
' **********************

The really correct way would be to create a collection class in which you
could implement the usual Add, Count, Remove, Item and Enumeration
procedures.

The problem with this method is setting the procedure attributes for the
item and enumeration procs so that they work properly, but it can be done
(with a bit of hacking).

Signature

Terry Kreft

> Hello.
>
[quoted text clipped - 22 lines]
> Thanks
> -Mark

Reply to this Message