2
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ Please do not update the code in your question to incorporate feedback from answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers. \$\endgroup\$
    – Mast
    Nov 9, 2019 at 19:23

3 Answers 3

3
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ this is really interesting. most of my sheets are in the <10000 rows area, so what you suggested is so relevant. I just ran a simulation. I generated random values in column A between rows 0-2000, and ran a sub routine executing first my revised function (range->arr), against one using your tip (intersect + range->arr) and the second was over 1000 times faster. \$\endgroup\$ Nov 9, 2019 at 22:06
4
\$\begingroup\$

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.

\$\endgroup\$
3
  • \$\begingroup\$ I am aware of the existence of the sum function. The point was to show a very simple function that was easy to follow and would demonstrate how I would use a full column range as an argument. But converting the range to an array made the calculation a lot faster. \$\endgroup\$ Nov 9, 2019 at 18:38
  • \$\begingroup\$ I revised the code of the function to convert the range to an array and use the array instead. In terms of performance, is this the best move I can make? \$\endgroup\$ Nov 9, 2019 at 18:47
  • \$\begingroup\$ @dactyrafficle yes it is. And you should iterate arrays with a For...Next loop, too; For Each...Next works best with object collections, and incurs a serious performance penalty with arrays. \$\endgroup\$ Nov 10, 2019 at 0:04
2
\$\begingroup\$

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.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ The normal protocol here is to select the most appropriate/useful answer for your needs and mark is as 'accepted' (the tick on the side). This then tells other viewers that the question has an accepted answer which is useful for the long-term exposure and curation of posts. Cheers. \$\endgroup\$
    – AJD
    Nov 10, 2019 at 4:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.