Как я могу генерировать GUID в Excel?

У меня есть файл excel с одним порядком в каждой строке, и я хочу, чтобы каждый заказ имел уникальный идентификатор, поэтому будет столбец Unique ID. Каждый раз, когда я заполняю строку, я хочу, чтобы Excel автоматически заполнял столбец "Уникальный идентификатор" для меня. Я сделал некоторые исследования и был указан в направлении 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

но я не уверен, как это реализовать. Любая помощь будет принята с благодарностью. Заранее благодарю вас.

Ответы

Ответ 1

В следующем выражении Excel используется идентификатор GUID V4:

=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 (в зависимости от настроек локали/десятичных и разделителей списков) -

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

Обратите внимание, что первый символ третьей группы всегда равен 4, чтобы обозначить V4 (генерируемый псевдослучайным числом) GUID/UUID для RFC 4122, раздел 4.4.

Также обратите внимание, что первый символ четвертой группы всегда находится между 8 и B в одном RFC.

Стандартный отказ от ответственности: результирующие GUID/UUID не являются криптографически сильными.

Ответ 2

Я использовал следующую функцию в v.2013 excel vba для создания GUID и хорошо работает.

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

Ответ 3

Я знаю, что на этот вопрос ответил, но я думаю, что этот код должен выглядеть примерно так: http://snipplr.com/view/37940/

Не тестировались, но этот код, похоже, использует 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

Спасибо Гус Молина!

Если этот код работает (что я не сомневаюсь), я думаю, что вы получите новый набор GUID всякий раз, когда функция будет оцениваться, что означает, что каждый раз, когда лист вычисляется - когда вы сохраняете книгу, для пример. Обязательно скопируйте-pastespecial-values, если вам нужен GUID для последующего использования... что несколько вероятно.

Ответ 4

Я нашел довольно решение здесь:
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

Ответ 5

То же самое для немецкой версии 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))

Ответ 6

Подход VBA, основанный на генерации случайных чисел с использованием функции Rnd(), а не на внешних вызовах 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

Это, по сути, реализация VBA ответа NekojiruSou (он также генерирует GUID v4) и имеет те же ограничения, но будет работать в VBA и может быть проще реализовать.

Обратите внимание, что вы можете опустить последнюю строку, чтобы не возвращать тире и фигурные скобки в результате.

Ответ 7

Поскольку обновление Windows вывело "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

С другой стороны,

если вы подключаетесь к SQL Server 2008 или выше, попробуйте вместо этого использовать функцию SQL NEWID().

Ответ 8

Если вы вставляете записи в базу данных, вы можете использовать этот способ для создания GUID.

Это, вероятно, самый простой и простой способ реализовать, поскольку вам не нужна сложная функция VBA, поскольку вы используете встроенную функцию SQL.

В заявлении используются NewID(),

Синтаксис выглядит следующим образом:

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

В синтаксисе VBA он выглядит следующим образом:

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

И если вам нужно конкатенировать значения, просто рассматривайте его как строку и конкатенацию, как обычно для оператора SQL,

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

Ответ 9

Недавно я столкнулся с проблемами с использованием CreateObject ( "Scriptlet.TypeLib" ) в некотором коде vba.

Итак, на основе функций excel NekojiruSou написано следующее, которое должно работать без каких-либо особых функций 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

Ответ 10

Я создал функцию VBA, которая работает как на Mac, так и на Windows:

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

Ответ 11

Function funGetGuid() As String

    Const URL As String = "http://www.guidgen.com/"
    Const strMask As String = "value="

    Dim l As Long
    Dim txt As String

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .send
        txt = .responseText
    End With

    Do
        l = InStr(l + 1, txt, strMask)
        If l = 0 Then Exit Do
        funGetGuid = Mid$(txt, l + Len(strMask) + 1, 36)
    Loop

End Function