The Random Number Feature

Hey:

I have a User Form that a co-worker uses to enter new contacts onto a spreadsheet that is also used to keep track of his phone calls, e-mails and other encounters with said contacts.

As the list grew, I decided to give all the names "unique" ID by using the Rand function just before the User Form finishes plunking the Contact's info. onto the spreahsheet. This made for easy coding on my part, and less steps on my co-worker's part in differentiating Phone Call encounters between 2 different Dr. John Rebenacs from Phila, etc.

So I set to using Excel's Rand function...

Here's the code I'm using:

Static Function NonStaticRand()

Application.Volatile False
NonStaticRand = Int(Rnd * 1000)
ActiveCell = NonStaticRand

Call NextSubPr

End Function

Problem is, since a lot of these Contact records are being added and deleted, Excel's Rand function is so Random, it can repeat the same #'s not just twice, but sometime thrice.

Hardly "unique" IDs.

I'd do a simple Worksheet formula, if A3 = 1 and there was a name in B4:
=IF(B4=""," ",A3+1)
giving a value of 2, and so on down the column.

Problem with that is when one presses my "Sort" Button I get Value Errors since A2 is the ID# header (ie ID# + 1 = ??)...

So what can I do to make this whole ID# thingy work...?

Any ideas... ?

Thanks...

--Dodd

Probably the biggest reason why you are getting duplicate values with your rnd is by forcing it to Int and only multiplying by 1000, you are limiting yourself to only 1000 possible numbers. Try Int(Rnd * 10000000) for a much smaller chance of duplicate values.

Probably the biggest reason why you are getting duplicate values with your rnd is by forcing it to Int and only multiplying by 1000, you are limiting yourself to only 1000 possible numbers. Try Int(Rnd * 10000000) for a much smaller chance of duplicate values.


I'll try that. Thanks, man...

A simple incrementing LONG value like the autonumber field in Access would *guarantee* uniqueness and would probably be just as simple to implement.