Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries



Links

Issociate
Impressum

#1: VBA collections question

Posted on 2006-02-08 19:54:52 by mark

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

Report this message

Mr Ad

Google

#2: Re: VBA collections question

Posted on 2006-02-08 22:02:28 by Anthony England

"Mark" wrote in message
news:WtednS8intPlo3feRVn-gw@w-link.net...
> 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


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...

Report this message

#3: Re: VBA collections question

Posted on 2006-02-08 23:08:32 by mark

"Anthony England" wrote:
>
> 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...

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.

Report this message

#4: Re: VBA collections question

Posted on 2006-02-09 06:12:46 by Tom van Stiphout

On Wed, 8 Feb 2006 14:08:32 -0800, "Mark"
wrote:

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.


>"Anthony England" wrote:
>>
>> 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...
>
>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.
>

Report this message

#5: Re: VBA collections question

Posted on 2006-02-09 11:15:26 by Terry Kreft

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).



--

Terry Kreft


"Mark" wrote in message
news:WtednS8intPlo3feRVn-gw@w-link.net...
> 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
>
>
>

Report this message