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:
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!
CodePudding user response:
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:
- Always use
Option Explicit
at the very top of the code. It makes the declaration of variables mandatory and it helps to avoid mistakes - 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