Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

**VBA Random Numbers (RND) – Table of Contents**

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

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

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

**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 macro and see what the result is.

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.

4.6 (247 ratings)

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

Now the code and see what the result is.

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

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

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion