CreateGuid: A Reliable Way to Generate GUIDs in VBA

I had the need to generate a GUID (globally unique identifier) in VBA today.

This seemed like a simple enough task, but there's surprisingly little good information available on the topic in 2022.  

What NOT To Do

Here are a few of the most common search results that I found:

Scripting Guys: Two-liner

I started with this Scripting Guys article that seemed to be exactly what I needed.

Alas, I received a permissions error when trying to run the code:

This is the result of a security fix and is by design:

This control is blocked as a security measure to help prevent malicious code from running in Office applications.

Workaround  The preferred method is to change the code to use Windows API CoCreateGuid instead of CreateObject("Scriptlet.TypeLib").Guid

Rolling Your Own

This next one comes from Daniel Ferry: Excel VBA: Create GUIDs, Easily.

Seems straightforward enough.  And this will indeed create Version 4 GUIDs.  There's just one small problem.  If you need a bunch of them all at once, they may not be unique.  And that kind of defeats the purpose of a globally unique identifier.

With the caveat that I did not reproduce the following results, I suspected this might be the case before I even saw the comment (EDIT: I ended up being able to reproduce these results; see below for details):

Three collisions (at least) out of only 1,200 generated GUIDs is definitely a problem.  

Most likely, the problem has to do with the implicit seed being passed to the Randomize function.  If the optional [number] argument is not used, the function relies on the results of the VBA Timer function to provide a seed.  The Timer function provides "fractional portions of a second," but rapid and repeated calls to the Timer function will likely result in at least a few instances where the exact same seed value is returned.  This in turn may lead to duplicated "randomness":

Generating pseudorandom numbers is one of those deceptively difficult computer science challenges.  Which is why it's best to rely on a library that was explicitly developed to generate GUIDs.

CoCreateGUID: A Well-Tested Option

The CoCreateGuid Win32 API function is part of the COM library ().  

As the COM ecosystem relies heavily on GUIDs that are, in fact, globally unique, I have a high degree of confidence that this is one of our best–if not the best–options in Windows (feel free to prove me wrong in the comments...I'll update the article accordingly).

The code below is adapted from one of my favorite sites to get VBA-compatible Windows API code samples: AllAPI.net.  The site has not been maintained for years (e.g., you'll need to update all the samples to run in 64-bit VBA), but it is still a treasure trove of great information.

The function below was adapted from the CoCreateGuid page:

'These Declare lines go at the very top of the code module
#If VBA7 Then
    Private Declare PtrSafe Function CoCreateGuid Lib "ole32" (id As Any) As Long
#Else
    Private Declare Function CoCreateGuid Lib "ole32" (id As Any) As Long
#End If


' ----------------------------------------------------------------
' Procedure  : CreateGUID
' Author     : Dan (webmaster@1stchoiceav.com)
' Source     : http://allapi.mentalis.org/apilist/CDB74B0DFA5C75B7C6AFE60D3295A96F.html
' Adapted by : Mike Wolfe
' Republished: https://nolongerset.com/createguid/
' Date       : 8/5/2022
' ----------------------------------------------------------------
Public Function CreateGUID() As String
    Const S_OK As Long = 0
    Dim id(0 To 15) As Byte
    Dim Cnt As Long, GUID As String
    If CoCreateGuid(id(0)) = S_OK Then
        For Cnt = 0 To 15
            CreateGUID = CreateGUID & IIf(id(Cnt) < 16, "0", "") + Hex$(id(Cnt))
        Next Cnt
        CreateGUID = Left$(CreateGUID, 8) & "-" & _
                     Mid$(CreateGUID, 9, 4) & "-" & _
                     Mid$(CreateGUID, 13, 4) & "-" & _
                     Mid$(CreateGUID, 17, 4) & "-" & _
                     Right$(CreateGUID, 12)
    Else
        MsgBox "Error while creating GUID!"
    End If
End Function

Testing GUID Creation

To be sure we are generating unique GUIDs, I devised the following simple test routine:

Sub TestCreateGuid()
    Const Iterations As Long = 10000
    
    Dim GuidDict As Object 'Scripting.Dictionary
    Set GuidDict = CreateObject("Scripting.Dictionary")
    
    Dim i As Long
    For i = 1 To Iterations
        Dim Guid As String
        Guid = CreateGUID
        
        If GuidDict.Exists(Guid) Then
            Debug.Print "Duplicate GUID created: " & Guid
            Stop
        Else
            GuidDict.Add Guid, ""
        End If
    Next i
    
    Debug.Print GuidDict.Count; " unique GUIDs generated out of "; Iterations; " attempts"
End Sub

I ran the test with additional iterations until I reached a practical limit of 1,000,000–which took about five minutes to complete.  

CreateGUID() Is Rock Solid

Here are the results of testing the CreateGUID() function:

GUID$() ... Not So Much

Here are the results of testing the GUID$() function:

Eleven duplicates out of 10,000 is indeed unacceptable.

Image by Gerd Altmann from Pixabay