excel - Personal.xlsb 文件中的用户定义函数挂起 Excel。有没有办法避免这种情况并优化执行？

Question

我在 VBA 中创建了一个用户定义的函数，并将其保存在 Personal.xlsb 文件中，以使其可用于所有书籍。这个函数的目的是接收一个范围和一个数字。对于范围内的所有数字，第一步是将其舍入到作为参数接收的指定数字的下一个倍数。进行四舍五入后，下一步是计算众数。功能如下:

Function ROUNDEDMODEV(rng As Variant, multiple As Variant)
    ' Array with the values from the selected range.
    Dim cellRange() As Variant
    ' Copy the specified range values to array
    cellRange = rng
    ' Looping through the array.
    Dim r As Long
    Dim c As Long
    For r = 1 To UBound(cellRange, 1) ' First array dimension is rows.
        For c = 1 To UBound(cellRange, 2) ' Second array dimension is columns.
            On Error Resume Next
            cellRange(r, c) = WorksheetFunction.MRound(cellRange(r, c), multiple)
        Next c
    Next r
    ROUNDEDMODEV = WorksheetFunction.mode(cellRange)
End Function

我将其称为以下方式:

=PERSONAL.XLSB!ROUNDEDMODEV(IF($B$2:$B$20="some text", $C$2:$Z$20),0.5)

Answer 1

考虑更改签名以使预期的数据类型更加明确:

Public Function ROUNDEDMODEV(ByVal rng As Range, ByVal multiple As Double) As Variant

一个 Variant可以很好地包装一个二维变体数组；考虑声明 cellRange如下:

Dim cellRange As Variant

rng参数是 Range (你的是 Variant/Range 在运行时)，它是一个对象，并且对象不像值那样工作 - 这个分配涉及一个名为 let coercion 的过程:

cellRange = rng

Let-coerced 对象需要有一个默认成员(如果没有，则会引发错误 438) - 在 Excel.Range 的情况下类，默认成员是一个名为 [_Default] 的隐藏属性，这实际上调用了 Item参数化时的属性...或 Value否则属性(property)。因此，我们可以使该分配更明确地说明正在发生的事情，如下所示:

cellRange = rng.Value

关于这一点的一个注意事项:

    For c = 1 To UBound(cellRange, 2) ' Second array dimension is columns.
        On Error Resume Next

因为永远不会恢复错误处理( On Error GoTo 0 )，所以只有第一次迭代会看到 On Error声明有任何影响。考虑在“危险”语句之后恢复错误处理:

On Error Resume Next
cellRange(r, c) = WorksheetFunction.MRound(cellRange(r, c), multiple)
On Error GoTo 0

如果出现错误，它将在评估赋值的右侧时；在这种情况下， cellRange(r, c)将保留 Variant/Empty .

需要的时候可以打 Variant -返回函数使用 CVErr 产生工作表错误功能:

ROUNDEDMODEV = CVErr(xlErrValue) 'xlErrNA, etc.

如果您的函数不需要返回错误代码，请考虑显式声明其返回类型:

Public Function ROUNDEDMODEV(ByVal rng As Range, ByVal multiple As Double) As Double

测试 UDF 时，从 VBE 的即时工具窗口 (Ctrl+G) 调用它，而不是从工作表单元格调用它: #VALUE!错误可能意味着您的函数正在引发未处理的错误，并且从 VBA 代码(或即时 Pane )调用它会为您提供更多调试信息。