How can I generate GUIDs in Excel?

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 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?

Warning: The above code will add random garbage to the end of the string: stackoverflow.com/questions/19556268/…

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.

14 Answers 14

The following Excel expression evaluates to a V4 GUID:

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

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.

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))

@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.

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 

Nice piece of code. To eliminate the hyphens you could make it GetGUID = Replace(Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36),"-",vbNullString)

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/…

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

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.

@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.

@MathieuGuindon, sir, is there any probability of collision while GUID created using CoCreateGuid method?

@KamalBharakhda it's probably what the Scripting library is invoking under the hood anyway, and it's OS-level. I doubt there's any risk of collisions there.

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 

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 = "" 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.

Private Function getGUID() As String Call Randomize 'Ensure random GUID generated 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$(Int(Rnd() * 16)), 1, 1) Next End Function 

@AliSafari Thanks for the information. You were absolutely correct, Something I didn't catch was that clng doesn't round down like in other languages! Fixed now :)

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.

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 

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 .

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

'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 

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