Runtime error using dictionary when using late binding but not early binding
What I am doing is putting the dictionary into an array within the subroutine
This definition
Dim Arr() As Variant
ReDim Arr(0 To Dict.Count - 1)
For c= 0 To Dict.Count - 1
Arr(c) = Dict.Keys(c) ' <~~~~~~ Error here
Next c
The compiler says
Runtime Error 451: property let procedure is undefined and property get procedure does not return an object.
It works fine with
Public Sub SubRoutine(Dict As Scripting.Dictionary)
but not with
Public Sub SubRoutine(Dict As Object) โ
Refer to Declare Dictionary Without Running Microsoft Scripting Runtime
source to share
<Solution>
You do it
Dim Arr() As Variant
ReDim Arr(0 To Dict.Count - 1)
For c = 0 To Dict.Count - 1
Arr(c) = Dict.Keys(c)
Next c
But loops like this are completely unnecessary. What it does Arr = Dict.Keys
. So instead of the above, just
Dim Arr As Variant Arr = Dict.Keys
An added bonus is that the bug goes away.
</Solution>
But why is there an error in the later linked version of the code, but not the earlier one?
<Educated guess slash wild speculation>
With early binding, the compiler knows the method.Keys
takes no parameters - it just returns an array. Therefore, it interprets Dict.Keys(c)
as {returned array}(c)
and returns the c
th element of this returned array.
On late binding, I think the Object container doesn't know that the method .Keys
doesn't take parameters (aka arguments), so it sends it c
as a parameter. But such a getter (or setter) does not exist, hence the error. To fix this, you can explicitly force it to not send parameters to the method Keys
by saying Dict.Keys()(c)
which behavior returns {returned array}(c)
.
</Educated guess slash wild speculation>
This is the first time I have encountered such a case where late-bound code behaves differently than early-bound code.
source to share