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.