CODEWITHSUNDEEP
excelvbaoptimizationuser-defined-functions
Helbirah
Helbirah

Reputation: 61

User Defined Function in Personal.xlsb file is hanging up Excel. Is there a way to avoid this, and optimize execution?

I've created a user defined function in VBA, and saved it in Personal.xlsb file in order to have it available for all books. The intention of this function is to receive a range and a number. For all the numbers in the range, the first step is to round it to the next multiple of the specified number received as argument. After doing that rounding, the next step is to calculate the mode. The function is as follows:

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

The way I'm calling this the following:

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

Upvotes: 1

Views: 362

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Replace:

Function ROUNDEDMODEV(rng As Variant, multiple As Variant)

with:

Function ROUNDEDMODEV(rng As Range, multiple As Variant)

On my system (Win 10 / Excel 365) VBA was unable to resolve the Range rng into the 2-D array cellRange unless I explicitly Dim'ed as a Range.

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Consider changing the signature to make the expected data types more explicit:

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

A Variant can very well wrap a 2D variant array; consider declaring cellRange as follows:

Dim cellRange As Variant

The rng parameter being a Range (yours is a Variant/Range at run-time), it's an object, and objects don't quite work like values - this assignment involves a process named let coercion:

cellRange = rng

Let-coerced objects need to have a default member (if they don't, error 438 is raised) - in the case of the Excel.Range class, the default member is a hidden property named [_Default], that essentially invokes the Item property when parameterized... or the Value property otherwise. So we can make that assignment much more explicit about what's going on, like this:

cellRange = rng.Value

One note about this:

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

Because error handling is never restored (On Error GoTo 0), only the first iteration sees the On Error statement have any effect. Consider restoring error handling after the "dangerous" statement:

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

If an error is raised, it will be while evaluating the right-hand side of the assignment; in such cases, cellRange(r, c) will remain Variant/Empty.

When needed, you can make a Variant-returning function yield a worksheet error using the CVErr function:

ROUNDEDMODEV = CVErr(xlErrValue) 'xlErrNA, etc.

If your function does not need to return error codes, consider declaring its return type explicitly:

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

When testing your UDF, invoke it from the VBE's immediate toolwindow (Ctrl+G), not from a worksheet cell: a #VALUE! error likely means your function is throwing an unhandled error, and invoking it from VBA code (or the immediate pane) would give you much more debugging information.

Upvotes: 3

Related Questions