Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
''''''''''''''''''''''''''''''''''''
' http://hocexcel.online
' source for createGUID: http://www.cpearson.com/excel/CreateGUID.aspx
' source for genGUID: http://stackoverflow.com/questions/7031347/how-can-i-generate-guids-in-excel
''''''''''''''''''''''''''''''''''''
Option Explicit
''''''''''''''''''''''''''''''''''''
Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long
''''''''''''''''''''''''''''''''''''
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
''''''''''''''''''''''''''''''''''''
Public Function genGUID()
genGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)
End Function
''''''''''''''''''''''''''''''''''''
Function genID() As String
genID = Format(Now, "yymmddhhmmss")
End Function
''''''''''''''''''''''''''''''''''''
Public Function CreateGUID() As String
Dim G As GUID
With G
If (CoCreateGuid(G) = 0) Then
CreateGUID = _
String$(8 - Len(Hex$(.Data1)), "0") & Hex$(.Data1) & _
String$(4 - Len(Hex$(.Data2)), "0") & Hex$(.Data2) & _
String$(4 - Len(Hex$(.Data3)), "0") & Hex$(.Data3) & _
IIf((.Data4(0) < &H10), "0", "") & Hex$(.Data4(0)) & _
IIf((.Data4(1) < &H10), "0", "") & Hex$(.Data4(1)) & _
IIf((.Data4(2) < &H10), "0", "") & Hex$(.Data4(2)) & _
IIf((.Data4(3) < &H10), "0", "") & Hex$(.Data4(3)) & _
IIf((.Data4(4) < &H10), "0", "") & Hex$(.Data4(4)) & _
IIf((.Data4(5) < &H10), "0", "") & Hex$(.Data4(5)) & _
IIf((.Data4(6) < &H10), "0", "") & Hex$(.Data4(6)) & _
IIf((.Data4(7) < &H10), "0", "") & Hex$(.Data4(7))
End If
End With
End Function
''''''''''''''''''''''''''''''''''''
Sub TickBenchmark()
Dim Start_gen As Long
Dim Finish_gen As Long
Dim Start_create As Long
Dim Finish_create As Long
'genGUID
Start_gen = GetTickCount()
Sheet1.Range("A1:A" & Sheet1.Range("H2").Value2).Formula = "=genGUID()"
Finish_gen = GetTickCount()
'createGUID
Start_create = GetTickCount()
Sheet1.Range("B1:B" & Sheet1.Range("H2").Value2).Formula = "=CreateGUID()"
Finish_create = GetTickCount()
MsgBox "(Col A) gen method : " & CStr((Finish_gen - Start_gen) / 1000) & vbNewLine & _
"(Col B) create method : " & CStr((Finish_create - Start_create) / 1000)
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.