EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home VBA VBA Resources VBA Math & Trig Functions VBA Random Number

VBA Random Number

By Ashwani JaiswalAshwani Jaiswal

VBA Random number

Introduction to VBA Random Number

In excel we have a function called RAND which is used for generating the random numbers. So as we have RND function in VBA which is used for generating random numbers. The RND Function generates numbers which are greater than 0 but less than 1.

Syntax of Random Number in Excel VBA

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The syntax for the Random Number function excel VBA is as follows:

RND Function

We can see in the syntax of the RND Function above, we can only use a single number as input.

We give the input in RND Function in below 3 ways;

  1. If we give number <0, then it will generate the same number every time we run the code.
  2. If we give a number as 0, then it will pop up the most recent number which was generated.
  3. If we give number >0, then it will keep generating different random numbers. But it will be in sequence.

How to Use Excel Random Number Function?

Let’s see the examples of Random Number in Excel VBA.

You can download this VBA Random Number Excel Template here – VBA Random Number Excel Template

Example #1 – VBA Random Number

Step 1: This we can directly apply to VBA. For this go to the VBA window and open a module from the Insert menu as shown below.

Random Number Module 1

Step 2: Once we do that we will get a window of Module in VBA. In that module, write subcategory of VBA Random Number in the same name or in any name as per your choice.

Code:

Sub RandomNumber()

End Sub

Random Example 1.1

Step 3: Now choose a word or alphabet and define it as a variable and assign Double to it. Here we are considering variable Dim A and give it to Double. Selecting double instead of Integer will be useful because we will be seeing decimal values which are between 0 and 1.

Code:

Sub RandomNumber()

Dim A As Double

End Sub

Random Example 1.2

Step 4: Now next we will use variable A which we defined above and we will assign it a function Rnd for generating random numbers as shown below.

Code:

Sub RandomNumber()

Dim A As Double
A = Rnd(

End Sub

Random Example 1.3

Step 5: As we can see in the above screenshot, RND function has the syntax as the number and that will be a single digit number. Here we will see how giving a number less than 0 will generate what? So in brackets of RND Function insert -1 which is the most recent number less than 0.

Code:

VBA Random number 1.5

Sub RandomNumber()

 Dim A As Double
 A = Rnd(-1)

End Sub

Step 6: At last to get the output we need the reference of cell or a message box where it will reflect the random number. Insert Msgbox command of the message box and assign the defined variable here.

Code:

Sub RandomNumber()

 Dim A As Double
 A = Rnd(-1)
 MsgBox A

End Sub

Msgbox 1.6

Step 7: Now we can compile the code and run it by clicking on the play button below the menu bar. We will see the first random number generated which is less than 0 which is 0.2240070104599 as shown below.

Random Number 1

If we again run the code, it will keep on generating the same code again and again.

Example #2 – VBA Random Number

We have seen how to generate random numbers which is less than 0. There is another way to get random numbers. In this example, we will generate the random number by giving 0 as an input to RND function.

For this, we will use the same code used in example-1.

Same Random Example 1

Step 1: Now in place of -1 which is between the brackets of RND Function put 0.

Code:

Sub RandomNumber()

 Dim A As Double
 A = Rnd(0)
 MsgBox A

End Sub

random number Example 2.1

Step 2: We can compile the code, if required or else run directly by pressing F5 key (Shortcut Key) to run the code. We can see the RND function has generated the same number which was generated when we gave input less than 0. Which means, if we give 0 as an input RND function then it will generate the previously generated number.

VBA Random Number

And if we run the code again then it will keep on giving the same random number.

Example #3 – VBA Random Number

In this example, we will see what will happen if we give a value greater than 0. We can choose to write new code or we can make the changes in the code which we have written above. Let‘s consider the code which we have seen above.

VBA Random number 3.1

Step 1: As we need to test number greater than 0 so in RND function use blank and add +1 for values greater than 1 as shown below.

Code:

Sub RandomNumber()

  Dim A As Double
  A = 1 + Rnd()
  MsgBox A

End Sub

VBA Random number 3.1

Step 2: Compile the code if you want to and then run the code. We will see the random number generated is different than the number which was generated earlier. Below is the screenshot, which shows the random number is in the fraction of 1.035….41. Which is greater than 0.

VBA Random 3

If we run the same code again, we will keep on getting different random numbers. So we got just after running the code again. This time the random numbers which we got are the double infraction value compared with the random number generated earlier.

vba random 4

Pros of VBA Random Number

  1. It seems difficult but it is easy to apply.
  2. This is quite useful when we are working on generating ticket numbers on a random basis.
  3. The process shown in example-3 can be implemented in many different ways.

Things to Remember

  • This can be implemented in through MS Excel function also with the function name RAND.
  • We the test example-2 first before we run code of example-2. Because it will actually show what was the number generated earlier.
  • Testing the RND function by keeping the brackets blank will also give us a random number.
  • If we assign the variable as Integer then we will be getting only whole numbers which will be same as if 0 or 1.
  • If we use two-digit numbers as input in RND function then also it will generate the random number.

Recommended Articles

This is a guide to VBA Random Number. Here we discuss how to generate Random Number in VBA Excel using RND Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA InStr
  2. VBA Integer
  3. VBA Select Cell
  4. VBA Transpose
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more