Generate a unique number using a macro

[npravin.1804]

Hi,


I have an excel form which I use on daily basis. I need to develop a macro which generate an unique numer for the form everytime I open the sheet. This unique numbers should include the date and some other information in the form.For e.g. if I open that sheet today then it shuold give me a unique number as 20120522LnTrandomuniquenumber.

It can be seen as Date-20120522 - location-LnT and Random unique number

 

[Luke M]

Technically, you can't have something be truly random and guarantee that it will be unique. However, if you just need to always generate a unique number, why not include a time stamp, like:

[pre]

MyNumber = Format(Now, "yyyymmdd-hhmmss")

[/pre]
If I had run this at this moment, this would create a string of "20120522-085337"

 

[SirJB7]

Hi, npravin.1854!


Completing Luke M's idea, the instruction should be something like this:

' somewhere at top of the procedure with other declaration statements

Dim MaxNum as long, LnT as string

' somewhere after the declaration statements

MaxNum = 32768

Randomize()

' somewhere before assigning it: I don't know what value it should have

LnT = "???"

' where it corresponds

MyNumber = Format(Now, "yyyymmdd-hhmmss") & LnT & Format(Int(Rnd()*Max)), "00000")


Regards!

 

[npravin.1804]

Thanks Luke and JB for your update, but the story is like that I've the form ready with me with Date and location mentioned in it. So I just need a macro which pick these values and a random number to give it an unque number for the form. I don't mind if it consider the time as well in that string.

 

[SirJB7]

Hi, npravin.1804!

Just change "yyyymmdd-hhmmss" in my previous post by "yyyymmdd". If needed change "Now" by the related control value, if any (let's say txtDate.value if a control text box named txtDate).

Regards!