It's easier to implement some UDFs on the worksheet if full column references can be passed in as parameters. However, due to the explicit nature of coding, any loop involving these ranges may be processing hundreds of thousands of cells that are completely empty. This reduces your VBA project (and workbook) to a frozen mess while unnecessary non-values are processed.

Looping through a worksheet's cells is one of the slowest methods of accomplishing a task but sometimes it is unavoidable. Cutting the work performed down to what is actually required makes perfect sense.

The solution is to truncate the full column or full row references to the Worksheet.UsedRange property (opens new window) with the Intersect method (opens new window). The following sample will loosely replicate a worksheet's native SUMIF function so the criteria_range will also be resized to suit the sum_range since each value in the sum_range must be accompanied by a value in the criteria_range.

The Application.Caller (opens new window) for a UDF used on a worksheet is the cell in which it resides. The cell's .Parent (opens new window) property is the worksheet. This will be used to define the .UsedRange.

In a Module code sheet:

Option Explicit

Function udfMySumIf(rngA As Range, rngB As Range, _
                    Optional crit As Variant = "yes")
    Dim c As Long, ttl As Double
    
    With Application.Caller.Parent
        Set rngA = Intersect(rngA, .UsedRange)
        Set rngB = rngB.Resize(rngA.Rows.Count, rngA.Columns.Count)
    End With
    
    For c = 1 To rngA.Cells.Count
        If IsNumeric(rngA.Cells(c).Value2) Then
            If LCase(rngB(c).Value2) = LCase(crit) Then
                ttl = ttl + rngA.Cells(c).Value2
            End If
        End If
    Next c
    
    udfMySumIf = ttl

End Function

^Syntax:
=udfMySumIf(*sum_range*, *criteria_range*, [*criteria*])

(opens new window)

While this is a fairly simplistic example, it adequately demonstrates passing in two full column references (1,048,576 rows each) but only processing 15 rows of data and criteria.

_{Linked official MSDN documentation of individual methods and properties courtesy of Microsoft™.}

1. Open Excel
2. Open the Visual Basic Editor ( see Opening the Visual Basic Editor (opens new window) )
3. Add a new module by clicking Insert --> Module :

(opens new window)

1. Copy and Paste the following code in the new module :

Public Function Hello() As String
'Note: the output of the function is simply the function's name
Hello = "Hello, World !"
End Function

To obtain :

(opens new window)

1. Go back to your workbook and type "=Hello()" into a cell to see the "Hello World".

(opens new window)

Function countUnique(r As range) As Long
    'Application.Volatile False ' optional
    Set r = Intersect(r, r.Worksheet.UsedRange) ' optional if you pass entire rows or columns to the function
    Dim c As New Collection, v
    On Error Resume Next   ' to ignore the Run-time error 457: "This key is already associated with an element of this collection".
    For Each v In r.Value  ' remove .Value for ranges with more than one Areas
        c.Add 0, v & ""
    Next
    c.Remove "" ' optional to exclude blank values from the count
    countUnique = c.Count
End Function

Collections (opens new window)

Syntax

Remarks

A User Defined Function (aka UDF) refers to a task specific function that has been created by the user. It can be called as a worksheet function (ex: =SUM(...)) or used to return a value to a running process in a Sub procedure. A UDF returns a value, typically from information passed into it as one or more parameters.

It can be created by :

1. using VBA .
2. using Excel C API - By creating an XLL that exports compiled functions to Excel.
3. using the COM interface.