Solved

VBA Random Number

Posted on 2011-02-19
19
668 Views
Last Modified: 2012-05-11
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
Comment
Question by:maudette
  • 9
  • 9
19 Comments
 

Author Comment

by:maudette
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

by:SiddharthRout
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

Open in new window


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

by:maudette
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

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

Sid
0
 

Author Comment

by:maudette
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

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

Sid
0
 

Author Comment

by:maudette
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

by:SiddharthRout
ID: 34934011
Oh you mean like

U46870011
U29816544
U62269669
U64782118
U26379293
U27934205
U82980161
U82460212
U86261934
U79048001

Sid
0
 

Author Comment

by:maudette
ID: 34934014
yes exactly!
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 92

Expert Comment

by:Patrick Matthews
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

by:maudette
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

by:SiddharthRout
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

Open in new window


Sid
0
 
LVL 30

Expert Comment

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

Sid
0
 

Author Comment

by:maudette
ID: 34934052
Sid, is this a UDF?  How would I call this?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934053
Ok where do you want the output?

Sid
0
 

Author Comment

by:maudette
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                  
Dean      Adam      5/2/1983                  
Cute      Sue      5/7/1978                  
Free      Monica      4/3/1987                  
0
 
LVL 30

Expert Comment

by:SiddharthRout
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

Open in new window


And Just call this formula in every cell.

=RandNo("U", 8)

Sid
0
 

Author Comment

by:maudette
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

by:
SiddharthRout earned 125 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now