CODEWITHSUNDEEP
excelvbabyref
Lawrence
Lawrence

Reputation: 960

VBA ByRef argument type mismatch is inconsistent?

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:

Upvotes: 1

Views: 219

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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

Upvotes: 1

Related Questions