excel vba udf: entire column as fn parameter

Question

I have a function that accepts a range as an input. It works fine when I supply the function with a range like a1:a5, but not when I use an entire column like a:a.

I could always loop over the whole column (ie. a:a) but that is so slow.

Here is a simple example.

Function r_sum(r1 As Range) As Long

    For Each c In r1.Cells
        r_sum = r_sum + c.Value
    Next c

End Function

How can I supply my functions with ranges (ie. a:a or 2:2, entire columns or rows) and have the function execute efficiently? How do excel's worksheet functions do it so well?

The method I used above (ie. looping over every cell in the range) is so slow that I can't possibly imagine using such a technique, especially since many of the functions I have currently are complex and will crash the program.

Answer 1

Using the Intersect method to trim off cells not in the Worksheet's UsedRange will greatly improve performance.

 Function SumRange(Target As Range) As Double
    Dim result As Double
    Dim Cell As Range
    Set Target = Intersect(Target.Parent.UsedRange, Target)
    If Not Target Is Nothing Then
        For Each Cell In Target.Cells
            result = result + Cell.Value
        Next
    End If
    SumRange = result
End Function

For maximum performance you should trim the target range and then load the values into an array.

Function SumRange(Target As Range) As Double
    Dim result As Double
    Dim values As Variant
    Dim item As Variant
    Set Target = Intersect(Target.Parent.UsedRange, Target)
    If Not Target Is Nothing Then
        values = Target.Value
        For Each item In values
            result = result + item
        Next
    End If
    SumRange = result
End Function

It is a best practice Option Explicit to the top of the code module to ensure that you declare all your variables. This will help the compiler catch syntax and datatype errors.

Avoid using underscores in you method names as the VBA uses them to indicate events.

Answer 2

What you have there is equivalent to =SUM(A:A) - which runs much faster as a native Excel function. Always use Native Excel in Excel itself before resorting to VBA.

As a general note: if you are going to do something bespoke - convert the range to an array and work with the array. This has a significant performance improvement for a number of reasons which have been explained many times on this site. The key ones are that you are not switching between the Excel model and the VBA model each loop and that you can work with data types instead of objects.

Answer 3

On the advice of AJD I've made some changes to the initial code from my question, which I think constitutes an answer.

The main issue with the code in the original post is working with the input range. But by storing the range's data as an array, the function performs much more quickly.

Function r_sum(r1 As Range) As Long

    Dim arr As Variant
    arr = r1.Value

    For Each a In arr
        r_sum = r_sum + a
    Next a

End Function

In this way, I can supply my functions with an entire column or row range (or even multiple) and expect that the function will perform its calculations quickly enough for me to use them.

That is basically what I was hoping for, and so I consider my question answered.