RECALCULATION & RANDOM NUMBERS

[DAHOWARDUK]

I have created an Excel Workbook, with the objective of trying to simulate the queues on a Golf Course, depending on the gap between consecutive tee off times.

Worksheet 1 contains basic data, such as the par for each hole, the minimum, average and the maximum time likely to be spent playing each hole, etc etc

Worksheet 2 contains 20 sets of ‘random duration times’ for playing each hole, generated from the parameters within Worksheet 1. For the statisticians, I use the probability density function of a Triangular Distribution, and the appropriate Excel formula / function to calculate these random values. Each of these 20 sets contains 100 ‘random’ rounds of golf timings.

Worksheet 3 performs all of the Queue Calculation for a particular value of the parameter G, which is the gap between consecutive tee off times.

So I set G=10, and work out the Queue calculation.

My problem is, that if I now set G=8 (say), all of the ‘random’ durations also get recalculated (all 18x100x20 of them). But I need to compare G=10 with G=8 using exactly the same set of ‘random’ data, not a (somewhat) different set. (otherwise I might have introduced an unnecessary extra variable)

Is there a way that I can set Worksheet 2, to only recalculate manually, whilst the remainder of the Workbook, recalculates automatically? If so how?
Or can I set particular ranges in Worksheet 2 to only recalculate manually? If so, how?
Or is the another better solution?

[#2]

@DAHOWARDUK 

These suggestions are just "off the top of my head" things I'd try were I in your shoes. My apologies if you've already tried them.

1. If you're only doing this as a "one-time" affair (as opposed to daily from scratch), you could just copy the full range of randomly calculated values, and then do a "paste-special" values only to a different area, and use those now static values in conjunction with your various values of G.
2. Another possibility: have Worksheet 2 be a separate workbook of its own, not in the same workbook as Sheets 1 and 3. You can still use its values, but (I haven't verified this) I don't think they'd be recalculated when the workbook containing sheets 1 and 3 are recalculated.

So those are my two thoughts. Let me know if they make sense; even more, if they work! ;)

[#3]

@mathetes 

Thanks for the thoughts.

I've 'done' the "copy, paste values solution", but since I have 20 sets of data it is a pain.

Am I correct in thinking there is no "manual calculation" facility for a specific sheet in a workbook, rather than the whole workbook?

[#4 · Accepted solution]

@DAHOWARDUK  ``Is there a way that I can set Worksheet 2, to only recalculate manually, whilst the remainder of the Workbook, recalculates automatically?``

Yes.  In a new "normal" module (not a worksheet object), enter the following macros.

Sub disableCalc()
ActiveSheet.EnableCalculation = False
MsgBox ActiveSheet.Name & " enabled: " & ActiveSheet.EnableCalculation
End Sub

Sub enableCalc()
ActiveSheet.EnableCalculation = True
MsgBox ActiveSheet.Name & " enabled: " & ActiveSheet.EnableCalculation
End Sub

Sub calcStatus()
MsgBox ActiveSheet.Name & " enabled: " & ActiveSheet.EnableCalculation
End Sub

Sub recalcSheet()
Dim oldcalc As Variant
oldcalc = ActiveSheet.EnableCalculation
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
ActiveSheet.EnableCalculation = oldcalc
MsgBox ActiveSheet.Name & " enabled: " & ActiveSheet.EnableCalculation
End Sub

In Excel, use alt+f8 to select and run the desired macro.

Also, enter the following Worksheet_Open event macro or add the statement.

Private Sub Workbook_Open()
Sheet2.EnableCalculation = False
End Sub

Note:  You might need to change Sheet2 to the object name (not the worksheet name) of the worksheet with the "frozen" random data.

This is demonstrated in the attached Excel file.

Note: Sheet1 contains one formula (A6) that used RAND() directly.  This is for demonstration purposes only.  Normally, we would put all random data in a separate worksheet, as you indicated.