WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Randomize

VBA Randomize

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Randomize Statement in VBA

VBA Randomize statement is a simple one-liner statement that we add before applying the RND function. Whenever a workbook is reopened Randomize statement provides a new seed number to the RND function depending upon the computer’s system time.

Before I talk about the Randomize statement, let me introduce you to a simple RND function with VBA.

As a worksheet function “RAND,” in VBA, “RND” too will generate random numbers that are greater than 0 but less than 1.

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

VBA RND Syntax

[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.

Example

For example, look at the below code.

Code:

Sub RND_Example()

    Debug.Print Rnd

End Sub

RND Example 1.1

When I run the code in the Immediate window, I can see the below number.

RND Function - Output

Similarly, when I execute this code for 3 more times, I can see the below numbers.

RND Example 1.2

Now I will close the workbook and reopen it.

Now I will go back to the visual basic editor window.

RND Example 1.3

Now the immediate window is empty and clean.

Now again, I will execute the code four times and see what are the numbers we will get in the immediate window.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

RND Example 1.5

We got the same numbers as we got above.

This doesn’t look like a random number because every time we reopen the file, we tend to get the same numbers starting from scratch.

So, how do we generate random numbers irrespective of whether the workbook reopened or not?

We need to use the “Randomize” statement.

How to Use VBA Randomize Statement?

You can download this VBA Randomize Excel Template here – VBA Randomize Excel Template

Example #1

To get random numbers, all we need to do is to add the simple one-liner “Randomize” before the RND function.

Code:

Sub Randomize_1()
    
    Randomize
    Debug.Print Rnd

End Sub

VBA Randomize Example 1.1

Now I will run the code 4 times and see what I get.

VBA Randomize Example 1.2

It has generated the above numbers in my local window.

Now I will close the file and reopen the file once again.

As usual, we start with a clean slate in the visual basic window.

Now I will again execute the code and see what numbers we get this time.

Randomize -Output

Wow!!! We got different numbers this time around.

Since we added the statement Randomize before the RND function, we get different random numbers every time we reopen the file.

This looks like a random number, isn’t it???

Example #2

Random Numbers Greater Than One

As we have seen, the “RND” function can generate numbers from 0 to 1 only. But in order to generate numbers greater than one random number, we need to use “RANDOM BETWEEN,” which is available with worksheet function class.

So, to generate random numbers greater than one, we need to use the below code.

Code:

Sub Randomize_2()

    Randomize
    Debug.Print Rnd * 100

End Sub

VBA Randomize Example 2.1

Now I will execute the code and see what we get.

Randomize - Output 2

Like this, we can use the “Randomize” statement in VBA to generate random numbers every single time we reopen the excel file.

Recommended Articles

This has been a guide to VBA Randomize. Here we discuss how to use randomize statements in VBA to generate random numbers greater than one with example and downloadable excel template. You can learn more about VBA from the following articles –

  • VBA ByVal
  • Format Fractions in Excel
  • VBA Const
  • VBA Columns
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. 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

WallStreetMojo

Free Excel Course

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

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

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

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

Login

Forgot Password?

WallStreetMojo

Download VBA Randomize Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More