excel vba udf: entire column as fn parameter 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.
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.