I'm using an Optional Argument as Variant in a code, but the code doesn't seem to be treating the argument as a number. Do I need to re-declare it as integer? or coerce it into number? here's the code that is resulting in error:

In a sheet with week number "1x3" for example, column A contains product names, column B contains codes. The codes contain the shift number in the 4th position (1, 2, or 3).

I want to count how many times the codes contain the shift number requested by the user. From what I can figure, it seems the code isn't treating the digit pulled out of the code as a number. Little help?


Function TotalCC(Week As String, Optional ShiftNum As Variant) As Integer

'Application.Volatile

'item ranges based on sheet name
Set ProductRange = Sheets(WeekNum).Range("A1:A100")

'initialize
CountVals = 0

'check for shiftnum argument
If IsMissing(ShiftNum) Then
'do some stuff - in this case, the shift argument is present
ElseIf Not IsMissing(ShiftNum) Then
For Each ProductCell In ProductRange
If Mid(ProductCell.Offset(0, 1).Value, 4, 1) * 1 = ShiftNum * 1 Then
On Error Resume Next
CountVals = CountVals + 1
ElseIf Len(ProductCell.Offset(0, 9).Value) < 4 Then
CountVals = CountVals + (1 / 3)
End If
Next
End If

TotalCC = CountVals

End Function

Here's how I enter the function in the worksheet: =TotalCC("1x2",3)