When using a Range, is the type different for each individual cell when looping via for each?

Question

I have this loop acting on a Range object called rng:

For Each r In rng
    orgArray = Split(r.Text, "},{")
        
    For Each orgRow In orgArray
        fieldArray = Split(orgRow, """,""")
        Call parseAndExtract(fieldArray, rng)
        
    Next orgRow
        
Next r

And this is the signature for the parseAndExtract sub:

Private Sub parseAndExtract(fieldArray() As String, rng As Range)

The VBA compiler doesn't complain about this, but when I change parseAndExtract(fieldArray, rng) to parseAndExtract(fieldArray, r) it gives me the error "ByRef argument type mismatch." Using TypeName() on rng and r seems to confirm that both are type Range. I will probably change this to pass the current row/column number instead of a Range object, but it's made me very curious. Is the type of r actually not the same as rng?

Answer 1

When you change rng to r, this is a type mismatch error if:

• You not declared explicitly the r variable
• You declared it, but the type is not Range

The parseAndExtract sub wants a Range object.

If in the calling sub the variable is not declared, it is implicitly a Variant, which cause the error. If it is declared it must be a Range type.

Either declare the r variable in the calling sub:

Dim r as Range

or change the called sub parameter type:

Private Sub parseAndExtract(fieldArray() As String, rng As Variant)
or
Private Sub parseAndExtract(fieldArray() As String, rng)