Creating RQUIDs|VBA & Macros|Excel Forum|My Online Training Hub

I need help badly, I'm a beginner when it comes to VBA.

I have to create hundreds of RQUIDs almost daily. I have a macro (created by someone else) which worked beautifully until yesterday, when we had Excel updated. Now I'm getting Error '70' when my macro gets to the CreateObject line.

I searched Microsoft's website on how to work around the issue and they do show an example, but I have no idea how to incorporate it into my macro.

My original Macro:

-----------------------------------------

Sub GenerateGuids()
    Dim iGuids As Integer
    Dim iRow As Integer
   
    iGuids = InputBox("How Many Guids Do You Want?", "Guid Generator")
    If iGuids <= 100000000 Then
        iRow = 2
       
        Do While iRow <= iGuids
            Application.ActiveSheet.Cells(iRow, 1) = GenGuid()
            iRow = iRow + 1
        Loop
       
    ElseIf iGuids < 1 Then
        MsgBox ("You must generate at least 1.")
    Else
        MsgBox ("That is a lot of guids, the maximum I can generate is 100 million, and that's probably going to error before I am done.")
       
    End If
End Sub

Function GenGuid() As String
    Dim TypeLib As Object
    Dim guid As String
    Set TypeLib = CreateObject("Scriptlet.TypeLib")
    guid = TypeLib.guid
   
    guid = Replace(guid, "{", "")
    guid = Replace(guid, "}", "")
    'Guid = Replace(Guid, "-", "")
    GenGuid = guid
End Function

-----------------------------------------

This is what Microsoft says:

Workaround

The preferred method is to change the code to use Windows API CoCreateGuid instead of “CreateObject(“Scriptlet.TypeLib”).Guid”

Example of CreateObject error '70':

Dim strGuid As String

StrGuid = Left$(CreateObject("Scriptlet.TypeLib").Guid, 38)

Example of CoCreateGuid(…): Vba7 Win64 Office 64-bit

‘ No VT_GUID available so must declare type GUID

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

                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

CreateGuidString = strGuid

                                End If

                End if

End Function

------------------------------

I would be very grateful if someone could combine these two macros into a workable one.

Thank you,

Blanka