I need a function to add a GUID to cells in excel. I found this previous question on stackoverflow, but it is not working. It suggests the following function:
=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,6553??5),4),"-"
,DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151??),4),"-"
,DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8))
I have been unable to get the concatenate method to work, and so tried it using the "&" figure to concatenate anyway. That seemed to work, but then I get an inexplicable error on the second DEX2HEX
block: DEC2HEX(RANDBETWEEN(0,6553??5),4)
. The Excel formula evaluator says it's invalid, but I can't seem to figure out why at all. Any ideas?
The formula for Polish version:
=ZLACZ.TEKSTY(
DZIES.NA.SZESN(LOS.ZAKR(0;4294967295);8);"-";
DZIES.NA.SZESN(LOS.ZAKR(0;42949);4);"-";
DZIES.NA.SZESN(LOS.ZAKR(0;42949);4);"-";
DZIES.NA.SZESN(LOS.ZAKR(0;42949);4);"-";
DZIES.NA.SZESN(LOS.ZAKR(0;4294967295);8);
DZIES.NA.SZESN(LOS.ZAKR(0;42949);4)
)
for me it is correct, in Excel spanish
=CONCATENAR(
DEC.A.HEX(ALEATORIO.ENTRE(0,4294967295),8),"-",
DEC.A.HEX(ALEATORIO.ENTRE(0,65535),4),"-",
DEC.A.HEX(ALEATORIO.ENTRE(16384,20479),4),"-",
DEC.A.HEX(ALEATORIO.ENTRE(32768,49151),4),"-",
DEC.A.HEX(ALEATORIO.ENTRE(0,65535),4),
DEC.A.HEX(ALEATORIO.ENTRE(0,4294967295),8)
)
Italian version:
=CONCATENA(
DECIMALE.HEX(CASUALE.TRA(0;4294967295);8);"-";
DECIMALE.HEX(CASUALE.TRA(0;42949);4);"-";
DECIMALE.HEX(CASUALE.TRA(0;42949);4);"-";
DECIMALE.HEX(CASUALE.TRA(0;42949);4);"-";
DECIMALE.HEX(CASUALE.TRA(0;4294967295);8);
DECIMALE.HEX(CASUALE.TRA(0;42949);4))
This is not a problem with the function at all.
It took me a bit of digging, but the problem is in copying and pasting. Try copying this: RANDBETWEEN(0,6553??5)
string, posted in your original question, and paste it into a Hex Editor, then you'll see that there are actually two null characters in the 65535:
00000000 52 41 4E 44 42 45 54 57 45 45 4E 28 30 2C 36 35 RANDBETWEEN(0,65
00000010 35 33 00 00 35 29 53?..?5)
The formula for German Excel:
=KLEIN(
VERKETTEN(
DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;8));8);"-";
DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;4));4);"-";"4";
DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;3));3);"-";
DEZINHEX(ZUFALLSBEREICH(8;11));
DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;3));3);"-";
DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;8));8);
DEZINHEX(ZUFALLSBEREICH(0;POTENZ(16;4));
)
)
ESP:
=CONCATENAR(
DEC.A.HEX(ALEATORIO.ENTRE(0;4294967295);8);"-";
DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4);"-";
DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4);"-";
DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4);"-";
DEC.A.HEX(ALEATORIO.ENTRE(0;4294967295);8);
DEC.A.HEX(ALEATORIO.ENTRE(0;42949);4)
)
Ken Thompson is right! - for me also this way works (excel 2016), but type definition GUID_TYPE is skipped, so full scripting is:
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
Function CreateGuidString(Optional IncludeHyphens As Boolean = True, Optional IncludeBraces As Boolean = False)
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
' remove them from the GUID
If Not IncludeHyphens Then
strGuid = Replace(strGuid, "-", vbNullString, Compare:=vbTextCompare)
End If
' If IncludeBraces is switched from the default False to True,
' leave those curly braces be!
If Not IncludeBraces Then
strGuid = Replace(strGuid, "{", vbNullString, Compare:=vbTextCompare)
strGuid = Replace(strGuid, "}", vbNullString, Compare:=vbTextCompare)
End If
CreateGuidString = strGuid
End If
End If
End Function
After trying a number of options and running into various issue with newer versions of Excel (2016) I came across this post from MS that worked like a charm. I enhanced it bit using some code from a post by danwagner.co
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
Function CreateGuidString(Optional IncludeHyphens As Boolean = True, Optional IncludeBraces As Boolean = False)
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
' remove them from the GUID
If Not IncludeHyphens Then
strGuid = Replace(strGuid, "-", vbNullString, Compare:=vbTextCompare)
End If
' If IncludeBraces is switched from the default False to True,
' leave those curly braces be!
If Not IncludeBraces Then
strGuid = Replace(strGuid, "{", vbNullString, Compare:=vbTextCompare)
strGuid = Replace(strGuid, "}", vbNullString, Compare:=vbTextCompare)
End If
CreateGuidString = strGuid
End If
End If
End Function
Public Sub TestCreateGUID()
Dim Guid As String
Guid = CreateGuidString() '<~ default
Debug.Print Guid
End Sub
There are additional options in the original MS post found here: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2010/guid-run-time-error-70-permission-denied/c9ee4076-98af-4032-bc87-40ad7aa7cb38
The formula for French Excel:
=CONCATENER(
DECHEX(ALEA.ENTRE.BORNES(0;4294967295);8);"-";
DECHEX(ALEA.ENTRE.BORNES(0;42949);4);"-";
DECHEX(ALEA.ENTRE.BORNES(0;42949);4);"-";
DECHEX(ALEA.ENTRE.BORNES(0;42949);4);"-";
DECHEX(ALEA.ENTRE.BORNES(0;4294967295);8);
DECHEX(ALEA.ENTRE.BORNES(0;42949);4))
As noted by Josh M, this does not provide a compliant GUID however, but this works well for my current need.
=CONCATENATE(
DEC2HEX(RANDBETWEEN(0;4294967295);8);"-";
DEC2HEX(RANDBETWEEN(0;42949);4);"-";
DEC2HEX(RANDBETWEEN(0;42949);4);"-";
DEC2HEX(RANDBETWEEN(0;42949);4);"-";
DEC2HEX(RANDBETWEEN(0;4294967295);8);
DEC2HEX(RANDBETWEEN(0;42949);4)
)
The formula for Dutch Excel:
=KLEINE.LETTERS(
TEKST.SAMENVOEGEN(
DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;8));8);"-";
DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;4));4);"-";"4";
DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;3));3);"-";
DEC.N.HEX(ASELECTTUSSEN(8;11));
DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;3));3);"-";
DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;8));8);
DEC.N.HEX(ASELECTTUSSEN(0;MACHT(16;4));4)
)
)
As of modern version of Excel, there's the syntax with commas, not semicolons. I'm posting this answer for convenience of others so they don't have to replace the strings- We're all lazy... hrmp... human, right?
=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,42949),4))
Or, if you like me dislike when a guid screams and shouts and you, we can go lower-cased like this.
=LOWER(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,42949),4)))
=LOWER(
CONCATENATE(
DEC2HEX(RANDBETWEEN(0,POWER(16,8)),8), "-",
DEC2HEX(RANDBETWEEN(0,POWER(16,4)),4),"-","4",
DEC2HEX(RANDBETWEEN(0,POWER(16,3)),3),"-",
DEC2HEX(RANDBETWEEN(8,11)),
DEC2HEX(RANDBETWEEN(0,POWER(16,3)),3),"-",
DEC2HEX(RANDBETWEEN(0,POWER(16,8)),8),
DEC2HEX(RANDBETWEEN(0,POWER(16,4)),4)
)
)
Taken from git @mobilitymaster.
Source: Stackoverflow.com