- Excel Version
- 365
- 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
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.
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).
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.
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)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 FunctionNormal 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 FunctionIs 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.
