Transfer Dictionary data to an Array, but getting "subscript out of range"

[Certified]

My code is running into a Run-time error 9 (Subscript out of range).

When the code is debugged, the line printed in red is highlighted.

Here is my function:

Function populateDict(thearraY As Variant, retunArray As Variant) As Variant
Dim i As Integer

Dim key As Variant

    For i = 1 To UBound(thearraY)
    
        If theDict.Exists(thearraY(i, 2)) Then
        
        theDict(thearraY(i, 2)) = theDict(thearraY(i, 2) + thearraY(i, 8))
        
        
        Else
        
        theDict.Add key:=thearraY(i, 2), Item:=thearraY(i, 8)
    
        End If
    
    Next i
    
    i = 1
    
    ReDim Returnarry(UBound(thearraY), 2)
    
    For Each key In theDict.Keys
          [COLOR=#ff0000]
              returnArray(i, 1) = key[/COLOR]

              returnArray(i, 2) = theDict(key)

              i = i + 1             
            
              
        Next key    
    
End Function

At the error, i = 1, Key = "GEMS" theDict(key) = "Percentage", theDict has a count of 566, and the ubound(thearray) figure is 571.

I can't figure out why I am getting a out of range error.

 

[Norie]

Try adding Option Explicit at the top of the module, it should highlight the typo that is the probable cause of the problem.

 

[Norie]

PS Make that typoes.

 

[V_Malkoti]

Where is returnArray declared and initialized? I see retunArray (as parameter) and Returnarry (in ReDim) but not returnArray.

 

[Certified]

I have option explicit and option base 1 at the top

 

[Certified]

ReturnArray is declared at the top of the module before the main sub.

"dim returnarray() as variant"

 

[V_Malkoti]

So you have three variables with very similar names? That sounds confusing.

And where is this array variable returnArray() initialized or ReDimmed? I don't see it in populateDict() code that you posted.

 

[Certified]

Yes, I guess it is a bit confusing.

Let me explain what I am trying to accomplish.

The function I submitted is part of a subroutine.

1. The Function (populatedict) takes in an array (thearray)

2. The dictionary (theDict)is populated with only the unquie items

3. another array (returnarray) is populated with the dictionary items

4. the returnarray returned to the main sub.

The error is at step 3.


The returnarray is declared at the beginning of the module.

 

[dreid1011]

Yes, I guess it is a bit confusing.

Let me explain what I am trying to accomplish.

The function I submitted is part of a subroutine.

1. The Function (populatedict) takes in an array (thearray)

2. The dictionary (theDict)is populated with only the unquie items

3. another array (returnarray) is populated with the dictionary items

4. the returnarray returned to the main sub.

The error is at step 3.


The returnarray is declared at the beginning of the module.

I think the point being made is returnArray, retunArray and Returnarry are all supposed to be returnarray? But they are all spelled differently. There in lies the cause of the error. Unless of course, you meant to have three different array variables named something along the lines of ReturnArray.................

Function populateDict(thearraY As Variant, retunArray As Variant) As Variant
Dim i As Integer

Dim key As Variant

    For i = 1 To UBound(thearraY)
    
        If theDict.Exists(thearraY(i, 2)) Then
        
        theDict(thearraY(i, 2)) = theDict(thearraY(i, 2) + thearraY(i, 8))
        
        
        Else
        
        theDict.Add key:=thearraY(i, 2), Item:=thearraY(i, 8)
    
        End If
    
    Next i
    
    i = 1
    
    ReDim Returnarry(UBound(thearraY), 2)
    
    For Each key In theDict.Keys
          
              returnArray(i, 1) = key

              returnArray(i, 2) = theDict(key)

              i = i + 1             
            
              
        Next key    
    
End Function

 

[Certified]

Found it.

I had an spelling error. I misspelled returnarray inside the parameter.