Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA ByRef argument type mismatch

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.

like image 426
John Higgs Avatar asked Feb 16 '26 07:02

John Higgs


1 Answers

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
like image 200
Kostas K. Avatar answered Feb 19 '26 13:02

Kostas K.