3

context

In VBA you can create and fill a dictionary like:

 Dim oDict As Object Set oDict = CreateObject("Scripting.Dictionary") oDict("key 1") = "value 1" oDict("key 2") = "value 2" oDict("key 3") = "value 3"

With the Keys method you can get the keys as array:

Returns an array containing all existing keys in a Dictionary object.

 Dim aKeys() As Variant aKeys = oDict.Keys Debug.Print VarType(aKeys) ' prints "8204" Debug.Print VarType(oDict.Keys) ' prints "8204"

the problem

But when I access one of the keys directly it gives this cryptic error message:

 Debug.Print aKeys(2) ' prints "key 3" Debug.Print oDict.Keys(2) ' Run-time error '451': ' Property let procedure not defined ' and property get procedure did not ' return an object

failed attempts to solve

While the above is the main behavior I did not understand below the full list of attempts to handle oDict.Keys as array:

Option Explicit Public Function ArrayGet(ByRef aArray() As Variant, i As Integer) As Variant ArrayGet = aArray(i) End Function Public Function ArrayWrap(ByRef aArray() As Variant) As Variant() ArrayWrap = aArray End Function Public Sub TEST() Dim oDict As Object Set oDict = CreateObject("Scripting.Dictionary") oDict("key 1") = "value 1" oDict("key 2") = "value 2" oDict("key 3") = "value 3" Dim aKeys() As Variant aKeys = oDict.Keys Debug.Print VarType(aKeys) ' prints "8204" Debug.Print VarType(oDict.Keys) ' prints "8204" Debug.Print aKeys(2) ' prints "key 3" 'Debug.Print oDict.Keys(2) ' Run-time error '451': Property let procedure not defined and property get procedure did not return an object 'Debug.Print oDict.Keys.Get(2) ' Run-time error '424': Object required 'Debug.Print oDict.Keys.Item(2) ' Run-time error '424': Object required Debug.Print ArrayGet(aKeys, 2) ' prints "key 3" 'Debug.Print ArrayGet(oDict.Keys, 2) ' Compile error : Type mismatch: array or user-defined type expected 'Debug.Print Array(aKeys)(2) ' Run-time error '9' : Subscript out of range 'Debug.Print Array(oDict.Keys)(2) ' Run-time error '9' : Subscript out of range Debug.Print ArrayWrap(aKeys)(2) ' prints "key 3" 'Debug.Print ArrayWrap(oDict.Keys)(2) ' Compile error : Type mismatch: array or user-defined type expected Dim key As Variant For Each key In aKeys Debug.Print key ' prints "key 1", "key 2" and "key 3" Next key For Each key In oDict.Keys Debug.Print key ' prints "key 1", "key 2" and "key 3" Next key End Sub
Improve this question
2
  • NOTE FROM OP: The process of writing the question actually allowed me to find the answer myself. Hopefully the keywords contained in this question allow somebody else to find the solution more quickly. Commented Mar 25, 2019 at 7:47
  • NOTE FROM OP: Now that the cause is known it is easy to see that it is the same root problem as covered here Commented Mar 25, 2019 at 7:47

2 Answers 2

Reset to default
8

what was wrong

 Debug.Print oDict.Keys(2) ' Run-time error '451': ' Property let procedure not defined ' and property get procedure did not ' return an object

The Keys word is a method. While VBA allows you to drop the parentheses when not providing arguments it is still a method. If you specify parentheses behind it the contents will be passed to the method. The Keys method does not accept an integer argument.

how to fix

By explicitly providing the parentheses of the Keys method (like Keys()) we can directly apply / follow with the parentheses for accessing the array element.

In line with the example in the question: the following two alternatives are equivalent:

 Debug.Print aKeys(2) ' prints "key 3" Debug.Print oDict.Keys()(2) ' prints "key 3"
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you for sharing your finding. This has been plaguing me for so many years...I usually check my dictionaries and dictionary of dictionaries in Immediate window and this is the best thing that happened to me today...OMG...Thank you!!!
3

If you don't add the reference to Microsoft Scripting Runtime, you need to use late binding to use a dictionary object. With late binding, you lose some functionality.

If you try to access the dictionary's keys by index, you will throw a runtime error 451.

Run-time error '451':
Property let procedure not defined and property get procedure did not return an object.

enter image description here

If you add the Microsoft Scripting Runtime library reference to the project through the VBE's Tools, References you can now access the dictionary's keys directly through its ordinal index number.

enter image description here

Sub test3() Dim i As Long, dict As New Scripting.dictionary dict.Add Key:="key1", Item:="item1" dict.Add Key:="key2", Item:="item2" dict.Add Key:="key3", Item:="item3" For i = LBound(dict.Keys) To UBound(dict.Keys) Debug.Print dict.Keys(i) Next i End Sub 'results key1 key2 key3
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.