CODEWITHSUNDEEP
excelvba
Noobzoned
Noobzoned

Reputation: 1

Range object not passing into Function Excel VBA

This seems like a simple thing to solve but I don't quite understand the issue. I have a function that take a Range parameter but when I use a Loop, it doesn't seem to recognise it (I get a 424 - object error). Code provided below.

Sub PrintKeys(Data As Variant, C1 As Range)
Dim key As Variant
Dim Rg As Range
Set Rg = C1
Dim i As Integer
i = 1
For Each key In Data.Keys
    'Rg.Offset(0, i).Value = key
    'Rg.Offset(0, i).Interior.ColorIndex = 15
    Rg = Rg.Offset(0, i)
    Rg.Value = key
    Auto_Colour (Rg)
    i = i + 1
Next key
End Sub

Sub Auto_Colour(Rg As Range)
    Rg.Interior.ColorIndex = 15
End Sub

If I change the above to then it works:

Sub PrintKeys(Data As Variant, C1 As Range)
Dim key As Variant
Dim Rg As Range
Set Rg = C1
Dim i As Integer
i = 1
For Each key In Data.Keys
    Rg.Offset(0, i).Value = key
    Rg.Offset(0, i).Interior.ColorIndex = 15
    i = i + 1
Next key
End Sub

Based on the above, Rg is a Range object but the former example isn't able to pass it into the Auto_Colour function despite the function accepting a Range.

What am I missing?

Thanks in advance.

Upvotes: 0

Views: 160

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

Your issue is the line Auto_Colour (Rg)

The brackets tell VBA to pass the parameter ByVal overriding the default (implicit) ByRef. For a Range this causes the call to pass the default property of Rg ie Rg.Value, which is of cause a type mismatch in Auto_Colour

The solution: drop the (), ie use Auto_Colour Rg

Upvotes: 3

Related Questions