Create a GUID in entire column

okay... well i hate waiting so I started to solve this myself

here's what i put in the vba code window:

Declare Function CoCreateGuid Lib "ole32" (ByRef GUID As Byte) As Long

Function MakeGUID()
  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))
  Next N
  MakeGUID = GUID
End Function

Sub makeAllGUIDS()
  Sheets("Sheet1").Select
  Range("A1").Select
  
  Do Until Selection.Offset(0, 1).Value = ""
    Selection.Value = MakeGUID()
    Selection.Offset(1, 0).Select
  Loop
  
  Range("B1").Select
    
End Sub

Im not sure I trust that GUID function to be unique though, which is a huge problem.

Hello tvainisi,

Here is how to correctly generate the GUID (Globally Unique Identifier) or CLSID (Class ID). Place this code in its own module in your VBA project.

'Written: October 12, 2009
'Author:  Leith Ross
'Summary: Function creates a GUID (aka CLSID) and returns it as a string.

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

Private Declare Function CoCreateGuid _
  Lib "ole32.dll" _
  (ByRef pGUID As GUID) As Long

Private Declare Function StringFromGUID2 _
  Lib "ole32.dll" _
    (ByRef rGUID As Any, _
     ByVal lpstrCLSID As Long, _
     ByVal cbMax As Long) As Long

Function CreateGUID() As String

  Dim b() As Byte
  Dim BuffSize As Long
  Dim RetVal As Long
  Dim MyGUID As GUID

    BuffSize = 40
    ReDim b(BuffSize * 2) As Byte
    
    RetVal = CoCreateGuid(MyGUID)
    RetVal = StringFromGUID2(MyGUID, VarPtr(b(0)), BuffSize)
    CreateGUID = Left$(b, RetVal - 1)

End Function

Because this is a UDF (User Defined Function) you can place it in cell to return a new GUID to the cell.

   A1 = CreateGUID()

Thanks for the assistance Leith.

Is there a way to not have the curly brackets around the GUID, or do I just need to use some string functions to remove them?

Hello tvainisi,

The GUID includes the curly braces. So use a Mid function to remove them. Change the last line in the macro to this...

CreateGUID = Mid(b, 2, RetVal - 3)

My apologies - the 2009 reference didn't even register. I will read the rules.

And thanks for the suggested function.