Solved

# VBA Random Number

Posted on 2011-02-19
Medium Priority
714 Views
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########.
0
Question by:maudette
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 9

Author Comment

ID: 34933888
I also want to know how to call this in Excel once I have created the code in VBA
0

LVL 30

Expert Comment

ID: 34933913
I have made it more generic. Paste this code in a module

``````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
0

Author Comment

ID: 34933938
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?
0

LVL 30

Expert Comment

ID: 34933953
They will not have the same numbers as Rnd will generate a random number everytime.

Sid
0

Author Comment

ID: 34933976
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.
0

LVL 30

Expert Comment

ID: 34933984
How are the 10 users connected to the same workbook? Is the workbook shared?

Sid
0

Author Comment

ID: 34933998
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?
0

LVL 30

Expert Comment

ID: 34934011
Oh you mean like

U46870011
U29816544
U62269669
U64782118
U26379293
U27934205
U82980161
U82460212
U86261934
U79048001

Sid
0

Author Comment

ID: 34934014
yes exactly!
0

LVL 93

Expert Comment

ID: 34934019
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
0

Author Comment

ID: 34934033
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.
0

LVL 30

Expert Comment

ID: 34934037
This will give you 10 unique numbers.

``````Sub Sample()
Dim Ret As String

For i = 1 To 10
Debug.Print "U" & Format(Int(Rnd * 100000000), "00000000")
Next i
End Sub
``````

Sid
0

LVL 30

Expert Comment

ID: 34934042
If you wish, you may output these numbers to Excel Sheet.

Sid
0

Author Comment

ID: 34934052
Sid, is this a UDF?  How would I call this?
0

LVL 30

Expert Comment

ID: 34934053
Ok where do you want the output?

Sid
0

Author Comment

ID: 34934087
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
Cute      Sue      5/7/1978
Free      Monica      4/3/1987
0

LVL 30

Expert Comment

ID: 34934103
Then what I suggested in the first is what you need :)

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
0

Author Comment

ID: 34934131
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?
0

LVL 30

Accepted Solution

SiddharthRout earned 500 total points
ID: 34934142
Thanks,
I am new to this and may not have been asking the right question.  This is how I got it to work.

You asked the right question and I gave the right answer :)

Replace your function by my function in the module and replace =UID() in the cell with =RandNo("U", 8) ;-)

As far as the 2nd question is concerned that should go as a new post :)

Sid
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month12 days, 18 hours left to enroll