VBA Efficiency - Calculating on the "background" and passing to range

Question

it's been a month since i've started studying and practicing VBA in excel and this is the first time I ask a question in this forum, so keep that in mind.

The problem is this:

I have a sheet with 300k rows and 10 columns
Each row is the result of a calculation made in my script
The whole script takes 2min31s to run
I'm using ranges and all the options to speed up (e.g. "Application.Calculation = xlManual", etc)

Is it safe to think that performing the calculations on a memory variable would be faster that using a range? I've simplified the code to use as an example:

Option 1 - this is how I'm doing right now

For i = 0 To 300000
     sheet.Cells(i,1).Value = i^2
Next i

Option 2 - in my head this would run faster

For i = 0 To 300000
     variable(i) = i^2
Next i

'this doesn't work but it's just to illustrate what I'm looking for
sheet.Range(Cells(1,1),Cells(300000,1)).Value = variable

Can that be done? Can we pass the values of an array to a range all at once, avoiding looping through each cell of the range?

It is definitely possible to pass values from cells to a VBA array and back again, both in a single line of code. — John Coleman, Apr 06 '17 at 13:19
Possible duplicate of [Creating an Array from a Range in VBA](http://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba) — John Coleman, Apr 06 '17 at 13:19
I've seen the duplicate question but I can't seem to run the code the other way around... — Vicente Galle, Apr 06 '17 at 13:29
This only returns zeros -> Range(Cells(1, 1), Cells(300000, 1)).Value = variable — Vicente Galle, Apr 06 '17 at 13:32

z32a7ul · Accepted Answer · 2017-04-06T14:02:22.077

Yes it will be faster, however, you need a two dimensional array of Variants.

Dim variable(1 To 1, 1 To 300000) ' for a long row (but this would be too long for a row)
Dim variable(1 To 300000, 1 To 1) ' for a long column

This way you save time because you pass the data through the interface only once and not 300000 times. And calling the method to set the Value property has some fix cost because your Sub in the VBA editor is in the same process but a different apartment from Excel, so to cross the boundary RPC (Remote Procedure Call) is used.

BTW, if you want to know if a performance improvement works, you should measure it, like this:

Debug.Print "Started: " & Now()
' Your code
Debug.Print "Finished: " & Now()

Update

I tried, and it does work with one dimensional arrays. In this case it is interpreted as a long row. However, I think it's better to use the two dimensional array version because (1) it is cleaner (easy to see what the data will look like) and because (2) you get the data in the same format if you read it from a Range.

Update two

So I run these:

Public Sub SlowMethod(Optional cIterations As Long = 300001)
    Debug.Print "Started: " & Now()
    Dim i As Long: For i = 0 To cIterations - 1
        ActiveSheet.Cells(1 + i, 1).Value = i ^ 2
    Next i
    Debug.Print "Finished: " & Now()
End Sub

Public Sub FastMethod(Optional cIterations As Long = 300001)
    Debug.Print "Started: " & Now()
    Dim variable() As Variant: ReDim variable(1 To cIterations, 1 To 1)
    Dim i As Long: For i = 0 To cIterations - 1
        variable(1 + i, 1) = i ^ 2
    Next i
    ActiveSheet.Cells(1, 1).Resize(cIterations, 1).Value = variable
    Debug.Print "Finished: " & Now()
End Sub

And received:

SlowMethod
Started: 2017.04.06. 15:57:54
Finished: 2017.04.06. 15:58:56
FastMethod
Started: 2017.04.06. 15:59:02
Finished: 2017.04.06. 15:59:02

So its less than 1 second vs more than 1 minute.

I don't think that is true. Given a single variable, `A`, declared as a simple variant (no need to declare it as an array of variants) `A = R.Value` where R is the range variable/reference just works with no fuss. — John Coleman, Apr 06 '17 at 13:21
@JohnColeman The OP asked if he can place the value of 300000 different values into 300000 cells. You need an array to store 300000 variables. — z32a7ul, Apr 06 '17 at 13:31
Even though a Variant may contain an Array of Variants, you won't be able to write the code of the calculation. — z32a7ul, Apr 06 '17 at 13:32
Well, in case of reading, you need A = R.Value, however, the OP wants to write, not to read. — z32a7ul, Apr 06 '17 at 13:40
I see what you mean (+1), but even then you don't need a variable which has been explicitly declared to be a variant array of a size which matches the dimensions of the range. A simple variant can be redimed as needed. — John Coleman, Apr 06 '17 at 13:48
@z32a7ul. To get a more accurate timer use the method I've added to my answer. — CallumDA, Apr 06 '17 at 14:52

CallumDA · Answer 2 · 2017-04-06T20:01:08.040

1

Yes, it's much quicker. In fact, this take approx. 0.35 seconds on my computer.

Sub test()
    Dim v(1 To 300000, 1 To 1) As Variant
    Dim i As Long

    For i = 1 To 300000
        v(i, 1) = i ^ 2
    Next i

    Range("A1:A300000").Value = v
End Sub

If you're interested, I timed it using Timer.

Sub test()
    Dim t as Variant
    t = Timer

    'other code

    Debug.Print Timer - t
End Sub

edited Apr 06 '17 at 20:01

answered Apr 06 '17 at 13:46

CallumDA

12,025
6
30
52

Yes! Thank you, it worked really faster indeed. For 1kk rows this ran at 4s as opposed to 33s option I was doing before – Vicente Galle Apr 06 '17 at 13:52

score 0 · Answer 3 · answered Apr 06 '17 at 13:55

0

Yes, it worked!

This took 4 seconds to run

For i = 1 To 1000000
    variable(i, 1) = i ^ 2
Next i

Range(Cells(1, 1), Cells(1000000, 1)).Value = variable

This took 33 seconds to run

For i = 1 To 1000000
    sheet.Cells(i, 1) = i ^ 2
Next i

answered Apr 06 '17 at 13:55

Vicente Galle

25
4

VBA Efficiency - Calculating on the "background" and passing to range

Option 1 - this is how I'm doing right now

Option 2 - in my head this would run faster

3 Answers3

This took 4 seconds to run

This took 33 seconds to run