lundi 23 octobre 2017

VBA Random Figure Generator - could be more efficient?

I have random generating code like below. It generates "x" amount of figures from predefined population range. It take some time to generate them and I'm wandering if it can be more efficient? And numbers can't duplicate.

Private Sub CommandButton1_Click()

Dim real_rnd As Double
Dim letter_lng As Long
Dim lngCounter As Long
Dim lngRandomFigureList(2000) As Long
Dim i As Integer
Dim lngPopulation As Long
Dim intNoOfSamples As Integer
Dim strCell As String
Dim blnDuplicate As Boolean
Dim blnFound As Boolean


Sheets("random selection").Select
Range("Pop").Select
lngPopulation = ActiveCell.Value

Range("NoSamp").Select
intNoOfSamples = ActiveCell.Value

Range("Figures").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("Figures").Select

Range("Figures").Select

For lngCounter = 1 To intNoOfSamples
    blnDuplicate = True
    Do While blnDuplicate = True
        real_rnd = Rnd() * (lngPopulation - 1) + 1
        letter_lng = Abs(real_rnd)
        blnFound = False
        For i = 1 To lngCounter - 1
            If letter_lng = lngRandomFigureList(i) Then
                blnFound = True
            End If
        Next i
        If blnFound = False Then
            blnDuplicate = False
        End If
    Loop

    lngRandomFigureList(lngCounter) = letter_lng
    strCell = "A" & 6 + lngCounter
    Range(strCell).Select
    ActiveCell.Value = letter_lng
Next lngCounter`




Aucun commentaire:

Enregistrer un commentaire