Coffeehouse Post

Single Post Permalink

View Thread: Simulations using Excel
  • User profile image

    I made an attempt at writing VBA code for this problem, available here:

    Since it uses VBA, you will need to set Excel's security to allow macros to run (in Tools | Options | Security | Macro Security -- setting this value to Medium will prompt you regarding the execution of macros, however you should reset it back to High security if you are concerned about the potential for computer virii executing within Excel's context).

    Basically, I have provided a set of cells to configure the test. These cells are currently located in Range J1:N2. You can specify the number of tests to run in addition to the value of the constants.

    To the left of the configuration cells is a command button. Clicking the button will run the test. The data will populate in Columns A through F, although this is configurable in the code.

    To view the VBA code, select Tools | Macro | Visual Basic Editor. The majority of the code resides on the Sheet1 object.

    Finally, it is worth mentioning that I am currently populating the random number of passengers value using a simple VBA random number generator algorithm. However, if you want to use the RANDBETWEEN function, you can uncomment line 55 and comment line 56. I chose not to use RANDBETWEEN because it appears to recalculate all cells each time that it is executed, which results in very slow performance. The VBA approach will generate 1000 rows of data in less than one second on my workstation.

    Please let me know if you have any questions!