VBA Code to Generate Random Unique IDs in Excel

VBA Code:

' VBA Code to Generate Random Unique IDs in Excel

Sub GenerateRandomUniqueIDs()
    Dim ws As Worksheet
    Dim rng As Range
    Dim numRows As Long
    Dim numDigits As Integer
    Dim uniqueIDs As Collection
    Dim newID As String
    Dim i As Long
    
    ' Set the worksheet and range to generate IDs
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace with the name of your worksheet
    Set rng = ws.Range("A1:A10") ' Replace with the range where you want to generate the IDs
    
    ' Set the number of rows and number of digits in each ID
    numRows = rng.Rows.Count
    numDigits = 6 ' Adjust as needed
    
    ' Clear the existing IDs in the range
    rng.ClearContents
    
    ' Create a collection to store unique IDs
    Set uniqueIDs = New Collection
    
    ' Generate and assign unique IDs to the range
    For i = 1 To numRows
        newID = GenerateRandomID(numDigits)
        While Not IsUniqueID(uniqueIDs, newID)
            newID = GenerateRandomID(numDigits)
        Wend
        rng.Cells(i).Value = newID
        uniqueIDs.Add newID
    Next i
    
    ' Display a message
    MsgBox "Random unique IDs have been generated."
End Sub

Function GenerateRandomID(numDigits As Integer) As String
    Dim i As Integer
    Dim newID As String
    
    Randomize ' Initialize the random number generator
    
    ' Generate a random ID with the specified number of digits
    For i = 1 To numDigits
        newID = newID & CStr(Int((9 - 0 + 1) * Rnd + 0))
    Next i
    
    GenerateRandomID = newID
End Function

Function IsUniqueID(uniqueIDs As Collection, newID As String) As Boolean
    On Error Resume Next
    uniqueIDs.Add newID, CStr(newID) ' Try adding the new ID to the collection
    IsUniqueID = (Err.Number <> 457) ' Check if there was an error (457 indicates duplicate key)
    On Error GoTo 0
End Function


Check All VBA Codes

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group

Leave a Comment