How can I generate GUIDs in Excel?

Question

I have an excel file with one order on each row, and I want each order to have a unique identifier, so there will be a Unique ID column. Every time I fill a row, I want Excel to automatically populate the Unique ID column for me. I did some research and was pointed in the direction of GUIDs. I found the following code:

Function GenGuid() As String
Dim TypeLib As Object
Dim Guid As String
Set TypeLib = CreateObject("Scriptlet.TypeLib")
Guid = TypeLib.Guid
' format is {24DD18D4-C902-497F-A64B-28B2FA741661}
Guid = Replace(Guid, "{", "")
Guid = Replace(Guid, "}", "")
Guid = Replace(Guid, "-", "")
GenGuid = Guid
End Function

but I am not sure how I can implement it. Any help would be greatly appreciated. Thank you in advance.

I don't think your problem is generating a GUID. You have working code for that. You can even get a GUID into any cell by assigning it the formula =GenGuid() though it changes on every evaluation of the formula. Your question should rather be: How can I have Excel automatically populate a cell with the result of a VBA function? — Codo, Aug 11 '11 at 20:25
Warning: The above code will add random garbage to the end of the string: stackoverflow.com/questions/19556268/… — Chloe, Oct 24 '13 at 3:18
Don't put this function into a cell as a formula =GenGuid() - this will generate all new GUIDs for each row everytime the worksheet recalculates! Instead, use Worksheet events to look for changes to the sheet, and within that you'll need to devise a way to determine if a new/blank row is being edited ("is GUID column blank on this row?"), and if it is then you need to set cell value to the generated GUID (so that it remains static). That's the best way to ensure that once you assign a row a GUID that it keeps the same value forever. — 4AM, Dec 10 '20 at 19:42

Stephen · Accepted Answer · 2021-04-06 11:24:59Z

The following Excel expression evaluates to a V4 GUID:

=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8))

-or (depending on locale setting/decimal and list separators)-

=CONCATENATE(DEC2HEX(RANDBETWEEN(0;4294967295);8);"-";DEC2HEX(RANDBETWEEN(0;65535);4);"-";DEC2HEX(RANDBETWEEN(16384;20479);4);"-";DEC2HEX(RANDBETWEEN(32768;49151);4);"-";DEC2HEX(RANDBETWEEN(0;65535);4);DEC2HEX(RANDBETWEEN(0;4294967295);8))

Note that the first character of the third group is always 4 to signify a V4 (pseudo-random number generated) GUID/UUID per RFC 4122 section 4.4.

Also note that the first character of the fourth group is always between 8 and B per the same RFC.

Standard disclaimer: the resulting GUIDs/UUIDs are not cryptographically strong.

Edit: remove invisible characters

This didn't work for me in Excel 2007. I received a generic there is something wrong with this formula error. — Shane Courtrille, Nov 23 '12 at 17:45
Works perfectly on Excel for Mac, just have to change ; to , — Mirko Akov, Dec 11 '12 at 13:12
Here is the function with the changes this also works in Windows with the ',' change =CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8)) — GregM, Jan 18 '13 at 19:36
@MirkoAkkov it's not the Mac, it's the Thread.CurrentCulture's System.Globalization.CultureInfo instance that holds a TextInfo object which defines a ListSeparator property that determines the character Excel will correctly parse between function arguments in Excel an formula. In Windows this (along with many other settings) can be viewed/changed in the regional settings from the control panel. — Mathieu Guindon, Jan 24 '13 at 5:33

rchacko · Accepted Answer · 2014-09-25 02:04:05Z

33

I used the following function in v.2013 excel vba to create a GUID and is working well..

Public Function GetGUID() As String 
    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36) 
End Function

edited Sep 25 '14 at 2:04

answered Apr 17 '14 at 7:02

rchacko

1,72921 silver badges23 bronze badges

Nice piece of code. To eliminate the hyphens you could make it GetGUID = Replace(Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36),"-",vbNullString)
– Bob Cutler
May 8 '14 at 18:45
2

It will not work with the latest Windows update. Here is an information how to use this macro in such case: stackoverflow.com/questions/45332357/…
– Pawel Maga
Aug 16 '17 at 11:44
2

Since windows update taken out "Scriptlet.TypeLib", try the following: Declare Function CoCreateGuid Lib "ole32" (ByRef GUID As Byte) As Long Public Function GenerateGUID() As String Dim ID(0 To 15) As Byte Dim N As Long Dim GUID As String Dim Res As Long Res = CoCreateGuid(ID(0)) For N = 0 To 15 GUID = GUID & IIf(ID(N) < 16, "0", "") & Hex$(ID(N)) If Len(GUID) = 8 Or Len(GUID) = 13 Or Len(GUID) = 18 Or Len(GUID) = 23 Then GUID = GUID & "-" End If Next N GenerateGUID = GUID End Function
– rchacko
Jan 25 '18 at 2:49

Add a comment |

Mathieu Guindon · Accepted Answer · 2013-01-24 05:55:07Z

I know this question is answered, but I think the code in question should look something like what's on this page: http://snipplr.com/view/37940/

Haven't tested, but this code seems to tap into the Windows API to get its GUID's - I would try putting that in a public module and typing =GetGUId() in an Excel cell to see what I'd get. If it works in VB6 you have a great deal of a good chance it works in VBA as well:

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long

Public Function GetGUID() As String
'(c) 2000 Gus Molina

    Dim udtGUID As GUID

    If (CoCreateGuid(udtGUID) = 0) Then

        GetGUID = _
            String(8 - Len(Hex$(udtGUID.Data1)), "0") & Hex$(udtGUID.Data1) & _
            String(4 - Len(Hex$(udtGUID.Data2)), "0") & Hex$(udtGUID.Data2) & _
            String(4 - Len(Hex$(udtGUID.Data3)), "0") & Hex$(udtGUID.Data3) & _
            IIf((udtGUID.Data4(0) < &H10), "0", "") & Hex$(udtGUID.Data4(0)) & _
            IIf((udtGUID.Data4(1) < &H10), "0", "") & Hex$(udtGUID.Data4(1)) & _
            IIf((udtGUID.Data4(2) < &H10), "0", "") & Hex$(udtGUID.Data4(2)) & _
            IIf((udtGUID.Data4(3) < &H10), "0", "") & Hex$(udtGUID.Data4(3)) & _
            IIf((udtGUID.Data4(4) < &H10), "0", "") & Hex$(udtGUID.Data4(4)) & _
            IIf((udtGUID.Data4(5) < &H10), "0", "") & Hex$(udtGUID.Data4(5)) & _
            IIf((udtGUID.Data4(6) < &H10), "0", "") & Hex$(udtGUID.Data4(6)) & _
            IIf((udtGUID.Data4(7) < &H10), "0", "") & Hex$(udtGUID.Data4(7))
    End If

End Function

Thanks Gus Molina!

If this code works (which I don't doubt), I think you'd get a new set of GUID's whenever the function gets evaluated, which means everytime the sheet gets calculated - when you're saving the workbook, for example. Make sure to copy-pastespecial-values if you need the GUID's for later use... which is somewhat likely.

Anyone able to add comments to that code to explain what it's doing? — Emily Beth, Sep 11 '18 at 22:28
@EmilyBeth it invokes CoCreateGuid from OLE32.DLL, stores the result in a user-defined type, then left-pads each part with 0s and converts them into hex strings. Data1 As Long holds the first 4 bytes, Data2 and Data3 As Integer hold the next 4 bytes, then Data4 holds the remaining 8 bytes. A GUID is really just a very, very large number stored across 16 bytes, with specific parts. — Mathieu Guindon, Sep 11 '18 at 22:38

Erik A · Accepted Answer · 2017-09-28 16:31:01Z

A VBA approach based on generating random numbers using the Rnd() function, and not on external API calls or Scriptlet.TypeLib:

Public Function CreateGUID() As String
    Do While Len(CreateGUID) < 32
        If Len(CreateGUID) = 16 Then
            '17th character holds version information
            CreateGUID = CreateGUID & Hex$(8 + CInt(Rnd * 3))
        End If
        CreateGUID = CreateGUID & Hex$(CInt(Rnd * 15))
    Loop
    CreateGUID = "{" & Mid(CreateGUID, 1, 8) & "-" & Mid(CreateGUID, 9, 4) & "-" & Mid(CreateGUID, 13, 4) & "-" & Mid(CreateGUID, 17, 4) & "-" & Mid(CreateGUID, 21, 12) & "}"
End Function

This essentially is a VBA implementation of NekojiruSou's answer (it also generates a v4 GUID), and carries the same limitations, but will work in VBA and might be easier to implement.

Note that you can omit the last line to not return the dashes and curly braces in the result.

Alekzander · Accepted Answer · 2017-11-24 11:24:03Z

I found pretty solution here:
http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=751237&msg=8634441

Option Explicit

Private Type GUID
  Data1 As Long
  Data2 As Integer
  Data3 As Integer
  Data4(0 To 7) As Byte
End Type
Private Declare Function CoCreateGuid Lib "ole32" (pguid As GUID) As Long
Private Declare Function StringFromGUID2 Lib "ole32" ( _
  rguid As GUID, ByVal lpsz As Long, ByVal cchMax As Long) As Long

Public Function CreateGUID() As String
  Dim NewGUID As GUID
  CoCreateGuid NewGUID
  CreateGUID = Space$(38)
  StringFromGUID2 NewGUID, StrPtr(CreateGUID), 39
End Function

Chake · Accepted Answer · 2012-12-19 11:47:22Z

Same same for german Excel version:

=VERKETTEN(DEZINHEX(ZUFALLSBEREICH(0;4294967295);8);"-";DEZINHEX(ZUFALLSBEREICH(0;65535);4);"-";DEZINHEX(ZUFALLSBEREICH(16384;20479);4);"-";DEZINHEX(ZUFALLSBEREICH(32768;49151);4);"-";DEZINHEX(ZUFALLSBEREICH(0;65535);4);DEZINHEX(ZUFALLSBEREICH(0;4294967295);8))

The version is actually the same. It's the local settings that determine what the function aliases are and what character delimits function arguments. — Mathieu Guindon, Jan 24 '13 at 5:37

rchacko · Accepted Answer · 2018-01-25 02:54:38Z

Since windows update taken out "Scriptlet.TypeLib", try the following:

Declare Function CoCreateGuid Lib "ole32" (ByRef GUID As Byte) As Long
Public Function GenerateGUID() As String
    Dim ID(0 To 15) As Byte
    Dim N As Long
    Dim GUID As String
    Dim Res As Long
    Res = CoCreateGuid(ID(0))

    For N = 0 To 15
        GUID = GUID & IIf(ID(N) < 16, "0", "") & Hex$(ID(N))
        If Len(GUID) = 8 Or Len(GUID) = 13 Or Len(GUID) = 18 Or Len(GUID) = 23 Then
            GUID = GUID & "-"
        End If
    Next N
    GenerateGUID = GUID
End Function

Alternatively,

if you are connecting to SQL Server 2008 or higher, try to use the SQL NEWID() function instead.

Note that, if you're using Access, not Excel, you can use Application.StringFromGUID(ID) to convert the GUID to a string. Saves you from iterating over it yourself, and creates a GUID in a format that you can easily convert back to bytes using Application.GuidFromString. — Erik A, Aug 22 '18 at 11:02

mphase · Accepted Answer · 2017-07-21 19:48:28Z

I recently ran into problems using CreateObject("Scriptlet.TypeLib") in some vba code.

So based on NekojiruSou excel functions wrote the following which should work without any specific excel functions. This can be used to develop a user defined function in excel.

Public Function Get_NewGUID() As String
    'Returns GUID as string 36 characters long

    Randomize

    Dim r1a As Long
    Dim r1b As Long
    Dim r2 As Long
    Dim r3 As Long
    Dim r4 As Long
    Dim r5a As Long
    Dim r5b As Long
    Dim r5c As Long

    'randomValue = CInt(Math.Floor((upperbound - lowerbound + 1) * Rnd())) + lowerbound
    r1a = RandomBetween(0, 65535)
    r1b = RandomBetween(0, 65535)
    r2 = RandomBetween(0, 65535)
    r3 = RandomBetween(16384, 20479)
    r4 = RandomBetween(32768, 49151)
    r5a = RandomBetween(0, 65535)
    r5b = RandomBetween(0, 65535)
    r5c = RandomBetween(0, 65535)

    Get_NewGUID = (PadHex(r1a, 4) & PadHex(r1b, 4) & "-" & PadHex(r2, 4) & "-" & PadHex(r3, 4) & "-" & PadHex(r4, 4) & "-" & PadHex(r5a, 4) & PadHex(r5b, 4) & PadHex(r5c, 4))

End Function

Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    'From: http://www.tek-tips.com/faqs.cfm?fid=5031
    ' X is the value you want to round
    ' Factor is the multiple to which you want to round
        Floor = Int(X / Factor) * Factor
End Function

Public Function RandomBetween(ByVal StartRange As Long, ByVal EndRange As Long) As Long
    'Based on https://msdn.microsoft.com/en-us/library/f7s023d2(v=vs.90).aspx
    '         randomValue = CInt(Math.Floor((upperbound - lowerbound + 1) * Rnd())) + lowerbound
        RandomBetween = CLng(Floor((EndRange - StartRange + 1) * Rnd())) + StartRange
End Function

Public Function PadLeft(text As Variant, totalLength As Integer, padCharacter As String) As String
    'Based on https://stackoverflow.com/questions/12060347/any-method-equivalent-to-padleft-padright
    ' with a little more checking of inputs

    Dim s As String
    Dim inputLength As Integer
    s = CStr(text)
    inputLength = Len(s)

    If padCharacter = "" Then
        padCharacter = " "
    ElseIf Len(padCharacter) > 1 Then
        padCharacter = Left(padCharacter, 1)
    End If

    If inputLength < totalLength Then
        PadLeft = String(totalLength - inputLength, padCharacter) & s
    Else
        PadLeft = s
    End If

End Function

Public Function PadHex(number As Long, length As Integer) As String
    PadHex = PadLeft(Hex(number), 4, "0")
End Function

recently ran into problems using the CreateObject("Scriptlet.TypeLib") method as well. Permission denied? — Perposterer, Jul 27 '17 at 19:47

Martin Carlsson · Accepted Answer · 2019-04-10 09:43:30Z

I created a VBA function that works both on mac and windows:

https://github.com/Martin-Carlsson/Business-Intelligence-Goodies/blob/master/Excel/GenerateGiud/GenerateGiud.bas

'Generates a guid, works on both mac and windows 
Function Guid() As String
    Guid = RandomHex(3) + "-" + _
        RandomHex(2) + "-" + _
        RandomHex(2) + "-" + _
        RandomHex(2) + "-" + _
        RandomHex(6)
End Function

'From: https://www.mrexcel.com/forum/excel-questions/301472-need-help-generate-hexadecimal-codes-randomly.html#post1479527
Private Function RandomHex(lngCharLength As Long)
    Dim i As Long
    Randomize
    For i = 1 To lngCharLength
        RandomHex = RandomHex & Right$("0" & Hex(Rnd() * 256), 2)
    Next
End Function

Sancarn · Accepted Answer · 2021-05-21 10:48:50Z

This is based on a javascript implementation.

Private Function getGUID() As String
  getGUID = "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx"
  getGUID = Replace(getGUID, "y", Hex(Rnd() And &H3 Or &H8))
  Dim i As Long: For i = 1 To 30
    getGUID = Replace(getGUID, "x", Hex$(CLng(Rnd() * 15.9999)), 1, 1)
  Next
End Function

KyloRen · Accepted Answer · 2017-07-17 02:13:24Z

If you are inserting records into a database you can use this way to make a GUID.

It is probably the most simplest and easiest way to implement as you don't need a complex VBA function as you use the built in SQL function.

The statement uses NewID(),

The syntax is as follows,

INSERT INTO table_name (ID,Column1,Column2,Column3)
VALUES (NewID(),value1,value2,value3)

In VBA syntax it is as follows,

strSql = "INSERT INTO table_name " _
       & "(ID,Column1,Column2,Column3) " _
       & "VALUES (NewID(),value1,value2,value3)"

And if you need to concatenate values, just treat it as a string and concatenate as you would normally for a SQL statement,

strSql = "INSERT INTO table_name " _
       & "(ID,Column1,Column2,Column3) " _
       & "VALUES (" & "NewID()" & "," & "value1" & "," & "value2" & "," & "value3" & ")"

This is a great method, but not if you need to use the guid within VBA first, before uploading (I.E inserting the same GUID across multiple tables) — Brendan Gooden, Jul 16 '17 at 21:55
@BrendanGooden, I see now. The OP is not inserting into a database. I will update my answer to show that. Thanks. — KyloRen, Jul 17 '17 at 2:12

BdR · Accepted Answer · 2021-04-06 14:12:40Z

For generating random guids using just the Rnd() function, not using any libraries or APIs, the simplest I can think of is this:

' UUID Version 4 (random)
Function GetUUID4()

    Dim guid As String
    Dim i As Integer
    Dim r As Integer
    
    guid = ""
    Randomize
    
    For i = 0 To 31
        ' random digit 0..15
        r = Rnd() * 15

        ' add dash separators
        If (i = 8) Or (i = 12) Or (i = 16) Or (i = 20) Then guid = guid & "-"

        ' uuid4 version info in 12th and 16th digits
        If (i = 12) Then r = 4
        If (i = 16) Then r = (r And 3 Or 8)

        ' add as hex digit
        guid = guid & Hex(r)
    Next i

    GetUUID4 = guid
End Function

Echilon · Accepted Answer · 2016-11-29 09:53:02Z

Function funGetGuid() As String

    Const URL As String = "http://www.guidgen.com/"
    Const strMask As String = "value="

    Dim l As Long
    Dim txt As String

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .send
        txt = .responseText
    End With

    Do
        l = InStr(l + 1, txt, strMask)
        If l = 0 Then Exit Do
        funGetGuid = Mid$(txt, l + Len(strMask) + 1, 36)
    Loop

End Function

In my opinion making a remote request just to generate a GUID is not an appropriate method. — ajshort, Nov 5 '15 at 21:49

current community

your communities

more stack exchange communities

How can I generate GUIDs in Excel?

13 Answers 13

Not the answer you're looking for? Browse other questions tagged vba excel guid or ask your own question.

Linked

Hot Network Questions

13 Answers 13

Not the answer you're looking for? Browse other questions tagged vba excel guid or ask your own question.

Linked

Related