CODEWITHSUNDEEP
arraysvbaperformanceloopsfor-loop
LLC
LLC

Reputation: 111

VBA Loop Optimisation by Storing a Range as an Array?

I have a Function that calculates an average payout. The loop calculates down from x days to y days. As part of this function I have to interpolate a number using a specific range. However this is very slow.

I read that a method to speed up the code would be to read the range as values rather than a Range as VBA is slowed down by going to Excel each time the code is run.

Is this true?

My current code. 

    Function AveragePayout(Time As Double, period)
        Dim i As Integer
        Dim sum As Double
        Dim interpolate_surface As Range        
        Set interpolate_surface = Range("A1", "D4")

        If Time < period Then
            AveragePayout = 0
        Else
            For i = 1 To period
                interpolated_val = Interpolation(interpolate_surface, 5, Time)
                sum = sum + CustomPricer(interpolated_value)
                Time = Time - 1
            Next i
            AveragePayout = sum / period
        End If
    End Function

I was thinking to change line 5 to the below to then run the Interpolation on an VBA matrix/array rather than returning to the Excel document each loop (which apparently slows the function tremendously:

Set interpolate_surface = Range("A1", "D4").Value2

Alternatively are there any other methods to speed up the running of this loop?

Many thanks.

Upvotes: 1

Views: 2770

Answers (2)

Brandon Barney
Brandon Barney

Reputation: 2392

While R.Leruth is very close, there are a few things that need to be elaborated on.

First, the reason why a Range object is slower is because you are working on the Object representation of that value, and there are events bound to that Range. As a result, calculations will run, the sheet will need to be evaluated, and accessing the value has to go through the Object, and not through an in-memory representation of that object.

This performance decrease generally stands true for any Range operations, and the performance decrease is directly tied to the size of the range. Thus, operating on 100 cells is much quicker than operating on 1,000,000 cells.

While performance time of an array is also directly linked, accessing each value is much quicker. This is because the the values are in-memory and easy to access. There are no Objects to depend on with an array. This doesn't mean arrays will always be fast. I have encountered instances of array operations taking many minutes or hours because I took their initial speed for granted. You will notice a performance decrease with arrays, but the rate of performance decrease is much much much lower.

To create an array, we use the Variant type. Keep in mind a Variant can be anything, so you have to be somewhat careful. General convention is to use Dim Foo as Variant() but then any argument that accepts or returns a Variant() must be given a Variant() and not a Variant (minor difference, huge impact on code). Because of this, I tend to use Dim Foo as Variant.

We can then assign the Values from a range back to the array. While Foo = Range("A1:B2") is functionally equivalent to Foo = Range("A1:B2").Value, I strongly recommend full qualification. Thus, I don't rely on implicit properties as much as I can (.Value is the implicit property of Range).

So our code should be:

Dim Foo as Variant
Foo = SomeRange.Value

Where Foo is your variable, and SomeRange is replaced with your range.

As long as your Interpolate function accepts an array, this should cause no issues whatsoever. If the Interpolate function doesn't accept an array, you may need to find another workaround (or write your own).

To output the array, we just need to create a range of the same size as our array. There are different ways of doing this. I tend to prefer this method:

SomeRange.Resize(UBound(SomeArray, 1) - LBound(SomeArray, 1) + 1, Ubound(SomeArray, 2) - LBound(SomeArray, 2) + 1)

All this does is takes some range (should be a single cell) and resizes that range by the number of columns in the array, and the number of rows in the array. I use (Ubound - Lbound) + 1 since, for a 0-based array, this will return Ubound + 1 and for a 1-based array it will return Ubound. It makes things much simpler than creating If blocks for the same purpose.

The last thing to make sure in all of this is that your Range variable is fully-qualified. Notice that Range("A1:B2").Value is functionally equivalent to ActiveSheet.Range("A1:B2").Value but again, relying on implicit calls quickly introduces bugs. Squash those out as much as possible. If you need the ActiveSheet then use it. Otherwise, create a Worksheet variable and point that variable to the correct sheet.

And if you must use the ActiveSheet then Dim Foo as Worksheet : Set Foo = ActiveSheet is much better than just using the ActiveSheet (since the ActiveSheet will generally change when you really need it not to, and then everything will break.

Best of luck in using arrays. They are performance changing but they are never an excuse for bad coding practices. Make sure you use them properly, and that you aren't introducing new inefficiencies just because you now can.

Upvotes: 2

R.Leruth
R.Leruth

Reputation: 11

What we usually do in VBA to speed up macros is to decrease the amount of interaction between the code and the sheet.

For example :

  1. Get all necessary values in an array

    Dim arr() as Variant
arr = Range("A1:D4")

  2. Treat the value

    ...

  3. Put them back

    Range("A1:D4") = arr

In your case just try to change interpolated_surface from Range to an array type.

Upvotes: 1

Related Questions