#If Win64 Then
Private Declare PtrSafe Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long
#Else
Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long
#End If
Private Type GUID
' This function sourced from http://support.microsoft.com/kb/176790
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
Public Function CreateGUID(G As GUID) As String
' This function sourced from http://support.microsoft.com/kb/176790
'Dim G As GUID
With G
If (CoCreateGuid(G) = 0) Then
CreateGUID = _
String$(8 - Len(Hex$(.Data1)), "0") & Hex$(.Data1) & _
String$(4 - Len(Hex$(.Data2)), "0") & Hex$(.Data2) & _
String$(4 - Len(Hex$(.Data3)), "0") & Hex$(.Data3) & _
IIf((.Data4(0) < &H10), "0", "") & Hex$(.Data4(0)) & _
IIf((.Data4(1) < &H10), "0", "") & Hex$(.Data4(1)) & _
IIf((.Data4(2) < &H10), "0", "") & Hex$(.Data4(2)) & _
IIf((.Data4(3) < &H10), "0", "") & Hex$(.Data4(3)) & _
IIf((.Data4(4) < &H10), "0", "") & Hex$(.Data4(4)) & _
IIf((.Data4(5) < &H10), "0", "") & Hex$(.Data4(5)) & _
IIf((.Data4(6) < &H10), "0", "") & Hex$(.Data4(6)) & _
IIf((.Data4(7) < &H10), "0", "") & Hex$(.Data4(7))
End If
End With
End Function
Sub FillGUID()
Dim range_populate As Range
Dim range_Area As Range
Dim l_Rows As Long
Dim l_Columns As Long
Dim l_Row As Long
Dim l_Column As Long
Dim TempArray() As String
Dim G As GUID
If Application.Selection.Count > 1 Then
'KNOWN ISSUE - will only work up to last 'used' cell, so won't work at bottom right of sheet
Set range_populate = Application.Selection.SpecialCells(xlCellTypeBlanks)
For Each range_Area In range_populate.Areas
l_Rows = range_Area.Rows.Count
l_Columns = range_Area.Columns.Count
ReDim TempArray(1 To l_Rows, 1 To l_Columns)
Application.ScreenUpdating = False
For l_Row = 1 To l_Rows
For l_Column = 1 To l_Columns
TempArray(l_Row, l_Column) = CreateGUID(G)
Next l_Column
Next l_Row
range_Area.Value = TempArray
Application.ScreenUpdating = True
Next range_Area
ElseIf Len(Selection.Value) = 0 Then Application.Selection.Value = CreateGUID(G)
End If
End Sub
Sub GUID_Reformat()
Dim rng_Row As Range
Dim rng_Col As Range
Dim rng_Sel As Range
Dim rng_Area As Range
Dim dbl_Rows As Double
Dim dbl_Cols As Double
Dim idx_Row As Double
Dim idx_Col As Double
Dim arr_Temp() As Variant
Set rng_Sel = Application.Selection
For Each rng_Area In rng_Sel.Areas
dbl_Rows = rng_Area.Rows.Count
dbl_Cols = rng_Area.Columns.Count
ReDim arr_Temp(1 To dbl_Rows, 1 To dbl_Cols)
If dbl_Rows = 1 And dbl_Cols = 1 Then
arr_Temp(1, 1) = rng_Area(1, 1)
Else
arr_Temp = rng_Area
End If
For idx_Row = 1 To dbl_Rows
For idx_Col = 1 To dbl_Cols
arr_Temp(idx_Row, idx_Col) = "{" & Mid(arr_Temp(idx_Row, idx_Col), 1, 8) _
& "-" & Mid(arr_Temp(idx_Row, idx_Col), 9, 4) _
& "-" & Mid(arr_Temp(idx_Row, idx_Col), 13, 4) _
& "-" & Mid(arr_Temp(idx_Row, idx_Col), 17, 4) _
& "-" & Mid(arr_Temp(idx_Row, idx_Col), 21, 12) & "}"
Next idx_Col
Next idx_Row
rng_Area.Value = arr_Temp
Next rng_Area
End Sub
section
References: