samedi 23 octobre 2021

Is it possible to set seed with `RandArray` in Excel?

I need to generate a few sets of random numbers and a minimal working example of my code is as follows:

Sub Macro1()

With Sheets("Economic Assumptions")

    .Range("B10:BL10") = WorksheetFunction.RandArray(1, 63)
    .Range("B11:BL11") = WorksheetFunction.RandArray(1, 63)
    .Range("B12:BL12") = WorksheetFunction.RandArray(1, 63)
    .Range("B13:BL13") = WorksheetFunction.RandArray(1, 63)

End With

With Sheets("Simulations")

   .Range("K11:K61") = WorksheetFunction.RandArray(51, 1)
    
End With

End Sub

However, I am wondering if there is a way to set a seed, such that each time I run this, I always get the same set of random numbers. To be more precise, as one might be able to tell, I am generating five different sets of random numbers here, four sets in one worksheet and one more in another. When I mean I want the same set of random numbers, I mean that each set should always give me the same random numbers, not that I have the exact same random numbers across all sets. In fact, each set (within the same run of the macro), should be giving different random numbers, so I imagine I would need five different seeds here.

In particular, I know how to set a seed in the following context:

Sub xx()

    x = Rnd(-1)
    Randomize 10

End Sub

In this way, each time I run xx, I will get the same set of 10 random numbers, so I would like to know if this is possible with RandArray too, or otherwise, how should I go about tweaking my code if I would like to achieve what I have described above?




Aucun commentaire:

Enregistrer un commentaire