• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VBA Random Numbers

Home » VBA » VBA Math & Trig Functions » VBA Random Numbers

By Jyoti Singh Leave a Comment

VBA Random Numbers

VBA Random Numbers (RND) – Table of Contents

  • VBA Random Numbers
  • How to Generate Random Numbers in Excel?

Random Numbers in VBA

We can generate random numbers in VBA by using a formula called “RND”. This works exactly the same as the excel function “RAND”. As I told in the worksheet function “RAND”, in VBA “RND” too will generate VBA random numbers which are greater than 0 but less than 1.

Now take a look at the syntax of the “RND” function in VBA.

VBA RND Formula

[Number]: We can pass the argument in three ways.

  • If we pass the number as <0, it keeps generating the same random number every time.
  • If we pass the number as 0, it will repeat the most recent number it has given.
  • If we pass the number >0, it keeps giving you different random numbers i.e. the next random number in the sequence.

Examples of RND Function in VBA

Now we will see the simple example of using the “RND” function in VBA. Follow the below steps to write the VBA code on your own.

Step 1: Declare the variable as “Integer” in VBA

Code:

Sub Rnd_Example1()

  Dim K As Integer

End Sub

VBA Random Number Example 1

Step 2: Now assign the value to the variable “k” through VBA “RND” function.

Code:

Sub Rnd_Example1()

  Dim K As Integer
  K = Rnd()

End Sub

VBA Random Number Example 1-1

Step 3: Show the value returned by the variable “k” in the message box.

Code:

Sub Rnd_Example1()

  Dim K As Integer
  K = Rnd()
  MsgBox K

End Sub

VBA Random Number Example 1-2

Now run the excel macro and see what the result is.

VBA Random Number Example 1-3

Look what has happened.

It is showing the result as 1 where “RND” function can return only numbers which are greater than zero but less than 1.

You must be thinking about what the wrong thing here is.

Popular Course in this category
Cyber Monday Sale
VBA Training (3 Courses, 12+ Projects) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
4.6 (247 ratings)
Course Price

View Course

Related Courses

The wrong thing here is the kind of data type we have assigned to the variable “k”.

If you look back at the variable we have declared we have assigned the data type as Integer. Since we have assigned the variable as Integer, it can only show the whole numbers between -32768 to 32767.

Whenever RND returns the decimal number VBA converts the decimal number to the nearest integer i.e. 1.

So, to make the formula work properly declare the variable as “Double”.

“Double” is the data type which can hold decimal values.

Code:

Sub Rnd_Example1()

  Dim K  As Double
  K = Rnd()
  MsgBox K

End Sub

Example 1-4

Now the code and see what the result is.

Example 1-5

Click on ok and run one more time and see what the result is.

Example 1-6

This time we got a different result. Since “RND” is a volatile function in nature it reproduces different results every time you execute the code.

Get Same RND Number Every Time

As we have seen the previous example “RND” function reproduces the result every time we execute the code. In order to get the same random number, again and again, we need to pass the argument as zero.

Code:

Sub Rnd_Example2()

  Dim K As Double
  K = Rnd(0)
  MsgBox K
 
End Sub

This will produce the same number again and again when we execute the code.

Generate Whole Random Number Using RND Function

As I told RND function can return numbers from 0 to 1. But what if we need whole numbers?

We can also generate whole numbers by using other VBA function or other input numbers. For an example look at the below code.

Code:

Sub Rnd_Example3()

  Dim K As Double
  K = 1 + Rnd * 100
  MsgBox K

End Sub

This code will generate random whole numbers with decimal points every time we execute the code.

If you are looking at the whole numbers without decimal points then we can use below code.

Code:

Sub Rnd_Example3()

  Dim K As Double
  K = CInt(1 + Rnd * 100)
  MsgBox K

End Sub

This will keep generating the whole numbers from 1 to 100.

You can download this VBA Random Numbers here. VBA Random Numbers Excel Template

Recommended Articles

This has been a guide to VBA Random Numbers. Here we learned how to Generate VBA Random Numbers in Excel using RND Formula along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

  • Examples to Insert New line in Excel VBA
  • VBA Split Function | Split String
  • VBA Data Type
  • Count Function in VBA
  • AutoFill in VBA
  • Format Number in VBA
  • VBA Enumerations
  • Value Property in VBA
  • Integer Data Types in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: VBA, VBA Math & Trig Functions

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Limited Period Offer - VBA Training Course (6 courses, 35+ hours video) View More