Issue calling VBA Function from within another Function

Below is the code I've written however I keep getting an issue with the line I've added the comment to, and only that line. I've commented out all the other lines and isolated this as the problem line but for the life of me and with the hour or more of research I've done I cannot figure out what the issue is. It's probably a really obvious one, but I'm really stuck and it's driving me crazy.

Anyway, the code is to be used to take a Range of data containing shift times and language capability and show how many people with a specific language are available during a given time period (The_Time in the code below)

Any help would be greatly appreciated!

Function ReturnAvailability(The_Time As String, The_Info As Range)

Dim The_Lang As String
Dim The_Shift_Start As String
Dim The_Shift_End As String
Dim stGotIt As String
Dim stCell As Integer
Dim Counter As Integer

Counter = 0

For Each r In The_Info.Rows
    For Each c In r.Cells
        stCell = c.Value
        If InStr(stCell, "Eng") > 0 Then
            The_Lang = "Eng"
        ElseIf InStr(c, ":") > 0 Then
            stGotIt = StrReverse(c)
            stGotIt = Left(c, InStr(1, c, " ", vbTextCompare))
            The_Shift_End = StrReverse(Trim(stGotIt))
            stGotIt = Left(The_Shift, InStr(1, The_Shift, " ", vbTextCompare))
            The_Shift_Start = stGotIt
            stCell = ReturnAvailabilityEnglish(The_Time, The_Shift_Start, The_Shift_End) ' this is the line causing the error
        End If
    Next c
Next r

ReturnAvailability = Counter

End Function


Function ReturnAvailabilityEnglish(The_Time As String, The_Shift_Start As String, The_Shift_End As String)

Dim Time_Hour As Integer
Dim Time_Min As Integer
Dim Start_Hour As Integer
Dim Start_Min As Integer
Dim End_Hour As Integer
Dim End_Min As Integer
Dim Available As Integer

Available = 13

Time_Hour = CInt(Left(The_Time, 2))
Time_Min = CInt(Right(The_Time, 2))
Start_Hour = CInt(Left(The_Shift_Start, 2))
Start_Min = CInt(Right(The_Shift_Start, 2))
End_Hour = CInt(Left(The_Shift_End, 开发者_Python百科2))
End_Min = CInt(Right(The_Shift_End, 2))

If Start_Hour <= Time_Hour And Start_Min <= Time_Min Then
    If End_Hour > Time_Hour And End_Min > Time_Min Then
        Available = 1
    Else
        Available = 0
    End If
End If

ReturnAvailabilityEnglish = Available

End Function

Thanks, Darragh J

You have declared

Dim stCell As Integer

Which means that this part cannot work:

stCell = c.Value
If InStr(stCell, "Eng") > 0 Then

Either the assignment of c.Value will fail, because it contains text, or InStr(stCell, "Eng") will never be true, because all cells in the range are numeric.

You are missing a text compare:

 If InStr(1, stCell, "Eng", vbTextCompare) > 0 Then

This is also a problem, you need to add a check as illustrated:

If The_Time = vbNullString Or The_Shift_Start = vbNullString _
    Or The_Shift_End = vbNullString Then
    Available = -1
Else

    Time_Hour = CInt(Left(The_Time, 2))
    Time_Min = CInt(Right(The_Time, 2))
    Start_Hour = CInt(Left(The_Shift_Start, 2))
    Start_Min = CInt(Right(The_Shift_Start, 2))
    End_Hour = CInt(Left(The_Shift_End, 2))
    End_Min = CInt(Right(The_Shift_End, 2))

    If Start_Hour <= Time_Hour And Start_Min <= Time_Min Then
        If End_Hour > Time_Hour And End_Min > Time_Min Then
            Available = 1
        Else
            Available = 0
        End If
    End If
End If
ReturnAvailabilityEnglish = Available

Finally, and most importantly, your function will always return 0, because you set counter to 0 at the beginning and never update it.