Progress Bar in a Monte Carlo sim

[DAGUET]

Dear all

here is a xlsm attached that works perfectly .... if I dont insert a progress bar in the code.
If I do, with the code below

'Run a Progress Bar
If i Mod iter / 100 = 0 Then
Call Progress.Progress_Display(i / iter)
End If

there is a dialog box "OBJECT REQUIRED" that is prompted and the code does not execute.

I don't understand why; Any help welcome!!

 

[Chihiro]

Well... I don't see any Module or UserForm that has "Progress.Progress_Display".

One way to do it... (Showing progress in status bar).

Sub run()
'Start up and save original calculation state
Dim StartState As Single
Application.ScreenUpdating = False
StartState = Application.Calculation
Application.Calculation = xlCalculationManual

'set up our variables
Dim i As Double
Dim iter As Double
'Start real macro
'loop through for our count of iterations
iter = ThisWorkbook.Sheets("sheet1").Range("G2")
For i = 1 To iter
    Application.Calculate
    ThisWorkbook.Sheets("Sheet2").Cells(i, 1) = ThisWorkbook.Sheets("sheet1").Range("H11")
    Application.StatusBar = "Progress: " & i & " of " & iter & " " & Format(i / iter, "Percent")
Next i

'clean up what we did on startup
Application.StatusBar = False
Application.Calculation = StartState
Application.ScreenUpdating = True
End Sub

If you want to use visual status/progress bar using userform...
Refer to link below.
http://www.excel-easy.com/vba/examples/progress-indicator.html

 

[Monty]

Hello DAGUET

As per Chihiro better to show progress on Status bar then on progress bar as your macro runs within seconds, so showing progress bar impact will be very less.


Thanks

 

[Hui]

Daguet

Adding progress bars to a worksheet based Monte Carlo analysis using a Data table as you have done is pretty much pointless.

Firstly, Data tables are highly optimised and very fast, Your 100 iterations take well under a second on my pc

Secondly there is no method of telling when Row X of 100 has been updated when using data tables

I have modified your model to use the Data Table to use 400 rows
Then shifted the Frequency analysis to work off the data table

Calculations Need to be on Automatic

See what you think

 

[DAGUET]

Thanks a lot
Great help to me
best regards!!

Well... I don't see any Module or UserForm that has "Progress.Progress_Display".

One way to do it... (Showing progress in status bar).

Sub run()
'Start up and save original calculation state
Dim StartState As Single
Application.ScreenUpdating = False
StartState = Application.Calculation
Application.Calculation = xlCalculationManual

'set up our variables
Dim i As Double
Dim iter As Double
'Start real macro
'loop through for our count of iterations
iter = ThisWorkbook.Sheets("sheet1").Range("G2")
For i = 1 To iter
    Application.Calculate
    ThisWorkbook.Sheets("Sheet2").Cells(i, 1) = ThisWorkbook.Sheets("sheet1").Range("H11")
    Application.StatusBar = "Progress: " & i & " of " & iter & " " & Format(i / iter, "Percent")
Next i

'clean up what we did on startup
Application.StatusBar = False
Application.Calculation = StartState
Application.ScreenUpdating = True
End Sub

If you want to use visual status/progress bar using userform...
Refer to link below.
http://www.excel-easy.com/vba/examples/progress-indicator.html

 

[DAGUET]

Hui
that is great. The automatic refresh is a functionality that adds value to it.
thanks a lot!
best

Daguet

Adding progress bars to a worksheet based Monte Carlo analysis using a Data table as you have done is pretty much pointless.

Firstly, Data tables are highly optimised and very fast, Your 100 iterations take well under a second on my pc

Secondly there is no method of telling when Row X of 100 has been updated when using data tables

I have modified your model to use the Data Table to use 400 rows
Then shifted the Frequency analysis to work off the data table

Calculations Need to be on Automatic

See what you think

 

[Hui]

I prefer using the method attached to the previous file as it avoids using VBA

I don't know if you ever read my post at:
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

It contains a wealth of info on Data Tables and Montecarlo Simulations

 

[DAGUET]

indeed yes. I read it. it iis just great stuff.

thanks