## Excel VBA Random Numbers

We have an inbuilt function calledRNDtogenerate random numbers in VBA. It just takes an argument of a number to generate random numbers. It is also an optional parameter. It will create random numbers greater than 0 and smaller than 1.

It works the same as the **Excel function “RAND**.**” **As we said in the worksheet function “RAND” in VBA, too, we can generate random numbers that are greater than 0 but less than 1.

Now, look at the syntax of the RND function.

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

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

##### Table of contents

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked

For eg:

Source: VBA Random Numbers (wallstreetmojo.com)

### How to Generate Random Numbers using VBA Code?

**VBA Basics Course (16+ Hours of Video Tutorials**)

**–>>** **If you want to learn Excel VBA professionally**, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#### Example #1

We will see a simple example of using the “RND” function. After that, follow the below steps to write the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more on your own.

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

**Code:**

Sub Rnd_Example1() Dim K As Integer End Sub

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

**Code:**

Sub Rnd_Example1() Dim K As Integer K = Rnd() End Sub

**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

Now, run the excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more and see what the result is.

Look what has happened.

It shows the result as 1, where the “RND” function can return only numbers greater than zero but less than 1.

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

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 in VBA that can hold decimal values.

**Code:**

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

Now, code and see what the result is.

Click on “OK” and run once more to see the result.

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.

#### Example #2 – Get the Same Random Number Every Time

As we have seen in the previous example e, the “RND” function reproduces the result every time we execute the code. Therefore, 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

It will produce the same number repeatedly when we execute the code.

#### Example #3 – Generate Whole Random Number

We can also generate whole numbers using other VBA functionsVBA FunctionsVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more or input numbers. For 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, we can use the code below.

**Code:**

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

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

### Recommended Articles

This article has been a guide to VBA Random Numbers. Here, we learned how to generate VBA random numbers using the RND function, practical examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –