Range object not passing into Function Excel VBA

Question

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.

YOu are not "setting" the range object, which you would need to do — Doug Coats
– Doug Coats, Commented Mar 4, 2018 at 22:25

chris neilsen · Accepted Answer · 2018-03-04 22:39:53Z

3

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

answered Mar 4, 2018 at 22:39

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Range object not passing into Function Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related