Is there any way in which I can generate a unique number in code ? I had an idea of using system time for that, but eventually could not implement it.
2
-
how many digit u need ?Eric K.– Eric K.2015-12-04 15:08:43 +00:00Commented Dec 4, 2015 at 15:08
-
What's the context? Is this code that generates unique values until done? Or does it run on a schedule/intermittently and needs to generate unique values anytime it is run? If it runs continuously until done, for example, just generate a variable and increment by 1 every time you need a new one. Could you use a hidden worksheet and use a particular cell that you increment every time you need a new number?user3476534– user34765342015-12-04 15:49:28 +00:00Commented Dec 4, 2015 at 15:49
Add a comment
|
2 Answers
You can use the Now() then format the output to a number:
Sub unique()
Dim t As Date
t = Now()
Range("A1").NumberFormat = "@"
Range("A1") = CStr(Format(t, "yyyymmddhhMMss"))
End Sub
This would be unique.
As @Vasily pointed out, without formatting the cell as string and placing the number as a sting the value gets truncated to scientific notation.
4 Comments
Vasily
I think that better is
Cstr(Format(now(), "yyyymmddhhMMssms"))
Scott Craner
@Vasily The OP clearly asked for a number. The
Format(now(), "yyyymmddhhMMssms") is a string and the Cstr() wrapper is not needed.Vasily
I mean that if you will inrease range of digits for unique identifyer
CDbl with format "yyyymmddhhMMssms", it will return a number+16, VBA doesn't has bigint (e.g. as in SQL Server), that is why my proposal was to use string instead of number. Does not matter number or string in case if the identifyer is unique.Scott Craner
@Vasily Ah, that makes sense. You are correct. Without formatting the cell as string and placing the number as a string it truncates the number in scientific format. I edited the answer.
especially for such cases the GUID (Global Unique IDentifyer) was invented. It may be a little bit oversized ... but just that you have seen it:
Option Explicit
Public Type TYP_GUID
bytes(15) As Byte
End Type
Public Declare Function CoCreateGuid Lib "OLE32.dll" _
(guid As TYP_GUID) As Long
Public Function newGUID() As TYP_GUID
Dim uGUID As TYP_GUID
CoCreateGuid uGUID
newGUID = uGUID
End Function
whenever you call newGUID() you will become a value that should be really unique in world. You can try and call it as often as you want ... you will never get the same value a second time.
it's also possible to convert such GUID's to string:
Option Explicit
Public Type TYP_GUID
bytes(15) As Byte
End Type
Public Declare Function CoCreateGuid Lib "OLE32.dll" _
(guid As TYP_GUID) As Long
Public Declare Function StringFromGUID2 Lib "OLE32.dll" _
(guid As TYP_GUID, _
ByVal lpszString As String, _
ByVal iMax As Long) As Long
Public Function newGUID() As TYP_GUID
Dim uGUID As TYP_GUID
CoCreateGuid uGUID
newGUID = uGUID
End Function
Public Function newGUID_String() As String
Dim sBuffer As String
Dim lResult As Long
sBuffer = VBA.Space(78)
lResult = StringFromGUID2(newGUID, sBuffer, Len(sBuffer))
newGUID_String = Left$(StrConv(sBuffer, vbFromUnicode), lResult - 1)
End Function
