mercredi 18 octobre 2017

How to improve (speed up) some simple macro (with the rnd function)

I wrote this VBA code as a bootstrap procedure (unfortunately, R function don't allow to extract every resampled series, so I had to do it with excel and rnd) :

Sub bootstrap()

Dim i, j, k, l, m, n As Integer
l = 9
For l = 9 To 508

For j = 5 To 4040
 Randomize
 k = Int((4040 - 5 + 1) * Rnd + 5)

Cells(j, l + 1).Value = Cells(k, 9).Value
Next
Columns(l + 2).Insert
Next

End Sub

So basically, I have an original column with 4036 data, and I want to randomly resample this serie 500 times (hence the For l = 9 To 508 and For j = 5 To 4040). So my macro will compute 500 new columns with randomly reallocated data from the original column. Unfortunately, the macro takes forever to run (around 45mn).

Anybody got an idea on how to improve it ?

Thanks a lot for your help,




Aucun commentaire:

Enregistrer un commentaire