Ruby-like Hash Functionality in VBA

Question

All,

I am currently writing a macro in Excel and have hit a road block. I typically write code in Ruby, so I may not be approaching this problem from the right angle.

I know this post has been tagged with VBA, but I feel I need to explain my thought process in Ruby. I am attempting to create a hash table in VBA that will allow me to look up values, associated with dates, based on an identifier. In ruby I would implement this functionality with a hash, as demonstrated below.

hash = { 9385 => { '10/1/2014' => [2, 4, 6, 1, 6] } }

The values would then be accessed in this manner: hash[9385]['10/1/2014'] => [2, 4, 6, 1, 6]

The hash would have a key of 9385 which would allow me to access to the associated data of 10/1/2014 and access the array held within.

I have attempted to recreate this functionality by linking dictionary objects in VBA. However, I cannot get the dictionary objects to recreate the hierarchy that one would find with a hash in Ruby.

Is there any way I can create this type of functionality?

Thank you.

Does [this](http://stackoverflow.com/questions/2782076/is-there-a-json-parser-for-vb6-vba) help? — shahkalpesh, Oct 01 '14 at 19:46

score 0 · Answer 1 · answered Oct 01 '14 at 20:27

We have many data types in Basic. I don't know Ruby but Dictionaries hash themselves.

Arrays.

Dictionaries

Collections

Disconnected Recordsets

and also the .NET classes for lists/arrays are also available in Basic. EG, an array object (different to a datatype), sorted one, etc. Add a reference to mscorelib.

I use disconnected recordsets rather than dictionaries. Here's a randomising sort piece of code that reads lines in from stdin and randomises them in a disconnected recordset and writes to stdout.

This is vbscript but vbscript is legal vb6.

cscript randomise.vbs <filename.txt


Set Arg = WScript.Arguments
set WshShell = createObject("Wscript.Shell")
Set Inp = WScript.Stdin
Set Outp = Wscript.Stdout

    Randomize 
    Set rs = CreateObject("ADODB.Recordset")
    With rs
        .Fields.Append "RandomNumber", 4 

        .Fields.Append "Txt", 201, 5000 
        .Open
        Do Until Inp.AtEndOfStream
            .AddNew
            .Fields("RandomNumber").value = Rnd() * 10000
            .Fields("Txt").value = Inp.readline
            .UpDate
        Loop
        .Sort = "RandomNumber"
        Do While not .EOF
            Outp.writeline .Fields("Txt").Value

            .MoveNext
        Loop
    End With

Ruby-like Hash Functionality in VBA

1 Answers1