title: Excel VBA: why am I unable to nest two worksheet functions, within a loop? Works in Excel but not in VBA
body: <p>Please see VBA code below. The loop runs fine when I run the random number generator function (RandBetween) alone, but crashes when I wrap it with the beta probability function (Beta_Inv). The nested Excel formula works fine and looks like this: = BETA.INV(RAND(), 0.5, 0.5, 0, 1). I can´t get it to work in VBA and I don´t want to have to refer to the formula in the Worksheet, for speed reasons. I highlight below the line where the error occurs (again, works fine if I remove the Application.Beta_Inv function).</p>
  <pre><code>Sub ArrayFillRange()
      Dim CellsDown As Long, CellsAcross As Long
      Dim i As Integer, j As Integer
      Dim Array1() As Double
      Dim Range1 As Range
       
  '   Change these values to alter array dimensions
      CellsDown = 1000
      CellsAcross = 1
       
  '   Redimension array
      ReDim Array1(1 To CellsDown, 1 To CellsAcross)
       
  '   Set worksheet range for output
      Set Range1 = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))
   
  '   Fill the array with random variables from inverse of cum beta probability density function
      Application.ScreenUpdating = False
      i = 0
      For i = 1 To CellsDown
          For j = 1 To CellsAcross
              Randomize
              ' the next line
              Array1(i, j) = Application.Beta_Inv(Round(Application.RandBetween(0, 100) / 100, 2), 0.5, 0.5, 0, 1)
          Next j
      Next i
     
  '   Output array values to worksheet
      Range1.Value = Array1
     
  End Sub
  </code></pre>