Algorithm for form numbers

[XP]

Using Office 2007 with Win XP;

I have a form designed in Excel for use by many different persons over a LAN.

In the past I have used a formula like the following to generate a unique form
number: =NOW()*10^7

This number is then formatted into a 12 digit value like: 3984-8374-2472
An auto open macro converts this formula to a value whenever a user opens a
form and it works very well. We have not had any duplicate form numbers
generated because it is clocked down to such a small fraction of a second.

The problem is, this new form being developed can have no more than 9
digits. If I alter my algorithm to say: =Now()*10^4
I find it very easy to generate two or even three forms having the same form
number, which will not work, since each form must never repeat a number.

Is there a chance anyone out there can help me out with an algorithm that
will work? BTW, the form can also use alphabetic characters. I'd really
rather NOT rely upon a centralized counter file or incrementing MS-Access
table if possible.

Thanks much in advance for any help/suggestions.

 

[JE McGimpsey]

I'd think that

Right(CLng(Date) & CLng(Time * 86400), 9)

ought to last you for a while, as long as nobody generates a form at
exactly the same second.

 

[Gary''s Student]

Why not a simple sequential:

000000000
000000001
000000002

etc.

 

[XP]

Hi,

Using the following formula, exactly:

=RIGHT(CLng(Date)&CLng(Time*86400),9)

I get "#NAME" error...yet I don't see anything wrong...any ideas?

 

[JE McGimpsey]

Since you posted in the .programming group, I assumed you were looking
for a VBA solution.

An XL formula equivalent would be

=RIGHT(TEXT(TODAY(),"00000")&TEXT(MOD(NOW(),1)*86400,"00000"),9)

though that introduces a possible problem if users are using different
date systems (i.e, 1900 vs. 1904), which could potentially cause
duplication after 4 years and 1 day.

 

[JE McGimpsey]

Given that she doesn't want to rely on a centralized counter file, it
seems to me that simple sequentials would be difficult to implement.

 

[JE McGimpsey]

Really should be:

Right(CLng(Date) & Format(Time * 86400, "00000"), 9)