CODEWITHSUNDEEP
vbaexcel
rohrl77
rohrl77

Reputation: 3337

VBA Error asking to define variable when going through Dictionary Keys

VBA keeps on giving me an error message saying that the "Key" variable is not defined. However, this code used to work, and i'm unsure why it doesn't now...

The variable it highlights is "Key" on the For Each Key In dic line.

Why does it give me an error?

'---------------------------------------------------------------------------------------
' Method : CreateUniqueISINList
' Purpose: Takes Array and groups it based on one criteria. The quantity data field is summed.
'---------------------------------------------------------------------------------------
Private Function CreateUniqueISINList(ByRef arr As Variant) As Variant
Dim dic As Dictionary
Set dic = New Dictionary

'Create dictionary with list of unique ISINs
Dim i As Long
For i = LBound(arr, 2) To UBound(arr, 2)
    If Not dic.Exists(arr(1, i)) Then
        dic.Add arr(1, i), arr(1, i) 'Keep ISIN
    End If
Next i

'Create output Array with relevant quantity of stocks held
Dim arrWIP
ReDim arrWIP(dic.Count, 1)

Dim j As Long
For Each Key In dic
    arrWIP(j, 0) = Key 'set ISIN
    For i = LBound(arr, 2) To UBound(arr, 2)
        If arr(1, i) = Key Then
            arrWIP(j, 1) = arrWIP(j, 1) + arr(3, i) 'update quantity
        End If
    Next i
    j = j + 1 'increase counter for arrWIP
Next Key

CreateUniqueISINList = arrWIP
Set dict = Nothing

End Function

Upvotes: 1

Views: 576

Answers (1)

Vityata
Vityata

Reputation: 43585

Dim Key would be enough. If you want to make it a bit better looking, write Dim Key as Variant. It was working before, because you did not have explicitly written Option Explicit on the top of the module. Now, when you have written it, you have to explicitly declare each variable.

Then make sure that you are not having 2 variables with names dic and dict. This is where Option Explicit actually helps you write better code.

Upvotes: 2

Related Questions