Shuffle this array, or else, fix my randomisation...

I have a macro that creates an array, populates it with random numbers then dumps it into a sheet.

Its quite large, and I'm seeing obvious patterns in the output, so doesn't look remotely random... I've added a test workbook, but depending on PC spec, might not run in a friendly manner... Be warned. Iv'e cut it down as small as I can to be able to see the results... my actual scrip populates 333x333 cells, this does 50x50

My question is either...

Can someone spot anything in my code that would make this more random

OR

Can I shuffle the array a few times after it's created, so add some more "randomness" to it?


This code was created from tutorials and forum posts, I'm "OK" at vba, but quite novice.

Please Login or Register  to view this content.

Many thanks in advance for anyones time and help, regardless of whether it solves my issue.

Yes, VBA using pseudo-random number generator, as described by Microsoft :

Rnd function
Returns a Single containing a pseudo-random number.
https://docs.microsoft.com/en-us/off...p/rnd-function


You can use a WinAPI call instead, like this :

Please Login or Register  to view this content.

For the random number generator, you could use

Please Login or Register  to view this content.

which is much better than VBA Rnd() .

To improve speed, change all the FALSE in the Vlookup of the conditional formatting to TRUE (every random number exists in the table).

HTH,

Francesco

Originally Posted by Darth269

Thanks for this, I'll give it a go, but I have one query regarding the line...

i = Int(Abs(i) / 2147483647 * 12000) + 1

the obvious difference between the rnd functions upper and lower bounds, could you explain the use of 2147483647... or link any documentation that exists for this function so I could look at it in more depth?

You are welcome, thanks for the rep.points.

The Microsoft documentation is here :
https://docs.microsoft.com/en-us/win...i-rtlgenrandom

We want to mimic the rnd function, which produce number between 0 and 1
Since we pass a long variable to this function, the result number is of course between the "Long" variable scope.
If you highlight the "Long" word in VBA then press F1, the help will show this :
Long Data Type
Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647. The type-declaration character for Long is the ampersand (&).
So to convert this return value of Long variable, we use Abs(i) which will produce positive value only then divided by the maximum number of the Long datatype scope to get number between 0 and 1, just as the same with the rnd function.

Originally Posted by Darth269

I'm getting an error, due to the 64bit MS Office (I assume) any thoughts?

Change the function declaration to this (tested on Windows 8 with Excel 2010 64 bit) :

Please Login or Register  to view this content.