Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

1DMF (Programmer)
6 Apr 10 10:12
Hi,

Is there a VBA equivalent to Java's Collections framework for sets & hashmap's?

Or

Is there a VBA equivalent to Perl's hash of hashes functionality?

I found this thread http://support.microsoft.com/kb/307933 which talks about hashtable, but it goes on to override methods and seems to require additional class's and methods to be written for storing the key/value pair, it's all a bit confusing, so your help is appreciated.

1DMF



 

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

Helpful Member!  PHV (MIS)
6 Apr 10 10:33
You may use either a Collection or a Scripting.Dictionary object.

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

1DMF (Programmer)
6 Apr 10 10:50
Can you point me in the direction of an example of its use so I can see how to implement it.

Thanks.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

PHV (MIS)
6 Apr 10 11:20
For a VBA Collection, type collection in the immediate window (Ctrl-G) and  then press the F1 key.
For a Scripting.Dictionary object you have to reference the Microsoft Scripting Runtime library.
http://msdn.microsoft.com/en-us/library/x4k5wbx4(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

1DMF (Programmer)
6 Apr 10 11:50
Thank PH, but I'm still confused, the example under F1 shows..

CODE

        Dim Inst As New Class1    ' Create a new instance of Class1.
        Num = Num + 1    ' Increment Num, then get a name.
        Msg = "Please enter a name for this object." & Chr(13) _
         & "Press Cancel to see names in collection."
        TheName = InputBox(Msg, "Name the Collection Items")
        Inst.InstanceName = TheName    ' Put name in object instance.
        ' If user entered name, add it to the collection.
        If Inst.InstanceName <> "" Then
            ' Add the named object to the collection.
            MyClasses.Add item := Inst, key := CStr(Num)
        End If

why is it creating instances of Class1, what's Class1?

What do I need to create this object for? can I not just use a collection to store simple String key/value pairs?

The declaration of such types in Java would be

CODE

Map<String, String> myCollection = new HashMap<String, String>();

then to add items....

CODE

myCollection.add("myKey","myValue");

So how do you do that with a VBA collections?

Is this correct?

CODE

Dim MyCollection As New Collection
MyCollection.Add item := "myValue", key := "myKey"

It seems to work if I then try

CODE

MsgBox (MyCollection.Item("myKey"))

But clarification on correct use is appreciated before I go re-writing a major report.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

PHV (MIS)
6 Apr 10 11:56
Your code is correct.
1DMF (Programmer)
6 Apr 10 12:02
Great, Thanks PHV.

Not sure how more efficient it will make the report!

I think I'll actually need a collection of collections, to achieve a hash of hashes effect.

This is cetrainly going to be fun!

 

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

Helpful Member!  strongm (MIS)
6 Apr 10 14:33
You can also, of course, use .NET's HashTable, which is what was being illustrated in the .NET example you linked above. It is pretty much an implementation of Java's HashTable (and thus HashMap) in terms of functionality.
1DMF (Programmer)
7 Apr 10 4:53
How can I use .NET in MS Access?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

MajP (TechnicalUser)
7 Apr 10 9:55
Out of curiosity what is it that you are trying to do?  Not sure how this relates to "efficient report." If you are managing multiple report instances there may be some other strategies.
1DMF (Programmer)
7 Apr 10 11:45
I have a report which is against a table where I have to generate the data for.

It is done via an unbeleivably inefficient loop, where multiple SQL count & sum statements are executed hundreds of times.

I have found for my web apps it is much more efficient and unbelievably faster if I use one SQL statement to get all records for processing and use a hash of hashes to generate the count / sum data.

In perl it's a sinch to do (well once you get your head around the hash of hashes syntax!).

As I am studying OO programing with Java, I came across hashmap, which is similar to perl & hashes.

So I wondered if there was a VBA equivalent I could use to make my report more efficient and faster.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

strongm (MIS)
7 Apr 10 12:16
>How can I use .NET in MS Access?

Surprisingly easily in the case of HashTables, becauase Microsoft kindly provides a COM wrapper for various bits of the .NET System namespace, e.g. (assuming .NET installed properly):

CODE


Dim dotnetHashTable as Object

Set dotnetHashTable = CreateObject("System.Collections.HashTable")

You'll need to look up the methods and properties on MSDN, since we cannot use intellitext in the IDE against a latebound object, and we sadly cannot early bind to the System namespace objects (at least, I've not found a way of doing so)

 
strongm (MIS)
7 Apr 10 18:52
And by 'intellitext' I actually mean 'intellisense', but I'd been struggling with Vibrant Media's IntelliTxt just before posting ...  
1DMF (Programmer)
8 Apr 10 6:12
i take it you mean the tooltip help that appears when you place a dot after a variable name offering you the various options.

The less we talk about the annoying advertisment pop-ups here on TT the better...grrr they make me angry!!

Anyways, it certainly seems interesting, but it means another steep learning curve by the looks of things, I'm concerned pushing too much new stuff in, I don't want to push out the Java stuff i've just been learning, and i don't want to get confused while in the exam, .NET syntax isn't going to score me points in a Java exam!

I might re-write the report web based and use perl , it's soooooo much easier , quicker and simpler.

I've really struggled with OO , I can't get my head around the point of putting an object, in an object, in another object and then another object...etc..

When you can achieve alot of this sort of stuff in one or two lines of perl code. OO seems totally inefficient, long winded and over complicated to achieve such simple tasks.

But i'll try to persevere!

 

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

strongm (MIS)
8 Apr 10 8:08
>.NET syntax

I'm not exposing you to any .NET syntax ...
1DMF (Programmer)
8 Apr 10 9:44
I thought you said the link I posted was .NET ?

Anyway as I said, I'm leaning more and more toward rewriting it in perl.

If I want to add one to a counter which is stored in an OO environment you need to use..

myHashMap.add(myKey,myHashMap.get("myKey") + 1);

or similar syntax.

in perl it's...

$myHash{myKey}++;

the only issue is the final report is in MS Access and it's a pain rewriting the output in HTML for web purposes.

hmm. a lot to think about.



 

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

strongm (MIS)
8 Apr 10 13:14
>I thought you said the link I posted was .NET ?

The link you posted was indeed to a .NET example. But I think you are missing my point; I'm not suggesting you use that example. I'm saying that the HashTable mentioned in that example (which is a .NET Class) can be used in VB6 (because Microsoft kindly wrapped it in a COM interface, allowing us to treat it a a COM object).

>If I want to add one to a counter which is stored in an OO environment you need to use..

Firstly, part of the 'limitation' to which you refer is simply because of VB's syntax, not because of OO. Secondly, it looks vaguely like you've plumped for using a Collection to pretend to be a hashmap - and the limitation you've found there is of the Collection object itself, rather than (again) OO. If you used a Dictionary object (or the .NET HashTable) then the VB6 equivalent of your perl code would be:

myHash(myKey) = myHash(myKey) +1

 
1DMF (Programmer)
8 Apr 10 18:53
lol I knew this was going to get complicated!

I guess the problem is I understand what i want but don't know VBA well enough to find the right object.

Quote:

Secondly, it looks vaguely like you've plumped for using a Collection to pretend to be a hashmap
I guess it's my current Java course which is causing the confusion, a HashMap is part of the Collections framework as are Sets and Lists.
In perl they are simply called hashes (key/value pairs), i'm trying to find the right object in VBA to acheive the same process, which enables multi-dimension value pairs.

I guess I might not be explaining myself very well, for which I apologise.




 

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

strongm (MIS)
9 Apr 10 9:17
>I guess I might not be explaining myself very well

You are explaining fine. We know exactly what a hashmap is, and the Dictionary class is the closest classic COM variant available to VB,  and the .NET Hashtable is a HashTable (assuming you want to use stuff already installed on your PC)
1DMF (Programmer)
9 Apr 10 10:45
Ok, i'll go check out the Dictionary class.

In Java what I want is a 'Set of Maps' in Perl it's a 'Hash of Hashes', and now I know in VB it's called a Dictionary.

I was never going to guess that one was I lol

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close