# Generating random data: Excel functions vs VBA

#### fundamental

##### New Member

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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try
Code:
``````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)

Last edited:
Try
Code:
``````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!

Replies
1
Views
882
Replies
1
Views
251
Replies
1
Views
221
Replies
1
Views
882
Replies
3
Views
455

### Forum statistics

1,196,052
Messages
6,013,112
Members
441,748
Latest member
MrBigglesworth ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back