VBA - Dictionary Calling Key by Value?

Question

It seems most of the answers for this involve C# so I felt relatively safe asking:

I need to call a dictionary key by it's value. I have a dictionary object with a list of four character keys. For Example:

dSCAC.Add "AAAA", 1
dSCAC.Add "BBBB", 2
dSCAC.Add "CCCC", 3
dSCAC.Add "DDDD", 4

etc.

I've been able to call the value associated with a key when it's found in a string (in this case, an email subject line) and then add or remove 1 from the value.

Ex: BBBB is detected, value is 2. Modify the value and call the appropriate key. In this case, in the end we'd want varOtherSCAC = AAAA.

If dSCAC(varSCAC) Mod 2 Then
        Debug.Print "Odd " & "PAPS"
        varOtherSCAC = (dSCAC(varSCAC) + 1)            
        Debug.Print "Opposite SCAC is " & varOtherSCAC

        Else
        Debug.Print "Even " & " PARS"
        varOtherSCAC = (dSCAC(varSCAC) - 1)
        Debug.Print "Opposite SCAC is " & varOtherSCAC
End if

I haven't been able to work out the syntax for calling the new key based on this value. Is it even possible in VBA? Is there a way around this?

What is the use case for this? A dictionary typically only ensures that the *key* is unique - do you need both the key and the value to be unique? If not, is there a rule as to which key you would return if there are duplicates? — Comintern, Feb 13 '19 at 01:56
I currently have a few little projects on the go and for convenient updating have created the dictionary as a central place for all the codes I need to check against/verify are correct/present in a string. In this case, when I discover a legal code I want to be able to quickly call its associated one (which can then be added to a new string and printed). If BBBB is in the string, I want to be able to find its counterpart; AAAA in this case. Each key/value would have to be unique, yes. AAAA would always only be 1, BBBB only ever 2, etc. — Kinote, Feb 13 '19 at 02:24
Unique key/value pairs aren't really the same thing as unique *values*. To rephrase the question, is there only one 1, only one 2, etc. — Comintern, Feb 13 '19 at 02:31

score 1 · Accepted Answer · answered Feb 13 '19 at 03:13

1

I'd take similar approach to @TateGarringer's, but wrap the two Dictionary objects in a class to give them a common interface and make the thing easier to use:

'In a class module named MirroredDictionary.cls (add reference to Scripting Runtime)
Option Explicit

Private backing As Scripting.Dictionary
Private mirror As Scripting.Dictionary

Private Sub Class_Initialize()
    Set backing = New Scripting.Dictionary
    Set mirror = New Scripting.Dictionary
End Sub

Public Sub Add(Key As Variant, Value As Variant)
    backing.Add Key, Value
    mirror.Add Value, Key
End Sub

Public Function KeyExists(Key As Variant) As Boolean
    KeyExists = backing.Exists(Key)
End Function

Public Function ValueExists(Value As Variant) As Boolean
    ValueExists = mirror.Exists(Value)
End Function

Public Function ValueFromKey(Key As Variant) As Variant
    ValueFromKey = backing.Item(Key)
End Function

Public Function KeyFromValue(Value As Variant) As Variant
    KeyFromValue = mirror.Item(Value)
End Function

Depending on how you intend to use this, you may or may not want to wrap additional functions. Usage would be similar to a normal Dictionary (aside from some differences in the property names, although you can change those to taste):

Public Sub Example()
    Set sample = New MirroredDictionary

    sample.Add "AAAA", 1
    sample.Add "BBBB", 2
    sample.Add "CCCC", 3
    sample.Add "DDDD", 4

    Debug.Print sample.ValueFromKey("AAAA")     '1
    Debug.Print sample.KeyFromValue(1)          'AAAA
    Debug.Print sample.ValueFromKey("BBBB")     '2
    Debug.Print sample.KeyFromValue(2)          'BBBB
    Debug.Print sample.ValueFromKey("CCCC")     '3
    Debug.Print sample.KeyFromValue(3)          'CCCC
    Debug.Print sample.ValueFromKey("DDDD")     '4
    Debug.Print sample.KeyFromValue(4)          'DDDD
End Sub

answered Feb 13 '19 at 03:13

Comintern

21,855
5
33
80

I like the idea of a common interface, but I've never dealt with class modules before so it might take a little while for me to wrap my head around. Thanks for the help! – Kinote Feb 13 '19 at 03:30
@Kinote This would be the perfect opportunity to play with them. ;-) Stepping through the sample code line by line in the debugger will give you a pretty good idea of how it works. You're basically implementing your own custom dictionary class. – Comintern Feb 13 '19 at 03:31
Compile Error: User-defined type not defined when I hit the class module - when it hits the first line `Private backing As Scripting.Dictionary` - Might all be a little above my head, syntax wise. The concept of the double dictionary does indeed solve my issue, the syntax for making it work... that'll take a while. I kind of stumbled into getting my current one working when my other modules interact with it. – Kinote Feb 13 '19 at 03:42
@Kinote That's the comment at the top - you either need to add a reference to the Microsoft Scripting Runtime (better) or [change it to late binding](https://stackoverflow.com/q/50583705/4088852). – Comintern Feb 13 '19 at 03:51
Ah. Kind of figured that's what I was missing. How would I go about adding a reference to the MSR? Apologies for all the rookie questions! – Kinote Feb 13 '19 at 04:16
Tools -> References... find Microsoft Scripting Runtime (scroll *way* down), check the box, then hit [OK]. That's all there is to it. – Comintern Feb 13 '19 at 04:21
Found it! Code seems to run just fine now and looks very interesting, I'll tinker with it a bit when I get some time. Thanks for all the help! – Kinote Feb 13 '19 at 23:51
Any idea why it only works in the third case? `'Test 1 - Doesn't work Dim strTestSCACValue As String: strTestSCACValue = "3" Debug.Print sample.KeyFromValue(strTestSCACValue) 'Test 2 - Doesn't work Dim intTestSCACValue: intTestSCACValue = "3" Debug.Print sample.KeyFromValue(intTestSCACValue) 'Test 3 - Works intTestSCACValue = 3 Debug.Print sample.KeyFromValue(intTestSCACValue)` – Kinote Feb 14 '19 at 01:14
@Kinote That doesn't work with *any* `Dictionary`. The first 2 are `String`s, the last one is an `Integer`. The key is stored as a hash, so the type has to match. – Comintern Feb 14 '19 at 01:32
Well don't I feel dumb. This whole time I've just been using the wrong type of variable to interact with it! Thanks again! – Kinote Feb 14 '19 at 01:54

Tate Garringer · Answer 2 · 2019-02-13T01:37:47.660

You could always create an additional dictionary to store your Key-Value pairs with your Value as your key and your key as the value

Sub test()
Dim dSCAC As Object
Dim dSCACArr As Object
Dim varOtherSCAC As String
Dim key
Dim varSCAC


Set dSCAC = CreateObject("Scripting.Dictionary")
Set dSCACArr = CreateObject("Scripting.Dictionary")

dSCAC.Add "AAAA", 1
dSCAC.Add "BBBB", 2
dSCAC.Add "CCCC", 3
dSCAC.Add "DDDD", 4

For Each key In dSCAC.Keys
    dSCACArr.Add dSCAC(key), key
Next

For Each varSCAC In dSCAC.Keys
    If dSCAC(varSCAC) Mod 2 Then
            Debug.Print "Odd " & "PAPS"
            varOtherSCAC = dSCACArr(dSCAC(varSCAC) + 1)
            Debug.Print "Opposite SCAC is " & varOtherSCAC

            Else
            Debug.Print "Even " & " PARS"
            varOtherSCAC = dSCACArr(dSCAC(varSCAC) - 1)
            Debug.Print "Opposite SCAC is " & varOtherSCAC
    End If
Next

End Sub

This yielded results

Odd PAPS
Opposite SCAC is BBBB
Even  PARS
Opposite SCAC is AAAA
Odd PAPS
Opposite SCAC is DDDD
Even  PARS
Opposite SCAC is CCCC

EDIT:

For Each varSCAC In dSCAC.Keys...Next is only included for proof of concept.

`ReDim dSCACArr(Application.Min(dSCAC.Items) To Application.Max(dSCAC.Items))` seems to cause Run-Time Error 438: Object doesn't support this property or method — Kinote, Feb 13 '19 at 00:16
You're absolutely right, sorry. I don't know why my brain went straight to Excel functions. You could bypass the array method by just using another dictionary. I've modified my code to reflect that. — Tate Garringer, Feb 13 '19 at 01:37
No worries, and thanks for the help! Say my search returned a value of 3, how could I make that call CCCC? — Kinote, Feb 13 '19 at 02:32

VBA - Dictionary Calling Key by Value?

2 Answers2