arrays - VBA如何直接访问字典Keys元素

Question

上下文

在 VBA 中，您可以创建并填充字典，例如:

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

与 Keys method您可以将键作为数组获取:

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"

问题

但是当我直接访问其中一个 key 时，它会给出以下神秘的错误消息:

    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

尝试解决失败

虽然上面是主要行为，但我不明白下面将 oDict.Keys 作为数组处理的尝试的完整列表:

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

Answer 1

出了什么问题

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

Keys 这个词是一个方法。虽然 VBA 允许您在不提供参数时删除括号，但它仍然是一种方法。如果您在其后面指定括号，则内容将传递给该方法。 Keys 方法不接受整数参数。

如何修复

通过显式提供 Keys 方法的括号(如 Keys())，我们可以直接应用/跟随括号来访问数组元素。

根据问题中的示例:以下两种选择是等效的:

    Debug.Print aKeys(2)         ' prints "key 3"
    Debug.Print oDict.Keys()(2)  ' prints "key 3"