Generate Unique IDs With Excel VBA

Objective:

We want to generate unique IDs in Excel using VBA. These unique IDs can be used for various purposes like identifying records in a database, tracking items in inventory, or assigning users to a system.

Step-by-Step Explanation

In the following VBA code, we will create a procedure that generates unique IDs based on the current date, a prefix, and a sequential number.

1. Prefix: We will use a prefix that will make the ID more meaningful. For example, « USR » for users or « INV » for inventory items.
2. Date-based component: We will incorporate the current date or year/month/day in the ID to make it time-dependent.
3. Sequential number: A counter will ensure that the ID is unique within that specific day. Every time we generate an ID, the counter will increment.

Code

Sub GenerateUniqueID()
    Dim prefix As String
    Dim dateComponent As String
    Dim counter As Long
    Dim uniqueID As String
    Dim lastRow As Long
    Dim idColumn As Long  
    ' Define the column where the IDs will be stored (e.g., Column A)
    idColumn = 1  ' This represents column A
    ' Define the prefix for the unique ID (e.g., "USR" for user)
    prefix = "USR-"
    ' Get the current date in yyyy-mm-dd format
    dateComponent = Format(Date, "yyyy-mm-dd")
' Find the last row with data in the column (adjust if your sheet has data in different columns)
    lastRow = Cells(Rows.Count, idColumn).End(xlUp).Row  
    ' Check if there are any existing IDs for today's date
    counter = 1
    If lastRow > 1 Then
        ' Loop through the column to find the most recent ID and increment the counter
        Do While Cells(lastRow, idColumn).Value Like prefix & dateComponent & "-" & Format(counter, "0000")
            counter = counter + 1
            lastRow = lastRow - 1
        Loop
    End If   
    ' Generate the unique ID with prefix, date, and counter
    uniqueID = prefix & dateComponent & "-" & Format(counter, "0000")  
    ' Write the unique ID into the next empty row in the ID column
    Cells(lastRow + 1, idColumn).Value = uniqueID
    ' Display a message to the user
    MsgBox "Generated Unique ID: " & uniqueID, vbInformation, "Unique ID Generated"
End Sub

Detailed Explanation

1. Defining the Variables:
   • prefix: This is the constant string that will be used as the starting part of the ID. For example, « USR- » for user-related IDs.
   • dateComponent: This stores the current date in the yyyy-mm-dd format. This ensures that each ID is unique to the day it is generated.
   • counter: This variable will be used to generate sequential numbers for IDs that are generated on the same day.
   • lastRow: This is used to find the last row in the column where IDs are being stored. It is important to determine where to insert the new unique ID.
   • idColumn: This specifies the column where IDs are being generated. In this example, it’s set to 1, which refers to column A.
2. Getting the Current Date:
   • The Format(Date, « yyyy-mm-dd ») function formats the current date as yyyy-mm-dd. This makes the generated ID time-sensitive.
3. Finding the Last Row:
   • Cells(Rows.Count, idColumn).End(xlUp).Row finds the last row in the idColumn that contains data. This ensures that we add the new unique ID below the last generated ID.
4. Checking for Existing IDs:
   • The code checks whether any IDs for today already exist in the column. It does this by looping through the column starting from the last row. The pattern for today’s date and counter (« USR-yyyy-mm-dd-0001 ») is used to check if the ID already exists. If it does, the counter is incremented and the loop continues until an unused ID is found.
5. Generating the Unique ID:
   • Once a unique ID is found, the code generates it using the prefix, dateComponent, and counter. The counter is formatted to ensure that it always has four digits (e.g., 0001, 0002).
6. Inserting the Unique ID:
   • The new unique ID is inserted into the next available row in the idColumn. The Cells(lastRow + 1, idColumn).Value = uniqueID line ensures that the unique ID is placed in the next empty cell.
7. Message Box:
   • A message box is displayed to inform the user that a new unique ID has been generated.

Customizations:

• Prefix: You can change the prefix to whatever suits your needs, such as « INV » for inventory, « ORD » for orders, etc.
• Date Format: You can change the date format if you want a different structure. For example, Format(Date, « mmddyyyy ») for a different date structure.
• Column and Range: If you want to generate IDs in a different column or sheet, adjust the idColumn variable or specify a particular range.

Example Output:

If you run the macro on March 27, 2025, the generated IDs might look like this:

USR-2025-03-27-0001
USR-2025-03-27-0002
USR-2025-03-27-0003

Conclusion:

This VBA script is a simple and effective way to generate unique IDs within Excel. It leverages the current date, a sequential counter, and a customizable prefix to ensure that the IDs are meaningful and non-repetitive. You can customize this script further to suit your specific needs, such as adjusting the ID format or adding additional components (like a time stamp or random number).