excel-vba User Defined Functions (UDFs) Allow full column references without penalty
Example
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 with the Intersect method. 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 for a UDF used on a worksheet is the cell in which it resides. The cell's .Parent 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*])
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™.
