vba 如何在 Excel 中生成 GUID？

Question

提问by abw333

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:

我有一个 excel 文件，每行一个订单，我希望每个订单都有一个唯一的标识符，所以会有一个唯一 ID 列。每次填充一行时，我都希望 Excel 自动为我填充唯一 ID 列。我做了一些研究，并指出了 GUID 的方向。我找到了以下代码：

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.

但我不确定如何实现它。任何帮助将不胜感激。先感谢您。

Answer 1

采纳答案by NekojiruSou

The following Excel expression evaluates to a V4 GUID:

以下 Excel 表达式的计算结果为 V4 GUID：

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

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

请注意，根据 RFC 4122 第 4.4 节，第三组的第一个字符始终为 4，以表示 V4（生成的伪随机数）GUID/UUID。

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

另请注意，根据同一 RFC，第四组的第一个字符始终介于 8 和 B 之间。

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

标准免责声明：生成的 GUID/UUID 在加密方面不强。

Answer 2

回答by rchacko

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

我在 v.2013 excel vba 中使用了以下函数来创建 GUID 并且运行良好..

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

Answer 3

回答by Mathieu Guindon

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/

我知道这个问题得到了回答，但我认为有问题的代码应该看起来像这个页面上的内容：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:

尚未测试，但此代码似乎利用 Windows API 来获取其 GUID - 我会尝试将其放入公共模块并=GetGUId()在 Excel 单元格中键入以查看我会得到什么。如果它在 VB6 中工作，你很有可能在 VBA 中也工作：

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.

如果这段代码有效（我毫不怀疑），我认为每当函数被评估时，你都会得到一组新的 GUID，这意味着每次计算工作表时 - 例如，当你保存工作簿时。如果您需要 GUID 供以后使用，请确保复制粘贴特殊值……这有点可能。

Answer 4

回答by Erik A

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

基于使用Rnd()函数生成随机数的 VBA 方法，而不是基于外部 API 调用或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.

这本质上是 NekojiruSou 答案的 VBA 实现（它还生成一个 v4 GUID），并且具有相同的限制，但可以在 VBA 中工作并且可能更容易实现。

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

请注意，您可以省略最后一行以不在结果中返回破折号和花括号。

Answer 5

回答by Alekzander

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

我在这里找到了很好的解决方案：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

Answer 6

回答by Chake

Same same for germanExcel version:

德语Excel 版本相同：

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

Answer 7

回答by rchacko

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

由于 windows update 取出了“Scriptlet.TypeLib”，请尝试以下操作：

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.

如果您连接到 SQL Server 2008 或更高版本，请尝试改用 SQL NEWID() 函数。

Answer 8

回答by Martin Carlsson

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

我创建了一个适用于 mac 和 windows 的 VBA 函数：

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

Answer 9

回答by KyloRen

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

如果您将记录插入到数据库中，您可以使用这种方式来制作 GUID。

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

这可能是最简单和最简单的实现方式，因为您不需要复杂的VBA函数，因为您使用内置的 SQL 函数。

The statement uses NewID(),

该语句使用NewID(),

The syntax is as follows,

语法如下，

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

In VBAsyntax it is as follows,

在VBA语法上，如下所示，

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,

如果您需要连接值，只需将其视为字符串并像通常的 SQL 语句一样连接，

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

Answer 10

回答by mphase

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

我最近在一些 vba 代码中使用 CreateObject("Scriptlet.TypeLib") 遇到了问题。

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.

因此，基于 NekojiruSou excel 函数编写了以下内容，它应该可以在没有任何特定 excel 函数的情况下工作。这可用于在 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

vba 如何在 Excel 中生成 GUID？

提问by abw333

采纳答案by NekojiruSou

回答by rchacko

回答by Mathieu Guindon

回答by Erik A

回答by Alekzander

回答by Chake

回答by rchacko

回答by Martin Carlsson

回答by KyloRen

回答by mphase

相关推荐

最近更新

标签

vba 如何在 Excel 中生成 GUID？

提问by abw333

采纳答案by NekojiruSou

回答by rchacko

回答by Mathieu Guindon

回答by Erik A

回答by Alekzander

回答by Chake

回答by rchacko

回答by Martin Carlsson

回答by KyloRen

回答by mphase

相关推荐

解析 CSV，忽略 VBA 中字符串文字中的逗号？

vba 如何将新记录添加到子表单

Excel VBA：从命令按钮调用 userForm 时不显示 ComboBox.Rowsource 值的动态范围

vba 一个工作簿中的宏可以更改另一个工作簿吗？

相关推荐

最近更新

标签