Generating random data: Excel functions vs VBA

[fundamental]

Hello, can someone please assist.

I was writing some VBA recently and noticed it was running slower than expected relative to when I simply copied a formula. I wondered if the VBA could be optimised to make the speed comparable.

I put the following formula into Cell A1 and copied it down to cell A1048576

="ABC" & RIGHT(10^9+ RANDBETWEEN(0,10^9),9)​

This took a couple of seconds.

I then tried to do the same using the following VBA routine and it took more like 30 seconds.
Why is it so slow and how can it be radically sped up?!

Sub gendata_in_A()
​

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim r As Long
For r = 1 To 2^20​

Cells(r, 1) = "ABC" + Right(10 ^ 9 + Int(10^9 * Rnd()), 9)​

Next
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic​

End Sub​

Thanks for any help given.

 

[mikerickson]

Try

Sub gendata_in_A()
    Dim arrOutPut as Variant
    Dim r As Long
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Redim arrOutPut(1 to 2^20, 1 to 1)
    For r = 1 To 2^20
        arrOutPut(r, 1) = "ABC" + Right(10 ^ 9 + Int(10^9 * Rnd()), 9)
    Next
    Range("A1").Resize(2^20, 1).Value = arrOutput

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

A single (2^20 calculations followed by one mass writing to sheet) is much faster than 2^20 (calculations + 1 small write to sheet)

 

[fundamental]

Try

Sub gendata_in_A()
    Dim arrOutPut as Variant
    Dim r As Long
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Redim arrOutPut(1 to 2^20, 1 to 1)
    For r = 1 To 2^20
        arrOutPut(r, 1) = "ABC" + Right(10 ^ 9 + Int(10^9 * Rnd()), 9)
    Next
    Range("A1").Resize(2^20, 1).Value = arrOutput

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

A single (2^20 calculations followed by one mass writing to sheet) is much faster than 2^20 (calculations + 1 small write to sheet)

Nice one Mike. I put some timing in and the original is actually ~25 seconds, whereas you have radically improved it to ~7 seconds.
Cheers!