[VBA Research] VBA produces non-repeated random numbers

Iamlasong

VBA programming achieves random output without repeating. The random function in VBA is RND, and the random function in worksheet is Rand. The difference between the word and can be remembered. The RND value range is [0, 1). It indicates a random number between 0 and 1, which contains 0, but does not contain 1.

1. Usage

Syntax: RND [(number)]
If the value of number is generated by randomize
If the value is less than 0, the same result is obtained by using number as the random number seed each time.
If the value is greater than 0, the preceding random number is the next random number generated by the seed.
If the value is equal to 0, a random number that is the same as the recently generated random number is generated.
Omitted. The preceding random number is the seed to generate the next random number.

Note:
The RND function returns a value smaller than 1 but greater than or equal to 0.
The value of number determines how RND generates a random number.
The same sequence is generated for the first given seed, because each call to the RND function uses the previous number in the sequence as the seed of the next number.
Before calling RND, use the randomize statement without parameters to initialize the random number generator (if a parameter is included, a random number corresponding to a specific sequence of the parameter is generated ), the generator has a seed based on the system timer.
To generate a random integer in a range, use the following formula:
INT (upperbound-lowerbound + 1) * RND + lowerbound)
Here, upperbound is the upper limit of the random number range, while lowerbound is the lower limit of the random number range.
　　Note:If you want to obtain a recurring random number sequence, call RND with a negative parameter value before using randomize with a numeric parameter. Using randomize with the same number value will not produce repeated random number sequences.

2. Algorithm 1 with no repeated random numbers

This is the simplest algorithm. Every time a random number is generated, it is compared with the existing one. if it already exists, it is re-generated. It is more suitable to extract a small part of data from a large range, for example, to extract questions from the question bank.

'Generate 20 non-repeated random numbers between 1-. Public sub rndnumbernorepeat1 () dim rndnumber, temp (20), I, K, maxrec as integer randomize (timer) 'initialize the random number generator maxrec = 100 temp (0) = int (maxrec * RND) + 1 cells (21, 1) = temp (0) 'random number k = 1 do while K <20 rndnumber = int (maxrec * RND) + 1 temp (K) = rndnumber cells (K + 21, 1) is output from A21) = rndnumber for I = 0 to k-1 If temp (I) = rndnumber then exit for next I if I = k then K = I + 1 'msgbox "Random Number: "& rndnumber loop end sub

3. Non-repeated random number algorithm 2

This algorithm is clever and requires careful understanding to know the true meaning. This algorithm does not repeatedly generate random numbers, but requires a placeholder array. It is suitable for scenarios where all values within the output range, such as random licensing.

'Generate a random number of 20 1-100 distinct sub rndnumbernorepeat2 () dim rndnumber, temparray (99), I as integer randomize (timer) 'initialize random number generator for I = 0 to 99' to generate a random sequence containing 1-non-repeating temparray (I) = I next I for I = 99 to 80 step-1 rndnumber = int (I * RND) 'output these numbers cells (120-I, 1) from A21) = temparray (rndnumber) + 1 temparray (rndnumber) = temparray (I) Next iend sub

4. algorithm 3 without repeated random numbers

This algorithm uses dictionary objects to complete deduplication, which is similar to the first algorithm, but the program looks much more concise.

'Generate a random number of 20 1-100 non-repeated sub rndnumbernorepeat3 () dim D as object 'New dictionarydim s as integerrandomize (timer)' to initialize the random number generator set D = Createobject ("scripting. dictionary ") do until D. count = 20 s = int (RND * 100 + 1) D (S) = "" loop [A21]. resize (D. count, 1) = application. transpose (D. keys) end sub

Appendix: usage Summary of dictionary objects in VBA

Dim dict

'Create a dictionary
Set dict = Createobject ("scripting. Dictionary ")

'Add a project
Dict. Add "A", 300
Dict. Add "B", 400
Dict. Add "C", 500

'Count the number of projects
N = dict. Count

'Delete a project
Dict. Remove ("")

'Determine whether the dictionary contains keywords
Dict. exists ("B ")

'Get the value corresponding to the keyword. Make sure that the key exists before use. otherwise, an additional record will appear in dict.
Value = dict. Item ("B ")

'Modify the value corresponding to the keyword. If it does not exist, create a new project.
Dict. Item ("B") = 1000.
Dict. Item ("D") = 800.

'Looping the dictionary
K = dict. Keys
V = dict. Items
For I = 0 to dict. Count-1
Key = K (I)
Value = V (I)
Msgbox key & Value
Next

'Delete all projects
Dict. removeall

Instance:

Sub macro 1 ()

Set DIC = Createobject ("scripting. Dictionary") 'dictionary
For 1 to 10000
If not I like "* 4 *" then
Dic. Add I, "" if "1" is not included"
End if
Next
Range ("A2"). Resize (DIC. Count, 1) = application. worksheetfunction. Transpose (DIC. Keys) 'is placed down from Unit A2
End sub


========================================================== ======================================
Example of tranpose worksheet function usage


'Convert a two-dimensional array with multiple columns in one row into a one-dimensional array.
Sub test ()
Dim arr, arrt
Arr = range ("A1: J1 ")
Arrt = worksheetfunction. Transpose (worksheetfunction. Transpose (ARR ))
Stop
End sub
First, let's take a look at the basic usage of the transpose function. Official help shows that the transpose function can return to the transpose cell area, that is, the row cell area is transposed into the Column cell area, and vice versa.


The transpose function syntax is: transpose (array)
The array parameter is the array to be transposed or the cell area on the worksheet. The so-called array transpose is to take the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.