maudette
asked on
VBA Random Number
Hello,
I am trying to create a user defined function to assign a UID random number using concatenation and random number
functions for 10 people. the output in excel should look like this U########.
I am trying to create a user defined function to assign a UID random number using concatenation and random number
functions for 10 people. the output in excel should look like this U########.
I have made it more generic. Paste this code in a module
To call from Excel, simply type this formula
=RandNo("U",8)
I have made it generic so that you can test it with other alphabets with different sizes :)
Sid
Public Function RandNo(strChar As String, Numb As Long) As String
RandNo = strChar & Int(Rnd * (10 ^ Numb))
End Function
To call from Excel, simply type this formula
=RandNo("U",8)
I have made it generic so that you can test it with other alphabets with different sizes :)
Sid
ASKER
Thanks Sid! I have one question, I put this the way your suggested in the module and called it the same way. I need them to all have the same amount of numbers tied to the U, is there a way to do this?
They will not have the same numbers as Rnd will generate a random number everytime.
Sid
Sid
ASKER
Is there a way to have the same amount of numbers everytime? such as a random number between 99999999 and 10000000 with U. such as everytime it will be U and 8 numbers.
How are the 10 users connected to the same workbook? Is the workbook shared?
Sid
Sid
ASKER
it really doens't matter the number of users, what I am trying to do is create a unique ID for a set of students a unique identifier using VBA. I used 10 users as an example to say I need 10 unique ID's, with a U in front of it. Does that make sense?
Oh you mean like
U46870011
U29816544
U62269669
U64782118
U26379293
U27934205
U82980161
U82460212
U86261934
U79048001
Sid
U46870011
U29816544
U62269669
U64782118
U26379293
U27934205
U82980161
U82460212
U86261934
U79048001
Sid
ASKER
yes exactly!
maudette,
While it is highly unlikely, using a single UDF call per user leaves open the possibility of duplication. On that basis, I should think a macro that spits out a specified number of unique IDs, or a UDF returning such an array, would be a better fit.
Please clarify your requirements on such issues as the length of the string, the range of allowed numbers, whether to pad with leading zeroes, etc.
Patrick
While it is highly unlikely, using a single UDF call per user leaves open the possibility of duplication. On that basis, I should think a macro that spits out a specified number of unique IDs, or a UDF returning such an array, would be a better fit.
Please clarify your requirements on such issues as the length of the string, the range of allowed numbers, whether to pad with leading zeroes, etc.
Patrick
ASKER
Patrick,
I was tasked with creating a UDF by utilizing concat and RND functions to develop a Unique ID with a U infront and 8#'s attached such as the output example that Sid showed.
I was tasked with creating a UDF by utilizing concat and RND functions to develop a Unique ID with a U infront and 8#'s attached such as the output example that Sid showed.
This will give you 10 unique numbers.
Sid
Sub Sample()
Dim Ret As String
For i = 1 To 10
Debug.Print "U" & Format(Int(Rnd * 100000000), "00000000")
Next i
End Sub
Sid
If you wish, you may output these numbers to Excel Sheet.
Sid
Sid
ASKER
Sid, is this a UDF? How would I call this?
Ok where do you want the output?
Sid
Sid
ASKER
this is what I am using as a spreadsheet to create the UID... I need to be able to call a specific UID for the following people by creating a UDF that uses concat and RND that takes on the form U########. I then need to place them in two groups by birthday. Group 1 is anyone whose birthday between January 1 and June 30 of any year using if/then statement. Group 2 is anyone whose birthday is between July 1-Sept. 30 of any year using selectcase statement
Last Name First Name Birthday UID Project 1 team Project 2 team
Queen Victor 9/25/1973
Lame Patty 8/2/1982
Fence Noah 7/26/1986
King Joe 12/22/1973
Xtre Dean 11/25/1970
Sew Lane 12/20/1979
Due Paige 1/13/1971
Payne Jane 5/7/1984
Coe John 6/1/1975
Dean Adam 5/2/1983
Cute Sue 5/7/1978
Free Monica 4/3/1987
Last Name First Name Birthday UID Project 1 team Project 2 team
Queen Victor 9/25/1973
Lame Patty 8/2/1982
Fence Noah 7/26/1986
King Joe 12/22/1973
Xtre Dean 11/25/1970
Sew Lane 12/20/1979
Due Paige 1/13/1971
Payne Jane 5/7/1984
Coe John 6/1/1975
Dean Adam 5/2/1983
Cute Sue 5/7/1978
Free Monica 4/3/1987
Then what I suggested in the first is what you need :)
Paste this code in a module.
And Just call this formula in every cell.
=RandNo("U", 8)
Sid
Paste this code in a module.
Public Function RandNo(strChar As String, Numb As Long) As String
RandNo = strChar & Format(Int(Rnd * 10 ^ Numb), String(Numb, "0"))
End Function
And Just call this formula in every cell.
=RandNo("U", 8)
Sid
ASKER
Thanks,
I am new to this and may not have been asking the right question. This is how I got it to work.
Function UID()
UID = "U" & Int((99999999 - 10000000) * Rnd + 10000000)
End Function
and then called =UID() in the cell.
Any tips on how to separate the students into the two groups I mentioned above?
I am new to this and may not have been asking the right question. This is how I got it to work.
Function UID()
UID = "U" & Int((99999999 - 10000000) * Rnd + 10000000)
End Function
and then called =UID() in the cell.
Any tips on how to separate the students into the two groups I mentioned above?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER