CE En 270 - Homework #22
Oil Migration Problem: Multi-Dimensional Ranges & Simulation
The goal of this exercise is to simulate the movement of an oil spill on the surface of a body of water. A number of simplifying assumptions need to be made in order for this to be practical. The surface of the water is proceeds in a series of discrete time steps, one time step at a time. At each time step, the following rules determine the movement of oil in the simulation:
- E% of the oil in a square evaporates, where E is a constant entered by the user;
- Of the remaining oil, 10/(10+V) of the oil remains in the same cell, where V is the wind velocity in miles per hour;
- Of the oil which does not evaporate and does not remain in the same cell, 40% moves to the cell immediately to the east, 20% moves into each of the cells to the north-east and the south-east; and 10% moves into each of the cells to the north and south.
- Oil moving into cells beyond the bounds of the grid is lost to the simulation. At each time step, the program goes through the entire array and calculates the amount of oil in each cell based on the amounts in the neighboring cells at the previous time step. To do this correctly you need two grids -- one to hold all the old values while the new values are computed, and one to hold the new values.
The grid will be processed one time step at a time. To keep the display manageable, we assume that the maximum amount of oil in a cell, measured in barrels, is 999, and only the digits to the left of the decimal point are displayed. Therefore each cell in the grid takes up 3 digits plus a space in the display, and so the maximum array size that will fit comfortably in a reasonably sized spreadsheet is 19 columns by 19 rows. Initially (at time step zero), a given amount of oil is spilled into the middle cell in the westernmost column (middle of left edge of the grid), and all other cells are initialized to zero.
The user inputs the following information:
- # of barrels spilled (between 0 and 999)
- wind velocity in miles per hour
- evaporation rate (fraction between zero and one);
Your spreadsheet may look something like this:
Initially (after the Reset button has been selected), the time step is zero and only one cell (the one on the left side) contains oil. When the user hits the Step button, one time step is processed and the result is displayed. Whenever the user hits the Reset button, the grid should be initialized to zero, the time step should be set to zero, the initial oil amount should be added to the cell on the left, and the display should be updated.
Tips:
1. As you are moving oil among cells due to wind movement, you will be moving from one cell to each of the cells on the N, S, E, NE, and SE sides. However, you need to be careful. If you are on the top row, you cannot move to the N. If you are on the rightmost column, you cannot move to the NE, SE, or E. And on the bottom, you cannot move S. One way to solve this is to put in some if statements and don't move in those directions if you are on the edge. Another method is to use an extra row at the top and the bottom and an extra column on the right side of the grid. These rows and columns serve as a buffer. You can then cycle through the main part of your grid and you don't need the if statements when you get to the edge. You just move the oil off the main part into the buffer and the oil is lost to the simulation. To ensure that this doesn't mess up the display on the main page of your workbook, you may wish to do all of your calculations on two extra grids ("new" and "old") located on extra sheets in your workbook.
2. As you parse through your grids, it can get difficult to keep track of the proper indices to use. To keep things simple, I like to use some constants. For example:
Const nrows As Integer = 19
Const startrow As Integer = 11
Const endrow As Integer = 29
Const middlerow As Integer = 20
Const ncols As Integer = 19
Const startcol As Integer = 2
Const endcol As Integer = 20
and you can loop through your ranges as follows:
For i = startrow To endrow
For j = startcol To endcol
Sheets("Old").Cells(i, j) = Sheets("Main").Cells(i, j) '<-Example
Next j
Next i
3. To get a good fit for the oil spill grid in your spreadsheet, you may want to try setting the column widths to 4 (use the Format/Column/Width command).
4. You can speed up the display for each time step by adding the following statements just before and just after the code for each time step:
Application.ScreenUpdating = False
...
<put code here for time step caculations>
...
Application.ScreenUpdating = True
EXTRA CREDIT: You will be given 40% extra credit if you enhance your spreadsheet to do each of the following:
a. (10%) Use a series of colors to display the
variation in oil concentration as shown above rather than simply displaying the
numbers. I have found that the following ranges seem to work pretty well:
| Range | Color |
| #barrels < 0.5 | white |
| 0.5 <= #barrels < 2.5 | blue |
| 2.5 <= #barrels < 5.5 | cyan |
| 5.5 <= #barrels < 10.5 | green |
| 10.5 <= #barrels < 30.5 | yellow |
| 30.5 <= #barrels | red |
b. (30%) Change your spreadsheet so that it has controls similar to the following:
When you click on the Solve button, it should compute all of the time step solutions at once and store them in a three-dimensional array of grids (one index for the time step number and the other two indices for the rows and columns). Keep computing time steps until the largest value in any cell in the grid is less than 0.5. When you click on the <- Step or Step -> button, you should display the previous or next time step by copying it from the array to the spreadsheet. When you click on the Animate button you should automatically cycle through all of the time steps in sequence.
As you do your calculations (for the Solve button), nothing should be written to or read from the sheets. All calculations should be performed using arrays.
To simplify your array declarations, you can assume that the maximum number of time steps = 200.
Submittal Instructions:
Click here to upload your homework assignment.