0

I have a sheet with random numbers and a loop. The problem is: I need to refresh the sheet and recalculate all the numbers in the sheet. However, the random numbers should only change once in the loop. Right now, they keep changing all the time and the code does not finish. Does anybody know, how I can refresh the random numbers only once per loop? Any help is greatly appreciated.

  Sub MonteCarlo()
  Application.ScreenUpdating = False
   Dim x As Integer

Application.Calculation = xlManual

        For x = 1 To 1
        Do
        Worksheets("Ex-Ante TE").Calculate

        DoEvents
        Loop While Not Application.CalculationState = xlDone

     Worksheets("Monte Carlo").Range("A" & x).Value = Worksheets("Ex-Ante Te").Range("B2").Value
    Worksheets("Monte Carlo").Range("B" & x).Value = Worksheets("Ex-Ante Te").Range("B3").Value
    Worksheets("Monte Carlo").Range("C" & x).Value = Worksheets("Ex-Ante Te").Range("B4").Value

        Next

Application.ScreenUpdating = True
  Application.Calculation = xlAutomatic

End Sub
freddy888
  • 956
  • 3
  • 18
  • 39
  • why do you have the `Do .. While` loop? – arcadeprecinct Aug 29 '16 at 08:43
  • I don't see any random numbers in your code. If you are using the Excel functions `=RAND()` or `=RANDBETWEEN()` then - of course - they change each time that the sheet is calculated. If you want to create random numbers in the VBA code then you might want to have a look here: http://stackoverflow.com/questions/38891165/is-excel-vbas-rnd-really-this-bad/38893651#38893651 – Ralph Aug 29 '16 at 08:49
  • 3
    Worksheet.Calculate is called synchronously (the rest of your VBA code is not executed until it returns), you don't need to wait for it in a loop. – z32a7ul Aug 29 '16 at 09:05
  • Hey, i have cells that use =RAND(). However, you are right the Do...while loop is redundant - quite the simple answer. Thank you so much :) – freddy888 Aug 29 '16 at 11:58

1 Answers1

0

Maybe I am mistaken about your problem, but from what I gather I guess you have a sheet containing

  • =RAND() formulas, which you want to calculate once per For x-Loop.
  • other formulas, which you want to calculate more than once per For x-Loop, meaning in every Do-Loop, which is nested inside the For x-Loop.

A possible solution might be to calculate certain ranges (or named ranges) separately with something like

Worksheets("Ex-Ante Te").Range("A" & x).Calculate or

Worksheets("Ex-Ante Te").Range("myNamedRange").Calculate

in your Do-Loop.

EDIT: as z32a7ul pointed out, you dont have wait for your calculation to be finished, so you won't need Loop While Not Application.CalculationState = xlDone.

Martin Dreher
  • 1,514
  • 2
  • 12
  • 22
  • For future projects you could also consider avoiding any functions and calculations inside the Worksheet, and either print the `rnd()`-Values once per Loop or even omit printing the random-numbers at all and do all the calculation inside VBA. Something like `For i = LBound(myarr()) To UBound(myarr())` | `myarr(i) = Rnd()` | `Next i` comes to mind – Martin Dreher Aug 29 '16 at 09:14
  • Why are you commenting on your own answer instead of editing the answer? Then you could also append (and properly format) the code which is difficult to read in the comment. BTW, the proper sign to indicate a new line in VBA is `:` and not `|`. – Ralph Aug 29 '16 at 09:19
  • It could be that the comment is neither an answer nor an integral part of it, but merely a thought on how things could generally be improved. – Martin Dreher Aug 29 '16 at 09:25