# 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.
Dan_W
Author
Dermot
Views
3,022
First release
Last update
Rating
0 ratings

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

### Which adblocker are you using?

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

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