Generate GUID or Unique Key Excel VBA (Visual Basic for Applications)

Unique Key

Excel application known for data storage and supports major Relational Database features where Primary Key is the core entity to maintain relationship between one or more tables.

In this article we will try to write a simple function which will return a unique key based on Prefix supplied by the user. This function is very useful when you want to maintain Unique ID within a table.

Step 1: Insert a module and put following code:

Public Function GetUniqueKey(PreFix As String) As String
    On Error GoTo errh
    'Use Now mehtod and format to get a unique datetime
    GetUniqueKey = PreFix & Format(Now, "DDMMYYYYHHMMSSMS")
errh:
    If Err.Number <> 0 Then
        GetUniqueKey = "Invalid key"
    End If
End Function

Step 2: return to Excel and put “=” to write a formula as shown below and pass a string it can be your table initial name:

Output

GUID

Function GenrateGuidExample() As String
  GenrateGuid = ""
  Dim oLib As Object
  Dim GUID As String
  Dim guid2 As String
  Set oLib = CreateObject("Scriptlet.TypeLib")
  GUID = Left$(oLib.GUID, 38)
  Set TypeLib = Nothing
  
  GUID = Replace(GUID, "{", "")
  GUID = Replace(GUID, "}", "")
  
  'Return result
  GenrateGuid = GUID
End Function

Calling

Public Sub GetGUID()
    Debug.Print GenrateGuid
End Sub

Output

40EB43C4-7798-4E96-88A1-CB9F25E61925

Next >> Allow Edit Range in a Protected Worksheet VBA code example

Share on Facebook

Tweet