VBA ByRef argument type mismatch

Question

Initially in my main code section I had an ugly if statement - though ugly it would run. I decided to make it a function that I would call, this caused me to get an error "Compile error: ByRef argument type mismatch". My assumption is that the function needs to be referenced properly, though I've been reading the documentation and can't see why >.<

Declaring ShiftValue variable: Dim ShiftValue As String ShiftValue = LCase(Sheets("Raw_Rota").Cells(Counter, "C").Value)

The function contents and declaration:

Function ShiftCompare(ByRef ShiftValue As String)

If StrComp(ShiftValue, "am", vbTextCompare) = 0 Then
        Call IncAMs(AMs)   'this function increments the variable by 1.
        Call Inc(Counter)

    ElseIf StrComp(ShiftValue, "pm", vbTextCompare) = 0 Then
        Call IncPMs(PMs)
        Call Inc(Counter)

    ElseIf StrComp(ShiftValue, "days", vbTextCompare) = 0 Then
        Call IncDays(Days)
        Call Inc(Counter)

    ElseIf StrComp(ShiftValue, "leave", vbTextCompare) = 0 Then
        Call IncLeave(Leave)
        Call Inc(Counter)

    Else 'If the string doesn't compare to the above values tally it as unknown
        Call IncUnknown(Unknown)
        Call Inc(Counter)
    End If
End Function

Update:

My function call is in the Else part of an If section like so:

If X
"'Do stuff..."

Else 

Call ShiftCompare(ShiftValue)

EndIf

The error is raised on the Function line:

Function ShiftCompare(ByVal ShiftValue As String)

The value in whatever cell being referenced is either empty or a string.

Do you need it to be `ByRef`? It doesnt look like you are using it to return anything to the calling function, so try using `ByVal` instead. — braX, Sep 15 '17 at 12:29
ByVal didn't change anything - neither does just passing the variable on its own without specifying. Have updated the post to answer those questions Alex :) — John Higgs, Sep 15 '17 at 12:37
Change Function to Sub and it may work. Or pass the argument as a string in parenthesis. Like this: `Call ShiftCompare("ShiftValue")` — Vityata, Sep 15 '17 at 12:43
Negative unfortunately, will keep reading the docs myself - thanks for the help so far guys! — John Higgs, Sep 15 '17 at 12:45
@JohnHiggs - can you write `debug.print ShiftValue` in the line before calling the function? — Vityata, Sep 15 '17 at 12:47

score 1 · Answer 1 · answered Sep 15 '17 at 12:57

In addition to Vityata's answer above, consider a Select statement to avoid all those ElseIf statements. It runs faster.

Option Compare Text
Public Sub ShiftCompare(ByVal ShiftValue As String)
    Select Case True
        Case StrComp(ShiftValue, "am") = 0:
            Call IncAMs(AMs)   'this function increments the variable by 1.
            Call Inc(Counter)

        Case StrComp(ShiftValue, "pm") = 0:
            Call IncPMs(PMs)
            Call Inc(Counter)

        Case StrComp(ShiftValue, "days") = 0:
            Call IncDays(Days)
            Call Inc(Counter)

        Case StrComp(ShiftValue, "leave") = 0:
            Call IncLeave(Leave)
            Call Inc(Counter)

        Case Else:  'If the string doesn't compare to the above values tally it as unknown
            Call IncUnknown(Unknown)
            Call Inc(Counter)
    End Select
End Sub

Vityata · Accepted Answer · 2017-09-15T12:46:23.257

0

The value of the parameter when you are calling the function is not a string. Check the value of ShiftValue and try again. If you want to make sure it is a string, pass it like this Call ShiftCompare("MyStringValue")

The idea of the Function is that it is good that it returns a value. Yours does not return. Change Function to Sub.

edited Sep 15 '17 at 12:46

answered Sep 15 '17 at 12:40

Vityata

42,633
8
55
100

1

That's the one - the value was empty when being called. My initial for loop was picking up null or empty variable values and dealing with them on my last if statement, whereas when calling the function the value can't be null/empty. Will verify the value is not null/empty before calling that sub. Thanks for the help!! – John Higgs Sep 15 '17 at 12:54
1

@JohnHiggs - just FYI - null and empty are different terms in VBA. Be careful for which one you are making the check. – Vityata Sep 15 '17 at 12:57

VBA ByRef argument type mismatch

2 Answers2

Linked