VBA ByRef argument type mismatch is inconsistent?

Question

I'm writing a short script in VBA that prints and compares timestamps in various cells. The code is working fine, however I'm confused with the inconsistency of the "ByRef arugement type mismatch". My code is below.

Function nextrow()
With ActiveSheet
    nextrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With

End Function
____

Private Sub buttonclick(nr As Integer)
With ActiveSheet
    .Cells(nr, 2) = Now
    If nr = 2 Then Exit Sub
        dur = .Cells(nr, 2) - .Cells(nr - 1, 2)
        .Cells(nr - 1, 3) = dur
    End With

End Sub
____

Private Sub distract2()
nr = nextrow

If nr = 2 Then Exit Sub
    buttonclick nr - 1

End Sub

If you look at distract2, you'll notice I haven't defined nr as an integer, but even so it passes through to buttonclick without a problem.

However, when I remove -1 from after nr, VBA throws a ByRef error.

Two questions:

Does anyone know why this happens?
Is it better to dim nr as Integer or not?

You should *always* put `Option Explicit` at the top of *every* code module. And you should *always* `Dim` every variable you use. Aside from that, try `buttonclick (nr-1) to perform the calculation *before* passing anything to `buttonclick`and see if that helps. If not, put in a line `nr = nr -1` before the `buttonclick` line. — Cindy Meister, Jan 16 '19 at 06:51
Your `function nextrow` doesn't return a value, the definition of a function. So it's actually a sub. `nr` will never equal anything. — catcat, Jan 16 '19 at 08:20
@Cindy, thanks for the tips. @catcat, that's a good point. If I changed it, do you think I would still need to `dim nr`? — Lawrence, Jan 16 '19 at 22:48

Siddharth Rout · Accepted Answer · 2019-01-16T07:09:34.073

Since you are dealing with rows, I would recommned using Long instead of Integer. You are getting that error because in Private Sub buttonclick(nr As Integer), it is expecting an Integer and you are passing a Variant

Change Private Sub buttonclick(nr As Integer) to Private Sub buttonclick(nr As Long)

and use this

Private Sub distract2()
    Dim nr As Long
    Dim nVal As Long

    nr = nextrow

    If nr = 2 Then Exit Sub

    nVal = nr - 1

    buttonclick nVal
End Sub

However, when I remove -1 from after nr, VBA throws a ByRef error. Two questions: Does anyone know why this happens? Is it better to dim nr as Integer or not?

When you keep -1, it is subtracting the value by 1 and the result is of Integer type and hence you do not get an error. If nr was 104857 then it would give an error. Interesting Read

Yes it is better to dim your variables as relevant datatype. However in your case it should be Long instead of Integer as mentioned above

Your complete code can be written as

Option Explicit

Private Sub distract2()
    Dim nr As Long
    Dim nVal As Long

    nr = nextrow

    If nr = 2 Then Exit Sub

    nVal = nr - 1

    buttonclick nVal
End Sub

Function nextrow() As Long
    With ActiveSheet
        nextrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
End Function

Private Sub buttonclick(nr As Long)
    With ActiveSheet
        .Cells(nr, 2) = Now
        If nr = 2 Then Exit Sub
        .Cells(nr - 1, 3) = .Cells(nr, 2) - .Cells(nr - 1, 2)
    End With
End Sub

Thanks for the writeup, it really clarified things for me – Lawrence Jan 16 '19 at 22:41 — Lawrence, Jan 16 '19 at 22:41

VBA ByRef argument type mismatch is inconsistent?

1 Answers1