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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With