1

I'm trying to create a monte carlo macro in excel using formulas that involve randbetween. See example

Const Sim_Number As Long = 100
Dim outcomes(Sim_Number)

For counter_simple = 1 To Sim_Number
    outcomes(counter_simple) = ActiveSheet.Range("A1")
    Application.SendKeys ("{F9}") 'refresh
Next counter_simple

After the for loop the macro calculates average, standard deviation etc. The problem I'm having is that it seems like the refreshes happen all at once and the macro itself just uses the value per the last refresh for all 100 instances. So the average, minimum, maximum etc are all just the same value.

I wrote another macro that works as intended with the randbetween functions within the macro itself to determine that the issue is the for loop. What is the issue with refreshing within the macro? Is there a non sendkeys way to refresh randbetween values? Thanks.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Diofsi
  • 35
  • 3
  • 4
    Try using Activesheet.Calculate instead of sendkeys – Tim Williams Sep 25 '17 at 05:28
  • Also, since you are using RandBetween, I recall you need to use `Randomize`before each random call, so it actually changes. [There is more info about that here](https://stackoverflow.com/questions/2884972/repeating-random-variables-in-vba) – Moacir Sep 25 '17 at 11:48
  • Thanks Tim, that change worked. Is it not advised to use sendkeys in a loop the way I was because it is delayed? I looked at help in excel and it says "In some cases, you must call this method before you call the method that will use the keystrokes" about sendkeys. – Diofsi Sep 25 '17 at 12:58
  • `SendKeys` is a "last resort" approach for when there's no other more-direct way of doing what you want. Since there's an existing worksheet method which does what you need then I would always use that ahead of sendkeys. – Tim Williams Sep 25 '17 at 18:13

0 Answers0