Forum

Notifications
Clear all

Creating RQUIDs

4 Posts
2 Users
0 Reactions
169 Views
(@blankab)
Posts: 63
Estimable Member
Topic starter
 

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

 
Posted : 02/03/2018 11:17 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Blanka

Not tested on a 64 bit Excel but should work. I have limited the number of GUID generation to 500,000 (Excel maximum rows is around 1 million only).

Change to suit your needs.

Hope this helps.

Sunny

 
Posted : 02/03/2018 11:09 pm
(@blankab)
Posts: 63
Estimable Member
Topic starter
 

Hi Sunny,

It works perfectly. You are awesome! You saved me hours of frustration.

Thank you, thank you, thank you. I can't thank you enough.

Blanka

 
Posted : 03/03/2018 10:53 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Blanka

Thanks for your generous feedback Laugh

Glad to know it is working for you.

Cheers

Sunny

 
Posted : 03/03/2018 7:22 pm
Share: