In VBA, there’s are different ways that you can use to generate a random number in Excel, and in this post, we will look at all of them on by one.
To generate a random number, in VBA, there is a function called RND. This function stands for random and when you use this function in code it returns a random number between 0 to 1. In RND, you don’t need to specify any argument.
Range("A1") = Rnd()
The above code uses it to get a random number in the cell A1 and it has returned 0.705547521.
But when you use it for an entire range it returns the same random number in the entire range and that makes it void for using to generate random number more than once.[sc name=”ad-level-2″ ]
Now here it doesn’t make sense to use it, right? But there’s a solution that you can use, FOR NEXT LOOP.
In the above code, you have a loop that loops through 10 cells (one by one) from the selected cell and adds a random number.
Here’s the full code:
[sc name=”ad-level-3″ ]
Sub vba_random_number() Dim i As Long i = 10 For i = 1 To i ActiveCell.Value = Rnd() ActiveCell.Offset(1, 0).Select Next i End Sub
Random Number Between Two Numbers
The code for this formula would be like below.
Sub vba_random_number() Dim myRnd As Integer myRnd = Int(2 + Rnd * (45 - 10 + 1)) Range("A1") = myRnd End Sub
When you run this macro, RND uses the max number and the min number that you have defined to generate the random number and INT removes the decimal from that.[sc name=”ad-level-4″ ]
Using Worksheet Functions in a VBA Code to Generate a Random Number
Before you use these functions make sure to understand their arguments that you need to define. Now in the below code, you have RANDBETWEEN which generates a random number between two numbers.
Range("A1") = WorksheetFunction.RandBetween(1000, 2000)
Here the max number is 2000 and min number is 1000. In the same way, you can use the RANDARRAY which is dynamic arrays function.
Here’s the code.
Range("A1:B10") = WorksheetFunction.RandArray(10, 2, 1000, 2000, True)
If you look at the syntax of the RANDARRAY, you will find that you can enter random numbers in an entire range which is was not possible with all the methods that we have discussed above.
- How to Quickly Generate Random Letters in Excel
- How to Add a New Line (Carriage Return) in a String in VBA
- How to SELECT a Range using a VBA Code
- How to SET (Get and Change) Cell Value using a VBA Code