VBA Hash Of Hashes / HashMap / Collections 2

[1DMF]

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

 

[PHV]

You may use either a Collection or a Scripting.Dictionary object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

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]

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
FAQ181-2886

 

[1DMF]

Thank PH, but I'm still confused, the example under F1 shows..

        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

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

then to add items....

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

So how do you do that with a VBA collections?

Is this correct?

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

It seems to work if I then try

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]

Your code is correct.

 

[1DMF]

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

 

[strongm]

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]

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]

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]

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]

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

[blue]
Dim dotnetHashTable as Object

Set dotnetHashTable = CreateObject("System.Collections.HashTable")[/blue]

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]

And by 'intellitext' I actually mean 'intellisense', but I'd been struggling with Vibrant Media's IntelliTxt just before posting ...

 

[1DMF]

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]

>.NET syntax

I'm not exposing you to any .NET syntax ...

 

[1DMF]

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]

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

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.

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]

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

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

"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