生成在Excel中使用VBA唯一的随机号码列表
|在本文中，我们将创建一个自定义函数来生成指定范围之间的唯一和随机数列表。
在此示例中，我们可以通过单击“提交”按钮来运行宏。
在运行宏之前，我们必须输入四个参数的值。我们在单元格C12中提供了下限值，在单元格C13中提供了上限值，在单元格C14中需要唯一随机数，并且在单元格C15中需要输出目标地址。
逻辑解释
我们创建了“ UniqueRandomNumbers”自定义函数来生成唯一和随机数的列表。该功能将所需的数字，下限和上限作为输入参数。
我们创建了“ TestUniqueRandomNumbers”宏来调用“ UniqueRandomNumbers”自定义函数。通过单击“提交”按钮来执行此宏。该宏采用C12到C15范围内的用户输入值。
代码说明
i = CLng（Rnd（）*（ULimit-LLimit）+ LLimit）
上面的公式用于创建定义的上限和下限之间的随机数。 Rnd（）函数创建一个介于0和1之间的随机数。 Range（Selection，Selection.Offset（Counter-1，0））。Value = _ Application.Transpose（RandomNumberList）
上面的代码用于转置数组的输出并将输出分配给指定的目标。
请遵循以下代码
如果您喜欢此博客，请在Facebook和Facebook上与您的朋友分享。
Option Explicit
Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
'Declaring variables
Dim RandColl As Collection
Dim i As Long
Dim varTemp() As Long
'Validation check for the value specified by the user
If NumCount < 1 Then
UniqueRandomNumbers = "Number of unique random number required is less than 1"
Exit Function
End If
If LLimit > ULimit Then
UniqueRandomNumbers = "Specified lower limit is greater than specified upper limit"
Exit Function
End If
If NumCount > (ULimit - LLimit + 1) Then
UniqueRandomNumbers = "Number of required unique random number is greater than maximum number of unique number that can exists between lower limit and upper limit"
Exit Function
End If
'Creating new object of collection
Set RandColl = New Collection
Randomize
Do
On Error Resume Next
'Calculating the random number that exists between the lower and upper limit
i = CLng(Rnd() * (ULimit - LLimit) + LLimit)
'Inserting the unique random number in the collection
RandColl.Add i, CStr(i)
On Error GoTo 0
'Looping until collection have items equal to numCount
Loop Until RandColl.Count = NumCount
ReDim varTemp(1 To NumCount)
'Assigning value of the items in the collection to varTemp array
For i = 1 To NumCount
varTemp(i) = RandColl(i)
Next i
UniqueRandomNumbers = varTemp
Set RandColl = Nothing
Erase varTemp
End Function
Sub TestUniqueRandomNumbers()
'Declare variables
Dim RandomNumberList As Variant
Dim Counter As Long, LowerLimit As Long, UpperLimit As Long
Dim Address As String
'Getting the values input by the user
Counter = Range("C14").Value
LowerLimit = Range("C12").Value
UpperLimit = Range("C13").Value
Address = Range("C15").Value
'Calling custom function UniqueRandomNumbers
RandomNumberList = UniqueRandomNumbers(Counter, LowerLimit, UpperLimit)
'Selecting the destination
Range(Address).Select
'Assigning the value in the destination
Range(Selection, Selection.Offset(Counter - 1, 0)).Value = _
Application.Transpose(RandomNumberList)
End Sub
我们很希望收到您的来信，请让我们知道我们如何才能改善我们的工作并使您的工作更好。写信给我们[email protected]