在 Excel 中，我尝试使用 vba UDF 在表中生成 UUID 列，但我只希望它在第一次调用时运行，以便它生成的 UUID 永远不会改变。
制作 UUID 很容易（从https://nolongerset.com/createguid/修改-编辑：添加了对单元格 A1 的检查，以便与下面@MGonet 的答案正常工作）：
'These Declare lines go at the very top of the code module
#If VBA7 Then
Private Declare PtrSafe Function CoCreateGuid Lib "ole32" (id As Any) As Long
#Else
Private Declare Function CoCreateGuid Lib "ole32" (id As Any) As Long
#End If
' ----------------------------------------------------------------
' Procedure : CreateGUID
' Author : Dan ([email protected])
' Source : http://allapi.mentalis.org/apilist/CDB74B0DFA5C75B7C6AFE60D3295A96F.html
' Adapted by : Mike Wolfe
' Republished: https://nolongerset.com/createguid/
' Date : 8/5/2022
' ----------------------------------------------------------------
Private Function CreateGUID() As String
'Application.OnRepeat Text:="Paste Next Date", _
'Procedure:="Application.thisCell.Text"
If (Application.thisCell.Address <> "$A$1") Then
Const S_OK As Long = 0
Dim id(0 To 15) As Byte
Dim Cnt As Long, GUID As String
If CoCreateGuid(id(0)) = S_OK Then
For Cnt = 0 To 15
CreateGUID = CreateGUID & IIf(id(Cnt) < 16, "0", "") + Hex$(id(Cnt))
Next Cnt
CreateGUID = LCase(Left$(CreateGUID, 8) & "-" & _
Mid$(CreateGUID, 9, 4) & "-" & _
Mid$(CreateGUID, 13, 4) & "-" & _
Mid$(CreateGUID, 17, 4) & "-" & _
Right$(CreateGUID, 12))
Else
MsgBox "Error while creating GUID!"
End If
Else
CreateGUID = Application.thisCell.Text
End If
End Function
我想要的是将其包装在一个If语句中以确定该函数是否已经被计算过。经过一番折腾后我尝试过：
Public Function CreateGUID() As String
If (Application.thisCell.Characters = "") Then
'Generate UUID
Else
CreateGUID = Application.thisCell.Value
End If
End Function
然而这似乎不起作用。我还尝试使用application.onrepeat进行修改，但这似乎也不起作用。是否有其他方法来确定公式是否已被评估？
