title: Random-Number-Generator Error from "Data Analysis Tool" use in VBA script
body: <p>I have created a VBA script which performs the statistical technique bootstrapping. My original sample is drawn from a Poisson distribution through the use of the Data Analysis Tool in excel (as there is no inverse build in excel function for Poisson distribution and I cannot think of any other way of simulating values from a Poisson distribution). However when I run my VBA script, I got this notification from its first iteration:</p>
  
  <p>"Random Number Generation-Outputs range will overwrite existing data. Press OK to overwrite data in range:'[TestCoverage(Poisson,ss50).xlsm]ParametricBootstrap'!$B$2:$B$51"</p>
  
  <p>I am aware of the "overwriting" demand when the Data Analysis Tool is used and for that reason I always check that the cells are empty before I operate the VBA script. But apart from checking myself that they are clear, I have incorporated in the model a specific part for clearing the contents of the cells that the problem is indicated in, just before the first time the Tool is used (line16), but still the VBA script acts like the cells are not empty showing this notification. By the time the VBA script reaches line22 (the operation of the Data Analysis Tool) it throws the previous message.</p>
  
  <p>The peculiar part is that I have created an identical VBA script (as I have to evaluate bootstrapping for a pool of distributions) which draws the original sample from a Normal distribution, again with the use of the Data Analysis Tool, but in that case it operates normally. </p>
  
  <p>This is the code:</p>
  
  <pre><code>Sub parametricbootstrappoisson50()
  
  Dim iterations As Integer
  
  iterations = InputBox("Please insert the number of times you want the bootstrapping process to be performed")
  
  Application.ScreenUpdating = False
  
  'These statements clear the results in the Coverage Results sheet existing from previous operations of the macro
  Sheets("Coverage Results").Activate
  Range("A3").Select
  Range(Selection, Selection.End(xlToRight)).Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.ClearContents
  Sheets("Parametric Bootstrap").Activate
  Range("B2:C51").Select
  Selection.ClearContents
  
  For n = 1 To iterations
  
      'Creates an original sample from a Poisson distribution with parameter lambda P2 (mean)
      Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$B$2"), 1, 50, _
      5, , Range("P2").Value
  
      '1000 bootstrap samples and bootstrap means are produced and stored in the respective cells
  
      For i = 1 To 1000
  
          'Creates a bootstrap sample from a Poisson distribution with estimated parameter lambda Q2 (mean)
          Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$B$2"), 1, 50, _
          5, , Range("Q2").Value
          Sheets("Parametric Bootstrap").Range("E2").Offset(i, 0).Value = Sheets("Parametric Bootstrap").Range("R2").Value
  
          'Delete the bootstrap sample produced so that it will not need confirmation to overwrite the data when the next bootstrap sample will be drawn
          Range("C2:C51").Select
          Selection.ClearContents
  
      Next i
  
      'This is the process of transferring the results of the bootstrapping process to the Coverage Results sheet
      Sheets("Coverage Results").Range("A1:F1").Offset(n, 0).Value = Sheets("Parametric Bootstrap").Range("I2:N2").Value
  
      'Indication on whether the population mean is covered from the bootstrap confidence intervals of the nth iteration or not
      If Sheets("Parametric Bootstrap").Range("P2").Value &gt;= Sheets("Coverage Results").Range("C1").Offset(n, 0).Value And Sheets("Parametric Bootstrap").Range("P2").Value &lt;= Sheets("Coverage Results").Range("D1").Offset(n, 0).Value Then
          Sheets("Coverage Results").Range("G1").Offset(n, 0).Value = "Yes"
      Else
          Sheets("Coverage Results").Range("G1").Offset(n, 0).Value = "No"
      End If
  
      Sheets("Parametric Bootstrap").Activate
  
      'Delete the original sample produced so that it will not need confirmation to overwrite the data when the next original sample will be drawn
      Range("B2:B51").Select
      Selection.ClearContents
  
  Next n
  
  Application.ScreenUpdating = True
  
  Sheets("Coverage Results").Activate
  Range("I2").Select
  
  End Sub
  </code></pre>