Generate A Guid To A Selected Range Of Cells With Vba
A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resources. The term GUID is generally used by developers working with Microsoft technologies, while UUID is used everywhere else.
How unique is a GUID?
128-bits is big enough and the generation algorithm is unique enough that if 1,000,000,000 GUIDs per second were generated for 1 year the probability of a duplicate would be only 50%. Or if every human on Earth generated 600,000,000 GUIDs there would only be a 50% probability of a duplicate.
'==================================================================================================
' ## Declarations for the GUID type and for Windows API
'==================================================================================================
Private Type GUID_TYPE
'// Vars
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type
'// Test for 32 or 64 bit Excel
#If VBA7 Then
Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (guid As GUID_TYPE) As LongPtr
Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As LongPtr
#Else
Private Declare Function CoCreateGuid Lib "ole32.dll" (guid As GUID_TYPE) As Long
Private Declare Function StringFromGUID2 Lib "ole32.dll" (guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As Long
#End If
'==================================================================================================
' ## Function to call Windows API and grab a GUID
' Using this method as July 2017 Windows 10 security update
' throws a permission denied error trying to use: CreateObject("Scriptlet.TypeLib")
'==================================================================================================
Function CreateGuidString(Optional AddHyphens As Boolean, _
Optional AddBraces As Boolean) _
As String
'// Vars
Dim guid As GUID_TYPE
Dim strGuid As String
Dim retValue As LongPtr
'// registry GUID format with null
' terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
Const guidLength As Long = 39
retValue = CoCreateGuid(guid)
'// Get the raw GUID which includes braces and hyphens
If retValue = 0 Then
strGuid = String$(guidLength, vbNullChar)
retValue = StringFromGUID2(guid, StrPtr(strGuid), guidLength)
If retValue = guidLength Then
CreateGuidString = strGuid
End If
End If
'// If AddHyphens is switched from the default True to False,
' remove them from the GUID
If Not AddHyphens Then
CreateGuidString = Replace(CreateGuidString, "-", vbNullString, Compare:=vbTextCompare)
End If
'// If AddBraces is True from the default False to True,
' leave those curly braces be!
If Not AddBraces Then
CreateGuidString = Replace(CreateGuidString, "{", vbNullString, Compare:=vbTextCompare)
CreateGuidString = Replace(CreateGuidString, "}", vbNullString, Compare:=vbTextCompare)
End If
End Function
'==================================================================================================
' ## Insert a GUID to the selected cells
' This example has both braces and hyphens set to true
'==================================================================================================
Sub GuidToCell()
'// Vars
Dim rngCell As Range
Dim rngSelection As Range
'// Loop through selection and apply GUID to each cell
Set rngSelection = Application.Selection
For Each rngCell In rngSelection
rngCell.Value = CreateGuidString(True, True)
Next rngCell
End Sub