Why are my random numbers not changing accordingly in Excel?-CodePudding

I have some code below that generates some random numbers in Excel:

Sub Macro1()

Dim RA1 As Variant
ReDim RA1(1 To 5)

For i = 1 To 5

    Rnd (-1)
    Randomize i

    For j = 1 To 5
            
        RA1(j) = Rnd
                             
    Next j
    
    With Sheets("Sheet1")
    
        .Range(Cells(i, 1), Cells(i, 5)).Value = RA1
        
    End With
    
Next i

End Sub

This code basically generates 5 rows of 5 random numbers, but it is not running exactly as it should. When I run this code on my iMac (2021), the random numbers in each row are exactly the same. However, what this code should be generating are 5 different rows of random numbers.

Here is where things become even weirder. When I run this code on my Windows laptop, the output is as desired - that is, I do indeed get 5 different rows of random numbers. I have spoken to my professor about this and he has tried it on his Windows computer too and got 5 different rows of random numbers.

We have come to the conclusion that it is some setting in my iMac's Excel that is preventing this code from running as it should. My professor also suspects that it is not inherently an Apple issue, since he says no one has come to him with this problem before.

All in all, we basically do not know why my iMac is unable to run this code. Does anyone have any guesses as to why there is this discrepancy here? For example, are there any settings in my iMac's Excel that is preventing my code from running correctly?

Any explanations and solutions will be greatly appreciated! :)

Note: I have not changed any setting in my iMac's Excel before (well, at least, not that I know of), so everything should be default as of now.

CodePudding user response：

If you read the manual of the Randomize statement you can find the following:

To repeat sequences of random numbers, call Rnd with a negative argument immediately before using Randomize with a numeric argument. Using Randomize with the same value for number does not repeat the previous sequence.

So I highly recommend to remove Rnd (-1) which makes it repeat the sequences of random numbers.

Also try removing the seed from Randomize i and just use Randomize so the computer takes the system timer as seed (for better random numbers).

// Edit according comment

If you need the same numbers everytime but different numbers in all rows/columns then you might need to put the initialisation before your first loop.

Rnd -1
Randomize 1 'if you don't need the same seed everytime use Randomize without number

For i = 1 To 5

CodePudding user response：

The behaviour you described is a bug.

First, if we replace line:

Randomize i

with:

Randomize 0

we can see that on Windows we get the exact same repeated values as we would get on a Mac:

This immediately suggested to me that there can only be 2 possible explanations:

Maybe the algorithm is different
There is an issue and the value of i is not passed/read correctly.

In order to find a rule, I used a separate method (brute-forced i/x) and found the following magic numbers. Again, if we replace line:

Randomize i

with:

#If Mac Then
    Dim arr() As Variant: arr = Array(26489, 63707, 185603, 15365, 92513)
    Randomize i / arr(i - 1)
#Else
    Randomize i
#End If

we get the same results on Windows and Mac.

I could not find a clear pattern in those magic numbers so I discarded that the algorithm is different. This left me with finding the issue/bug.

After some trial and error I found that if we pass a Double data type to the Randomize method it does not read the full 8 bytes but instead only reads the first 4 bytes. That is why dividing by those magic numbers worked, because the resulting numbers (of those divisions) were using the first 4 bytes (including the exponent bits) instead of the 8 full bytes.

The fix is to offset (to the left) the double value by 4 bytes. Here is the final code that works on both Windows and Mac:

Option Explicit

#If Mac Then
    #If VBA7 Then
        Public Declare PtrSafe Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As LongPtr) As LongPtr
    #Else
        Public Declare Function CopyMemory Lib "/usr/lib/libc.dylib" Alias "memmove" (Destination As Any, Source As Any, ByVal Length As Long) As Long
    #End If
#End If

Sub Macro1()
    Dim i As Long
    Dim j As Long
    Dim RA1 As Variant
    ReDim RA1(1 To 5)
    
    For i = 1 To 5
        Rnd (-1)
        
        #If Mac Then
            Dim d As Double
            
            d = CDbl(i)
            CopyMemory d, ByVal VarPtr(d)   4, 4 'Read the last 4 double bytes into the first 4
            Randomize d
        #Else
            Randomize i
        #End If

        For j = 1 To 5
            RA1(j) = Rnd
        Next j
        
        With Sheets("Sheet1")
            .Range(Cells(i, 1), Cells(i, 5)).Value = RA1
        End With
    Next i
End Sub

You'll have noticed that I've also added Option Explicit, declared all variables and indented the code.