从另一个函数中调用 VBA 函数的问题

Issue calling VBA Function from within another Function

提问by AsDuskFalls

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)

无论如何，该代码将用于获取包含轮班时间和语言能力的数据范围，并显示在给定时间段内有多少使用特定语言的人（以下代码中的 The_Time）

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, 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

谢谢，达拉赫 J

回答by Fionnuala

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.

c.Value 的赋值将失败，因为它包含文本，或者 InStr(stCell, "Eng") 永远不会为真，因为范围内的所有单元格都是数字。

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.

最后，也是最重要的是，您的函数将始终返回 0，因为您在开始时将计数器设置为 0，并且从不更新它。