Columns function - object-defined error

[30percent]

Hi,

I have the following procedure:

Sub store_Dict()

Dim key As Variant
Dim myDict As New Dictionary
Set myDict = CreateDictFromColumns("Sheet1", 1, 4)

For Each key In myDict.Keys
    Debug.Print "Key = ", key, " Change = ", myDict(key)
Next

End Sub

Where UDF CreateDictFromColumns is defined as follow:

Function CreateDictFromColumns(sheet As String, keyCol As String, valCol As String) As Dictionary    
    Set CreateDictFromColumns = New Dictionary
    Dim rng As Range: Set rng = Worksheets(sheet).Range(Columns(keyCol), Columns(valCol))
    Dim i As Long
    Dim lastCol As Long 

    lastCol = rng.Columns.Count
    For i = 2 To rng.Rows.Count
        If (rng(i, 1).Value = "") Then Exit Function
        CreateDictFromColumns.Add rng(i, 1).Value, rng(i, lastCol).Value
    Next
End Function

when I run store_Dict() sub I got an error message "Application-defined or object-defined error" that point to Set rng = Worksheets(sheet).Range(Columns(keyCol), Columns(valCol)). I surmised it is the function Columns() that is creating the problem.

Wonder if someone could point how to me how I could do with that line of error to make the UDF work?

I also tried but it doesn't work:

 Dim rng As Range: Set rng = Worksheets(sheet).Range(Cells(1, keyCol).EntireColumn, Cells(1, valCol).EntireColumn)

 

[Nine Zero]

definitely just shooting ideas out here but ummm could possibly be in your UDF the last 2 parameters might need to be Integers, not strings, or putting quotes around the numbers when calling the UDF?

 

[Fluff]

Try

Function CreateDictFromColumns(sheet As String, keyCol As [COLOR=#ff0000]Long[/COLOR], valCol As [COLOR=#ff0000]Long[/COLOR]) As Dictionary
    Set CreateDictFromColumns = New Dictionary
    Dim rng As Range
    Dim i As Long
    Dim lastCol As Long
    [COLOR=#ff0000]With Worksheets(sheet)
       Set rng = .Range(.Columns(keyCol), .Columns(valCol))
    End With[/COLOR]
    lastCol = rng.Columns.Count
    For i = 2 To rng.Rows.Count
        If (rng(i, 1).Value = "") Then Exit Function
        CreateDictFromColumns.Add rng(i, 1).Value, rng(i, lastCol).Value
    Next
End Function

 

[strooman]

Shouldn't that be:

 If (rng[COLOR="#FF0000"].Cells[/COLOR](i, 1).Value = "") Then Exit Function
        CreateDictFromColumns.Add rng[COLOR="#FF0000"].Cells[/COLOR](i, 1).Value, rng[COLOR="#FF0000"].Cells[/COLOR](i, lastCol).Value