• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
Dermot

VBA: Enhance and speed up random numbers and sorting, using spill functions

Excel Version
  1. 365
  2. 2019
Sorting Arrays
VBA does not have a built in sort function, which means you need your own sorting code. But now, the Excel Sort function can be used in VBA, and based on my testing, it is faster than my quicksort function.
Note that the array must be 2D, eg (1000,1) or (500,3)

For example if you have an array A, you can sort it into an array B like this
VBA Code:
B = Application.WorksheetFunction.Sort(A)
you can include a sort column and sort direction if needed, just as in Excel

Better behaved random numbers
VBA's Rnd is very fast, if rather primitive, and OK most of the time, except if you are interested in a very small interval, eg 0-0.01.

However, Excel now uses the much better Mersenne function, and while this function can't be called from VBA, you can call RANDARRAY to create a large set of random numbers from Excel, which you can use as required. This is half as fast as Rnd, which is pretty good given the much greater complexity of the Mersenne algorithm.
VBA Code:
Function RandXL() As Single 'gets a single random number
  Static Remaining As Long, R() As Variant
  If Remaining = 0 Then 'get more numbers if necessary
    R = Application.WorksheetFunction.RandArray(1000, 1) 'get a new set of random numbers
    Remaining = 1000
  End If
  RandXL = R(Remaining, 1) 'use the next number in our array
  Remaining = Remaining - 1
End Function

Normal random numbers
This trick doesn't involve spill functions, but it shows a way to create normal random numbers extremely quickly - more quickly than any VBA code or even Excel itself - as long as you need lots of them.

It relies on the fact that to create a normal random number, you start with an ordinary random number (ie 0-1), to which a complex formula is applied. Suppose you precalculated and stored the result for every number between 0 and 1 in steps of (say) .001, using the Excel NORM.INV function, then you wouldn't need to do the complex calculation - you could just look up the result. and that's what this code does. It is half as fast as Rnd, which is very quick (considering it includes a Rnd).

VBA Code:
'Creating the table causes a tiny lag up front, but thereafter, the lookup is instantaneous
Function NormalRandLookup(Optional Mean As Single = 0, Optional StdDev As Single = 1) As Single
  'most of the code is there to set up the lookup table
  Static R() As Single, n As Long 'Static makes the function remember these items next time we visit here
  If n = 0 Then 'we need to create the lookup table
    Dim i As Long
    n = 1000 'make this smaller or large to reduce or increase the resolution - 1000 seems to give .99+ correlation with using NORMINV
    ReDim R(0 To n)
    R(0) = -3.3: R(n) = 3.3 'the limits applied by Excel
    For i = 1 To n - 1
      R(i) = Application.WorksheetFunction.Norm_Inv(i / n, 0, 1)
    Next i
  End If
  NormalRandLookup = Mean + StdDev * R(Rnd * n)
End Function

Is it accurate enough to do this? I believe so, unless you need extreme precision, and even then, you can split the interval 0-1 into more steps. But for business applications, this should be satisfactory.
  • Like
Reactions: Dan_W
Author
Dermot
Views
1,281
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from Dermot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top