【VBA】重複のない乱数を作成するマクロを４つ紹介します！

Public Sub random()
'Rnd関数を使ったコード（Randomizeなし）

    Dim ws As Worksheet
    Dim rngCard As Range
    Dim i As Long

    'Randomize

    Const adress As String = "B3:F7" 'BINGOカードの対象範囲
    
    Set ws = ThisWorkbook.Worksheets(1)
    
    Set rngCard = ws.Range(adress)
    
    For i = 1 To rngCard.Count
        rngCard(i).Value = Int(Rnd * 100) + 1
    Next

End Sub

Public Sub random2()
'ExcelのRandBetween関数を使ったコード

    Dim ws As Worksheet
    Dim rngCard As Range
    Dim i As Long
    
    Const adress As String = "B3:F7" 'BINGOカードの対象範囲
    
    Set ws = ThisWorkbook.Worksheets(1)
    
    Set rngCard = ws.Range(adress)
    
    For i = 1 To rngCard.Count
        rngCard(i).Value = WorksheetFunction.RandBetween(1, 99)
    Next

End Sub

Public Sub random3()
'重複しない乱数を作るコード（配列使用）
    
    Dim num(24) As Long
    Dim i As Long
    Dim buf As Long
    Dim ws As Worksheet
    Dim rngCard As Range
    Dim j As Long
    Dim k As Long
    
    Const adress As String = "B3:F7" 'BINGOカードの対象範囲
    
    i = 0
    Do Until i > 24
        buf = WorksheetFunction.RandBetween(1, 99)
        If Not ExistsNumber(buf, num) Then
            num(i) = buf
            i = i + 1
        End If
    Loop
    
    Set ws = ThisWorkbook.Worksheets(1)
    
    Set rngCard = ws.Range(adress)
    
    j = 0
    For k = 1 To rngCard.Count
    rngCard(k).Value = num(j)
        j = j + 1
    Next
End Sub

Private Function ExistsNumber(buf As Long, num() As Long) As Boolean
    
    Dim i As Long
    
    For i = 0 To 24
        If buf = num(i) Then
            ExistsNumber = True
            Exit For
        End If
    Next
    
    ExistsNumber = False
    
End Function

Public Sub random4()
'重複しない乱数を作るコード（Dictionary）
    
    Dim dic As Dictionary
    Dim i As Long
    Dim buf As Long
    Dim ws As Worksheet
    Dim rngCard As Range
    Dim j As Long
    Dim k As Long
    
    Const adress As String = "B3:F7" 'BINGOカードの対象範囲
    
    Set dic = New Dictionary
    
    i = 0
    Do Until i > 24
        buf = WorksheetFunction.RandBetween(1, 99)
        If Not dic.Exists(buf) Then
            dic.Add buf, i
            i = i + 1
        End If
    Loop
    
    Set ws = ThisWorkbook.Worksheets(1)
    
    Set rngCard = ws.Range(adress)
    
    j = 0
    For k = 1 To rngCard.Count
        rngCard(k).Value = dic.Keys(j)
        j = j + 1
    Next
    
End Sub

Public Sub random5()
'連番からランダムに抜き出すコード(Collection)　フィッシャーイェーツ
    
    Dim cll As Collection
    Dim i As Long
    Dim ws As Worksheet
    Dim rngCard As Range
    Dim j As Long
    Dim n As Long 'CollectionのIndex
    
    Const adress As String = "B3:F7" 'BINGOカードの対象範囲
    
    Set cll = New Collection
    For i = 1 To 99
        cll.Add i
    Next i
    
    Set ws = ThisWorkbook.Worksheets(1)
    
    Set rngCard = ws.Range(adress)
    
    For j = 1 To rngCard.Count
        n = WorksheetFunction.RandBetween(1, cll.Count)
        rngCard(j).Value = cll(n)
        cll.Remove n
    Next
    
End Sub

Public Sub random6()
'連番をシャッフルするコード(配列)　ダステンフェルド

    Dim i As Long
    Dim j As Long
    Dim RandomNumber As Long
    Dim st As Long
    Dim arr() As Long
    Dim ws As Worksheet
    Dim rngCard As Range
    Dim k As Long
    
    Const adress As String = "B3:F7" 'BINGOカードの対象範囲
    
    Randomize
    ReDim arr(1 To 99)
    
    For i = 1 To 99
        arr(i) = i
    Next
    
    For j = 99 To 1 Step -1
        RandomNumber = Int(j * Rnd) + 1
        st = arr(j)
        arr(j) = arr(RandomNumber)
        arr(RandomNumber) = st
    Next
    
    Set ws = ThisWorkbook.Worksheets(1)
    
    Set rngCard = ws.Range(adress)
    
    j = 1
    For k = 1 To rngCard.Count
        rngCard(k).Value = arr(j)
        j = j + 1
    Next
End Sub