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