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
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
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
Hi Blanka
Thanks for your generous feedback
Glad to know it is working for you.
Cheers
Sunny