VBA function that generates a MS-style serial number?

Mark LaGrange
Mark LaGrange used Ask the Experts™
Sorry for having to ask this, but a few years ago I came across a VBA function that generates a random string that looks a lot like a typical MS software key, for example: H2F6F-W3RYD-AHDFYKV2PM-RBMBW

I have looked through my stuff and googled, and all I can find is about RND() and people's various hand-written code.

Can somebody please tell me the name of the VBA function that does this?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

These numbers are also called Global Unique Identifiers (GUID), and to my knowledge, there is no built-in function returning such values.
Are you working with MS Access or MS Excel ?

With MS Access, it is fairly easy:
Create a table, with a unique column, named ID.
Set the column's to auto-number, data type: Replication ID.
Save the table as GUID (hide it if you want).

Write a function that will:
Open a dynamic recordset on the GUID table.
Add a new record.
Return the ID field's value.
Cancel the update.

See the attached database for détails.
Fabrice LambertConsulting
Distinguished Expert 2017

I also found this on MSDN, using Windows API:
Option Explicit

    ' No VT_GUID available so must declare type GUID
Private Type GUID_TYPE
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

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

Public Function CreateGuidString() As String
    Dim guid As GUID_TYPE
    Dim strGuid As String
    Dim retValue As LongPtr
    Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}

    retValue = CoCreateGuid(guid)
    If retValue = 0 Then
        strGuid = String$(guidLength, vbNullChar)
        retValue = StringFromGUID2(guid, StrPtr(strGuid), guidLength)
        If retValue = guidLength Then
                ' valid GUID as a string
            CreateGuidString = strGuid
        End If
    End If
End Function

Open in new window


Thanks - your mention of "GUID" jogged my memory enough for me to go find the code I had used before:
strTmpARNmbr = Mid(CreateObject("Scriptlet.TypeLib").GUID, 2, 8)
(For that particular use, I was in Excel VBA, and only needed a portion of the whole string returned)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial