Arrays in Excel VBA. At some point it puts NA instead of the value

Question

I am trying to make a simple simulation in Excel VBA in which we roll two dices. What is the probability of getting "1"+"2" or "1"+"3"?

This is my code:

 Sub ProbabilityMeyerArray()
    Dim i As Long
    Dim ArrayDices(1 To 100000, 1 To 2) As Variant
    Dim ArrayResult(1 To 100000) As Variant
    
    'Simulation
    For i = 1 To 100000
        ArrayDices(i, 1) = WorksheetFunction.RandBetween(1, 6)
        ArrayDices(i, 2) = WorksheetFunction.RandBetween(1, 6)


        If (ArrayDices(i, 1) = 1 And ArrayDices(i, 2) = 3) _
            Or (ArrayDices(i, 1) = 1 And ArrayDices(i, 2) = 2) _
            Or (ArrayDices(i, 1) = 3 And ArrayDices(i, 2) = 1) _
            Or (ArrayDices(i, 1) = 2 And ArrayDices(i, 2) = 1) Then
            
            ArrayResult(i) = 1
            
        Else
            ArrayResult(i) = 0
        End If
    Next i
    
    
    'print the values to cells
    Range("A1:B100000").Value = ArrayDices
    Range("C1:C100000").Value = WorksheetFunction.Transpose(ArrayResult)
    
    'Calculate the probability
    Probability = Application.WorksheetFunction.Sum(ArrayResult) / 100000
    
    MsgBox "The Probability is " & Probability
    
End Sub

The problem is that when I print the values from arrays to the cells, then in column C I have 0 and 1 (as it should be), but then from row 34465 I get NA. Here is a screenshot:

https://ibb.co/7jsjjJC

So, for some reason it starts putting NA instead of 0 and 1. The calculation does not work properly either, because the probability is too low, and I guess this is because it only counts the first 34464 zeros and ones, while dividing with 100 000. Can you help me understand what is wrong here? It seems to be a problem with (my understanding of) arrays, since I can run a similar simulation without arrays (by simply using cells), and it works.

Thanks in advance!

Answer 1

As @RaymondWu said in the comments, the problem is that the Transpose function has a limit to the length of the array it can manipulate. This limit is somewhere between 65k and 66k columns.

Indeed, your code will run as expected for 65k iterations.

You can easily avoid using transpose and to be honest I don't see the reason to use it in the first place.

Instead of declaring your array as Dim ArrayResult(1 To 100000) As Variant which by default makes it 1 row x 100000 columns, you can declare it as so:

Dim ArrayResult(1 To 100000, 1 To 1) As Variant

This will make it 100000 rows x 1 columns, which can now be written in a column in excel easily like so:

Range("C1:C100000").Value = ArrayResult

Of course you will also need to change the code accordingly where needed:

 ArrayResult(i,1) = 1
            
        Else
            ArrayResult(i,1) = 0

A few other tips:

1. Always use Option Explicit at the very top of the code. It makes the declaration of variables mandatory and it helps to avoid mistakes
2. Always use explicit references. When referencing Ranges the best practice is to define the worksheet to which they belong e.g. ThisWorkbook.Worksheets("Sheet1").Range("C1:C100000").Value = ArrayResult