May 19 2021 12:43 AM
May 19 2021 12:43 AM
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?
May 19 2021 07:17 AM
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.
So those are my two thoughts. Let me know if they make sense; even more, if they work!
May 19 2021 07:48 AM
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?
May 19 2021 07:49 AM - edited May 19 2021 08:00 AMSolution
@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.