自定义函数在 VBA 中工作，并在 Excel 单元格中调用时返回 #VALUE "wrong data type"

Question

我有一个 UDF，用于计算给定一周内有多少客户端和服务工作者，或者客户端有多少服务工作者。数据是两个不同的范围，一个按服务工作进程排序，另一个按客户端排序，以防止必须首先排序。

该代码创建一个数组来记录我正在搜索的一周行的行引用，然后遍历 at 计数 worker 或客户端的最后一个实例。该代码在 VBA 中工作，并在 excel 中返回一个 #VALUE，并在 Excel 中返回"错误检查"，指示错误的数据类型。使用中断时，代码在创建范围时似乎失败，这可能与从范围中获取行引用的数组的创建有关，也可能与创建有关。

我想知道我是否正在尝试修改"excel 状态"。对问题的想法以及如何解决？

Public Function clientOrServiceWorkerCount(startWeek As Integer, endWeek As 
Integer, searchID As Long, outputType As Integer, sheetName As Variant) As 
Integer
    clientOrServiceWorkerCount = 0
    Dim WSMod111 As Worksheet
    Set WSMod111 = sheetName

    Dim rowOffset As Integer
    rowOffset = 3
    Dim endIndex As Integer
    endIndex = WSMod111.Cells(WSMod111.Rows.count, 13).End(xlUp).row - 
    rowOffset
    Dim tempRange As Range
    Set tempRange = WSMod111.Range("U4:Z" & endIndex)
    Dim tempArray() As Integer
    Dim weekNow As Integer
    Dim weekNext As Integer
    Dim ClientIDNow As Long
    Dim ClientIDNext As Long
    Dim serviceWorkerIDNow As Integer
    Dim serviceWorkerIDNext As Integer
    Dim arrayID As Integer
    Dim serviceWorkerCount As Integer
    Dim cLCount As Integer
    Dim colOffset As Integer
    Dim arrayCount As Integer
    arrayCount = 0
    ' Offset which columns to refer to based on the outputType value. 1 = 
    search for client then service workers
    ' 2 = search for service workers and then clients
    If outputType = 1 Then
        colOffset = 0
    End If
    If outputType = 2 Then
        colOffset = 3
    End If
    'Build the array for the week range of interest, defined by startWeek 
and endWeek
    For i = 0 To endIndex - 1
        weekNow = tempRange(i + 1, 1 + colOffset)
        arrayID = tempRange(i + 1, 2 + colOffset)
        If weekNow >= startWeek And weekNow <= endWeek And arrayID = 
        searchID Then
            ReDim Preserve tempArray(arrayCount)
            tempArray(arrayCount) = i + 1
            arrayCount = arrayCount + 1
            'Print the results to the worksheet to check answers
            tempRange.Cells(arrayCount, 8) = tempArray(arrayCount - 1)
        End If
    Next i
   For n = 0 To arrayCount - 1
        weekNow = tempRange(tempArray(n), 1 + colOffset)
        serviceWorkerIDNow = tempRange(tempArray(n), 2 + colOffset)
        ClientIDNow = tempRange(tempArray(n), 3 + colOffset)
        'Debugging printing
        'tempRange.Cells(1 + n, 9) = weekNow
        'tempRange.Cells(1 + n, 11) = serviceWorkerIDNow
        'tempRange.Cells(1 + n, 13) = ClientIDNow
        If n < arrayCount - 1 Then
            weekNext = tempRange(tempArray(n + 1), 1 + colOffset)
            serviceWorkerIDNext = tempRange(tempArray(n + 1), 2 + colOffset)
            ClientIDNext = tempRange(tempArray(n + 1), 3 + colOffset)
            'Debugging printing
            tempRange.Cells(1 + n, 10) = weekNext
            tempRange.Cells(1 + n, 12) = serviceWorkerIDNext
            tempRange.Cells(1 + n, 14) = ClientIDNext
        End If
        If outputType = 1 Then
            If ClientIDNow <> ClientIDNext Or n = arrayCount - 1 Then
                cLCount = cLCount + 1
            End If
        End If
        If outputType = 2 Then
            If serviceWorkerIDNow <> serviceWorkerIDNext Or n = arrayCount - 
            1 Then
                serviceWorkerCount = serviceWorkerCount + 1
            End If
        End If
    Next
    'Return the count of either the clients (outputType=1) or the serviceWorkerinators (outputType=2)
    If outputType = 1 Then
        clientOrServiceWorkerCount = cLCount
    End If
    If outputType = 2 Then
       clientOrServiceWorkerCount = serviceWorkerCount
    End If
End Function

Answer 1

您希望在工作表上使用用户定义的函数，但 UDF 包含更改单元格值的内容。这不能通过将函数直接应用于工作表来完成，因此会发生错误。

    'Debugging printing
    tempRange.Cells(1 + n, 10) = weekNext
    tempRange.Cells(1 + n, 12) = serviceWorkerIDNext
    tempRange.Cells(1 + n, 14) = ClientIDNext

在过程中使用函数的工作方式如下所示。

Sub test()
    Dim c As Integer
    c = clientOrServiceWorkerCount(2, 3, 1, 1, "Sheet2")
    Range("r1") = c
End Sub

如果您注释掉有问题的代码，则结果在工作表上效果很好。

Public Function clientOrServiceWorkerCount( _
        startWeek As Integer, _
        endWeek As Integer, _
        searchID As Long, _
        outputType As Integer, _
        sheetName As Variant) As Integer
    clientOrServiceWorkerCount = 0
    Dim WSMod111 As Worksheet
    Set WSMod111 = Sheets(sheetName)'<~~ Your code should change like this.

    Dim rowOffset As Integer
    rowOffset = 3
    Dim endIndex As Integer
    endIndex = WSMod111.Cells(WSMod111.Rows.Count, 13).End(xlUp).Row - rowOffset
    Dim tempRange As Range
    Set tempRange = WSMod111.Range("U4:Z" & endIndex)
    Dim tempArray() As Integer
    Dim weekNow As Integer
    Dim weekNext As Integer
    Dim ClientIDNow As Long
    Dim ClientIDNext As Long
    Dim serviceWorkerIDNow As Integer
    Dim serviceWorkerIDNext As Integer
    Dim arrayID As Integer
    Dim serviceWorkerCount As Integer
    Dim cLCount As Integer
    Dim colOffset As Integer
    Dim arrayCount As Integer
    arrayCount = 0
    ' Offset which columns to refer to based on the outputType value. 1 = search for client then service workers
    ' 2 = search for service workers and then clients
    If outputType = 1 Then
        colOffset = 0
    End If
    If outputType = 2 Then
        colOffset = 3
    End If
    'Build the array for the week range of interest, defined by startWeek and endWeek
    For i = 0 To endIndex - 1
        weekNow = tempRange(i + 1, 1 + colOffset)
        arrayID = tempRange(i + 1, 2 + colOffset)
        If weekNow >= startWeek And weekNow <= endWeek And arrayID = searchID Then
            ReDim Preserve tempArray(arrayCount)
            tempArray(arrayCount) = i + 1
            arrayCount = arrayCount + 1
            'Print the results to the worksheet to check answers
            'tempRange.Cells(arrayCount, 8) = tempArray(arrayCount - 1)
        End If
    Next i
   For n = 0 To arrayCount - 1
        weekNow = tempRange(tempArray(n), 1 + colOffset)
        serviceWorkerIDNow = tempRange(tempArray(n), 2 + colOffset)
        ClientIDNow = tempRange(tempArray(n), 3 + colOffset)
        'Debugging printing
        'tempRange.Cells(1 + n, 9) = weekNow
        'tempRange.Cells(1 + n, 11) = serviceWorkerIDNow
        'tempRange.Cells(1 + n, 13) = ClientIDNow
        If n < arrayCount - 1 Then
            'weekNext = tempRange(tempArray(n + 1), 1 + colOffset)
            'serviceWorkerIDNext = tempRange(tempArray(n + 1), 2 + colOffset)
            'ClientIDNext = tempRange(tempArray(n + 1), 3 + colOffset)
            'Debugging printing
            'tempRange.Cells(1 + n, 10) = weekNext
            'tempRange.Cells(1 + n, 12) = serviceWorkerIDNext
            'tempRange.Cells(1 + n, 14) = ClientIDNext
        End If
        If outputType = 1 Then
            If ClientIDNow <> ClientIDNext Or n = arrayCount - 1 Then
                cLCount = cLCount + 1
            End If
        End If
        If outputType = 2 Then
            If serviceWorkerIDNow <> serviceWorkerIDNext Or n = arrayCount - 1 Then
                serviceWorkerCount = serviceWorkerCount + 1
            End If
        End If
    Next
    'Return the count of either the clients (outputType=1) or the serviceWorkerinators (outputType=2)
    If outputType = 1 Then
        clientOrServiceWorkerCount = cLCount
    End If
    If outputType = 2 Then
       clientOrServiceWorkerCount = serviceWorkerCount
    End If
End Function

Answer 2

Dy.Lee，我使用您的建议注释掉了我的调试打印：

    For n = 0 To arrayCount - 1
        weekNow = tempRange(tempArray(n), 1 + colOffset)
        serviceWorkerIDNow = tempRange(tempArray(n), 2 + colOffset)
        ClientIDNow = tempRange(tempArray(n), 3 + colOffset)
        'Debugging printing
        'tempRange.Cells(1 + n, 9) = weekNow
        'tempRange.Cells(1 + n, 11) = serviceWorkerIDNow
        'tempRange.Cells(1 + n, 13) = ClientIDNow
        If n < arrayCount - 1 Then
            weekNext = tempRange(tempArray(n + 1), 1 + colOffset)
            serviceWorkerIDNext = tempRange(tempArray(n + 1), 2 + colOffset)
            ClientIDNext = tempRange(tempArray(n + 1), 3 + colOffset)
            'Debugging printing
            'tempRange.Cells(1 + n, 10) = weekNext
            'tempRange.Cells(1 + n, 12) = serviceWorkerIDNext
            'tempRange.Cells(1 + n, 14) = ClientIDNext

而且它工作正常。我还更新了您所做的 Sheet（"） 引用，并且不再收到编译错误。我不知道我的调试代码违反了"单元格中引用的函数不能更改除引用的单元格之外的任何内容"的规则 - 因为我不知道该规则的存在。非常感谢您的帮助，几周来我一直在办公桌上猛击我的头，试图找出问题所在。问候，安东尼