VBA BYREF参数类型不匹配

Question

最初在我的主代码部分中，我有一个丑陋的if语句 - 尽管丑陋会运行。我决定将其称为一个函数，这导致我遇到了一个错误" 编译错误：BYREF参数类型不匹配"。我的假设是，尽管我一直在阅读文档，但无法看到为什么>。＆lt;

声明ShiftValue变量： Dim ShiftValue As String
ShiftValue = LCase(Sheets("Raw_Rota").Cells(Counter, "C").Value)

功能内容和声明：

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

更新：

我的功能调用在if类似的if so的其他部分：

If X
"'Do stuff..."
Else 
Call ShiftCompare(ShiftValue)
EndIf

在功能线上提出了错误：

Function ShiftCompare(ByVal ShiftValue As String)

所引用的任何单元格中的值是空或字符串。

Answer 1

除了上面的Vityata答案外，请考虑一个选择的语句，以避免所有这些ElseIf语句。它运行速度更快。

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

Answer 2

呼叫函数时参数的值不是字符串。检查ShiftValue的值，然后重试。如果要确保它是字符串，请像此Call ShiftCompare("MyStringValue")

一样传递。

---

该函数的想法是它返回值是很好的。你的不回来。将Function更改为Sub。