VBA Rnd Function

VBA Rnd Function

Overview of VBA Rnd Function:

VBA Rnd function is categorized as Math(Mathematical) & Trig function. This is a built-in Excel VBA Function. This function returns a random number that is greater than or equal to 0 and less than 1. This function takes an optional argument and it contains a pseudo random number. Use VBA Randomize function to generate different set of random numbers every time.

We can use this function in VBA and can’t use in Excel. This function we use in either procedure or function in a VBA editor window in Excel. We can use this VBA Rnd Function in any number of times in any number of procedures or functions. Let us learn what is the syntax and parameters of the Rnd function, where we can use this Rnd Function and real-time examples in Excel VBA.

Syntax of VBA Rnd Function

The syntax of the Rnd Function in VBA is

Rnd([Number])

The Rnd function returns an integer value.

Parameters or Arguments:

The Rnd function has one argument in Excel VBA.
where
Number:The Number is a required parameter. It represents a number or numeric value. We use this parameter for which we want to generate random number.

[Number] Output
< 0 The same random number each time, using [Number] as the seed.
> 0 The next random number in the sequence.
= 0 The most recently generated random number.
Blank or Omitted The next random number in the sequence.

Where we can apply or use VBA Rnd Function?

We can use this Rnd Function in VBA MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Generate Random Number without optional argument

Here is a simple example of the VBA Rnd function. This below example generates random number without optional argument.

'Generate Random Number without optional argument
Sub VBA_Rnd_Function_Ex1()

    'Variable declaration
    Dim dResult As Double
            
    dResult = Rnd()
        
    MsgBox "The Random value of the number without optional argument is : " & dResult, vbInformation, "VBA Rnd Function"
    
End Sub

Output: Here is the screen shot of the first example output.
VBA Rnd Function

Example 2: Generate Random Number with optional argument

Here is another simple example of the VBA Rnd function. This below example generates random number with optional argument.

'Generate Random Number with optional argument
Sub VBA_Rnd_Function_Ex2()

    'Variable declaration
    Dim iValue As Integer
    Dim dResult As Double
    
    iValue = -4
        
    dResult = Rnd(iValue)
        
    MsgBox "The Random value of the number -4 is : " & dResult, vbInformation, "VBA Rnd Function"
    
End Sub

Output: Here is the screen shot of the second example output.
VBA Rnd Function

Example 3: Generate Random Number

Let us see one more example of the VBA Rnd function. This below example generates random number.

'Generate Random Number
Sub VBA_Rnd_Function_Ex3()

    'Variable declaration
    Dim iValue As String
    Dim dResult As Double
    
    iValue = 4
        
    dResult = Rnd(iValue)
        
    MsgBox "The Random value of the number 4 is : " & dResult, vbInformation, "VBA Rnd Function"
    
End Sub

Output: Here is the screen shot of the third example output.
VBA Rnd Function

Example 4: Generate Random Number

Another example of the VBA Rnd function. This below example generates random number.

'Generate Random Number
Sub VBA_Rnd_Function_Ex4()

    'Variable declaration
    Dim iValue As Double
    Dim dResult As Double
    
    iValue = 0
        
    dResult = Rnd(iValue)
        
    MsgBox "The Random value of the number 0 is : " & dResult, vbInformation, "VBA Rnd Function"
    
End Sub

Output: Here is the screen shot of the fourth example output.
VBA Rnd Function

Instructions to use Macro Codes

Here are the step by step instructions to use the VBA Rnd function in Excel VBA.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Copy above specified macros to the VBA editor.
  • Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.

VBA Math(Mathematical) & Trig Functions List of VBA Functions

Leave a Comment

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