Convert Dictionary Early to Late Binding

[tiredofit]

The code below work in early binding:

    Dim DIC As Scripting.Dictionary
    'Dim DIC As Object
    
    Set DIC = New Scripting.Dictionary
    'Set DIC = CreateObject("Scripting.Dictionary")
    
    Dim MyArray() As Variant
     
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Dim n As Long
    
    For n = 1 To UBound(MyArray(), 1)
        
        DIC.Item(MyArray(n, 1)) = 0
        
    Next n
    
    Dim KeysArray() As Variant
    
    KeysArray() = DIC.Keys
    
    Dim NumKeys As Long
    
    NumKeys = DIC.Count
    
    Dim ElementsArray() As Variant
    ReDim ElementsArray(1 To DIC.Count, 1 To 1) As Variant
    
    Dim DataRng As Range
    Set DataRng = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Module1.LRow(wks:=Sheet1), 1))
    
    Dim Counter As Long
    
    For Counter = 1 To DIC.Count - 1
    
        ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys(Counter))
       
    Next Counter

but when I convert it to late binding, it fails here:

ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys(Counter)

What do I have to do to fix it?

Thanks

 

[Fluff]

Try

ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys()(Counter))

 

[tiredofit]

Try

ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys()(Counter))

Thanks, I recall something like that sometime ago.

Why must add the brackets for late binding?

 

[Fluff]

I don't know, I just know that it works.

 

[tiredofit]

I don't know, I just know that it works.

Good enough answer for me,

 

[RoryA]

Keys doesn't actually take an index, so really it's stranger that the early bound one works at all. I suspect that with early binding the compiler is smart enough to realise that it returns an array and internally converts it to the late bound syntax in the P-code. With late binding, it doesn't know at compile time that an array is returned so just executes the literal instruction which, correctly, fails.

 

[tiredofit]

Keys doesn't actually take an index, so really it's stranger that the early bound one works at all. I suspect that with early binding the compiler is smart enough to realise that it returns an array and internally converts it to the late bound syntax in the P-code. With late binding, it doesn't know at compile time that an array is returned so just executes the literal instruction which, correctly, fails.

Thanks