• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Good Quality add-in RNG for Excel VBA

James989

Member
Hi,

I am looking for high quality Random Number Generator(RNG) similar to Mersenne Twister as a comprehensive MS OFFICE EXCEL add-in, enable users to generate random number in Excel VBA for Monte Carlo simulations.

One of the software that can meet this requirement is the Excel version of "EasyfitXL" software from Mathwave Technologies. However, this publisher site is no longer active.

Excel VBA have its own RNG called "Rnd" but I was told that it is NOT a good quality RNG. Where I can get GOOD quality RNG software that can add-in to Excel VBA to do Monte Carlo simulations ?

Regards

James
 

AlanSidman

Well-Known Member
I don't know of any software and I recognize you think that VBA is not sufficient but until you find what you want, you may want to try using this VBA solution

Code:
Option Explicit

Sub RandomNumbers()
    Dim x As Long
    Dim rn As Long
    Dim cn As String
    Dim sn As Long
    Dim en As Long
    cn = InputBox("Which Column to fill?")
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Start Number?")
    en = InputBox("Ending Number?")

    Application.ScreenUpdating = False
    x = 1
    Range(cn & "1").Select
    Do Until ActiveCell.Row = rn
        If ActiveCell.EntireRow.Hidden = False Then
            Debug.Print
            ActiveCell.Formula = WorksheetFunction.RandBetween(sn, en)
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    'Range(cn & "1:" & cn & rn).NumberFormat = "mm/dd/yy;@"
    'a = 1: b = 6: c = Int(Rnd() * (b - a + 1) + a)
    Application.ScreenUpdating = True
End Sub
 

James989

Member
I don't know of any software and I recognize you think that VBA is not sufficient but until you find what you want, you may want to try using this VBA solution

Code:
Option Explicit

Sub RandomNumbers()
    Dim x As Long
    Dim rn As Long
    Dim cn As String
    Dim sn As Long
    Dim en As Long
    cn = InputBox("Which Column to fill?")
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Start Number?")
    en = InputBox("Ending Number?")

    Application.ScreenUpdating = False
    x = 1
    Range(cn & "1").Select
    Do Until ActiveCell.Row = rn
        If ActiveCell.EntireRow.Hidden = False Then
            Debug.Print
            ActiveCell.Formula = WorksheetFunction.RandBetween(sn, en)
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    'Range(cn & "1:" & cn & rn).NumberFormat = "mm/dd/yy;@"
    'a = 1: b = 6: c = Int(Rnd() * (b - a + 1) + a)
    Application.ScreenUpdating = True
End Sub

Thanks for your reply

However, I was told that Rnd() is a flawed RNG(No ?) and try to avoid it.
 

AlanSidman

Well-Known Member
Do you have any supporting data for that statement. It is not something I am aware of, nor have I seen any documentation to support that claim. Would be very interested in seeing some support to validate that.
 

James989

Member

Luke M

Excel Ninja
It seems to me the article is just self promoting a somewhat useless bit of info. Yes, you've "only" got over 16 million random numbers. But why would you need more in Excel? That's more rows than a single sheet can even hold.

Reminds me of long ago when people would previously complain that an XL chart can only have 4000 points, when they had a monitor that could only display 1040 pixels (points) wide. They'd never be able to see all the individual data points anyway. :p
 
Top