GWLFXL: User’s Manual - Cornell University
GWLFXL 1.2.1 User’s Manual
January 2007
Bongghi Hong and Dennis Swaney
1. Introduction ................................................................................................................................2
1.1. General information ….................................................................................................2
1.2. System requirements ...................................................................................................2
2. New Features ..............................................................................................................................3
2.1. Atmospheric deposition of nitrogen ............................................................................3
2.2. Denitrification losses ...................................................................................................4
2.3. Land use “trajectories” ................................................................................................6
3. Model Setup ................................................................................................................................7
3.1. Obtaining GWLFXL model .........................................................................................7
3.2. Creating data folder .....................................................................................................7
3.3. Setting security options ...............................................................................................7
3.4. Installing Solver ...........................................................................................................8
3.5. Opening the model .......................................................................................................9
4. User Interface Overview ……...................................................................................................11
4.1. Menu items …….........................................................................................................11
4.2. Worksheets …….........................................................................................................13
5. Model Inputs .............................................................................................................................14
5.1. Specifying simulation options ....................................................................................14
5.2. Specifying transport parameters ................................................................................17
5.3. Specifying nutrient parameters ..................................................................................21
5.4. Providing weather data ..............................................................................................27
6. Running GWLFXL ……...........................................................................................................29
6.1. Making a standard run ...............................................................................................29
6.2. Examining simulation output .....................................................................................29
6.3. Secondary pulldown menus .......................................................................................35
7. Running Calibration ……..........................................................................................................36
8. Uncertainty and Sensitivity Analysis ........................................................................................40
8.1. Running uncertainty analysis .....................................................................................40
8.2. Running sensitivity analysis …..................................................................................43
9. “Tools” Pulldown Menu ...........................................................................................................48
10. “Print Output” Pulldown Menu …...........................................................................................51
11. “Help” Pulldown Menu ….......................................................................................................53
12. VBA Implementation ..............................................................................................................54
Appendix A: Troubleshootings .....................................................................................................55
A.1. Opening GWLFXL model …....................................................................................55
A.2. Making a standard run …...........................................................................................57
Appendix B: Glitch Fix Report ….................................................................................................60
1. Introduction
1.1. General information
GWLFXL is a Visual Basic for Applications (VBA) implementation of GWLF (Generalized Watershed Loading Functions), a watershed nutrient loading model, which runs in Microsoft Excel (Figure 1). The most recent version of the model (version 1.2.1) is available on the World Wide Web at . The application is a workbook consisting of several worksheets and a pulldown menu. The user can perform all the usual spreadsheet functions in addition to running the GWLF model. This documentation includes descriptions of all the worksheets and menu items used in GWLFXL.
[pic]
Figure 1. GWLFXL user interface.
1.2. System requirements
To make the best use of GWLFXL version 1.2.1, users need to have Excel 2003 (Microsoft Office 11), Excel 2002 (Microsoft Office 10), or Excel 2000 (Microsoft Office 9) installed on their computers. To be able to run calibration, the Solver Add-in should be running in Excel (see Section 3.4 for installing the Solver). Some glitches are expected when running the model in Excel 97 (Microsoft Office 8) or Microsoft Excel for Mac.
2. New Features
In addition to using Microsoft Excel as a modeling platform, thereby facilitating post-simulation analysis and providing alternate modes of simulation (uncertainty analysis, calibration, etc.), a few new features have been added to the model itself. These include:
• Atmospheric deposition of nitrogen,
• Denitrification losses, and
• Land use “trajectories”, i.e., the option of assuming linear trends of landuse change over time instead of fixed areas of land use.
These features are briefly described below.
2.1. Atmospheric deposition of nitrogen
Atmospheric nitrogen is treated very simply as a matter of bookkeeping with no attempt to simulate detailed chemical processes or transformations. Two forms of deposition are considered: wet deposition and dry deposition. Two new parameters (see Section 5.3 for description of nutrient parameters) are required to characterize the deposition on an annual basis, with the assumption that the total amount does not change from year to year. The two parameters are: total annual atmospheric N deposition (“Atmos N dep” in the “nutrient” worksheet, kg/ha/yr) and the fraction of deposition which occurs as dry deposition (“fraction dry dep” in the “nutrient” worksheet, unitless). Dry deposition over the year is assumed to fall at a uniform rate irrespective of weather or season, and so the daily value is equal to the total deposition times the fraction occurring as dry deposition divided by the number of days of the year:
[pic] (1)
where: drydep (d) = dry deposition of N on day d (kg/ha/day)
pdry = fraction of deposition occurring as dry deposition
ndeptot = total annual N deposition (kg/ha/yr)
yearlength = 365 (or 366 in leap years)
Wet deposition is assumed to fall in proportion to precipitation, and thus to be distributed over the year in the same proportions as precipitation distribution. This is accomplished by calculating an effective wet deposition concentration for each year y, ndepc (y):
[pic] (2)
where: ndepc (y) = effective concentration of nitrogen in precipitation in year y (mg N/l)
psum= total (sum) of precipitation for year y (cm)
Watershed wet (wetdepnitr) and dry (drydepnitr) depositions (kg N) for month i in year y are calculated respectively as:
[pic] (3)
where: precipitation (y, i) = total precipitation in year y, month i (cm)
areatotal = area of the watershed (ha)
[pic] (4)
where: lenmo (i) = number of days in month i
nday (y) = number of days in year y
2.2. Denitrification losses
Denitrification losses are estimated on a monthly basis. The fraction of denitrification loss to nitrogen fluxes from each watershed land use category and from watershed groundwater nitrogen are specified independently (see Section 5.2 for description of transport parameters). Denitrification occurs in three modes in the model:
• Watershed-wide denitrification of atmospherically-deposited nitrogen, which is assumed to occur at an area-weighted rate of that associated with each land use type,
• Near-surface denitrification of nitrogen otherwise lost to surface runoff from each category of land use, which occurs at a rate specific to each type of landuse, and
• Groundwater denitrification, which occurs at an average rate specific to groundwater over the entire watershed.
The first mode of denitrification is calculated on the basis of the monthly level of atmospheric deposition of nitrogen. A watershed-average level of near-surface denitrification fraction (denitfracavg) is calculated as an area-weighted average of each of the land use values:
[pic] (5)
where: area (l) = area of landuse l (ha)
denitfrac (l) = fractional loss of nitrogen to denitrification for landuse l
The watershed average loss of nitrogen to denitrification in month i for year y before it reaches groundwater is estimated by applying the above average fraction to the sum of wet and dry atmospheric deposition of nitrogen:
[pic] (6)
The second mode of denitrification results in a reduction of nitrogen flux in surface runoff or washoff from any landuse category. In the absence of denitrification, runoff loss of nitrogen in a given month i from a given rural landuse l is calculated as:
[pic] (7)
where: nrunoff (l,i) = nitrogen loss in runoff in month i from landuse l (kg N)
nitrconc (l) = user-specified nitrogen concentration of runoff from landuse l without denitrification (mg N/l)
Q (l, i) = amount of runoff in month i from landuse l (cm)
If a nonzero denitrification fraction for landuse l (denitfrac (l)) is specified, then the amount of denitrification (kg N) occurring in month i from landuse l is calculated as:
[pic] (8)
and the nitrogen loss in runoff corrected for denitrification is:
[pic] (9)
Similarly, for urban land uses, runoff nitrogen loss in the absence of denitrification is calculated as:
[pic] (10)
where: W (l,i) = washoff of solid-phase nutrients in month i from urban landuse l (kg N/ha)
If a nonzero denitrification fraction is specified for urban landuse l (denitfrac (l)), then the amount of denitrification occurring in month i is calculated as:
[pic] (11)
and the nitrogen loss in runoff corrected for denitrification is:
[pic] (12)
The third mode of denitrification is expressed as a fraction of the groundwater nitrogen contribution to streamflow nitrogen. In the absence of groundwater denitrification, this contribution to stream nitrogen (kg N) in month i for year y is:
[pic] (13)
where: grnitrconc = concentration of nitrogen in groundwater (mg N/l)
groundwater (y,i) = groundwater contribution to streamwater over the entire watershed in month i for year y (cm)
areatotal = total area of the watershed (ha)
If groundwater denitrification is present, it is expressed as the product of the fraction of mass lost denitrification and the groundwater contribution to streamflow:
[pic] (14)
where: gwdenitfrac = fractional loss of groundwater nitrogen to denitrification
and the groundwater nitrogen contribution to streamflow is adjusted for this loss:
[pic] (15)
2.3. Land use “trajectories”
Land use trajectories permit simulation of the effects of landuse change over time in a simple manner. In the current version (1.2.1) of the model, simple linear changes in land uses are permitted to occur over time, though the total watershed area should be constrained to be the same. This option can be specified in cell D15 of the “notes and parameters” worksheet (see Section 5.1 for specifying trajectory option). If the option is chosen, instead of specifying a single area for each land use, an initial and final area for each landuse should be specified in the “transport” worksheet and the program will interpolate the areas between these limiting beginning and ending landuse distributions for each year of the simulation. Properties of each landuse category will be used, and thus the relative importance of these properties will vary in each year of the simulation.
3. Model Setup
This section describes the setup processes the user needs to take to obtain and open the GWLFXL model. Most of the steps described in this section are required only once when the model is first used. Troubleshooting information on the model setup is available in the Appendix of this document.
3.1. Obtaining GWLFXL model
The GWLFXL model can be downloaded from the World Wide Web at . Click on “Download GWLFXL 1.2.1” and download the compressed file “GWLFXL1.2.1.zip”. The compressed file contains a Microsoft Excel file “GWLFXL1.2.1.xls”, which is the model file, as well as three text files “transport.txt” (transport parameters), “nutrient.txt” (nutrient parameters), and “hbweather.txt” (weather data) that are inputs to the model (see Section 5 for description of model inputs). The compressed file also contains “GWLFUsersManual.doc”, which is documentation of the original GWLF model.
3.2. Creating data folder
After downloading the GWLFXL model, the user should extract the compressed files, create a default data folder, and move the text and documentation files to the folder. The default data folder is where the model inputs read by the GWLFXL model are stored. The model will also write the simulation output (“results.txt”) to the folder after making a simulation. However, the model file (“GWLFXL1.2.1.xls”) doesn’t have to be stored in the default data folder. When the model is downloaded for the first time, the default folder is assumed to be “C:\GWLFXL\Data\”, as specified in cell D7 of the “notes and parameters” worksheet (see Section 5.1 for specifying default data folder). Providing the transport and nutrient files is optional because these parameters may be read from Excel spreadsheet (see Section 5.1 for specifying options for reading parameters). However, the weather data must be read from a text file stored in the default folder.
3.3. Setting security options
In order to permit the VBA program to function, security levels must be set appropriately to allow VBA to access the spreadsheet before opening the model. If the settings are not set properly, a popup warning window will appear when the spreadsheet is opened, and the GWLFXL dropdown menu will not appear among the others at the top of the spreadsheet. Two separate security settings must be specified. First, open Microsoft Excel and click on “Tools > Macro > Security…” and the “Security” window will be opened (Figure 2). Select the “Security Level” tab and choose the “Medium” option:
[pic]
Figure 2. Setting security level.
Next, select the “Trusted Publishers” tab and check the “Trust access to Visual Basic Project” checkbox (Figure 3):
[pic]
Figure 3. Setting trusted publishers.
Click “OK” and the “Security” window will be closed with appropriate settings.
3.4. Installing Solver
Optimal use of GWLFXL requires the Solver Add-in running in Excel, thus it is a good idea to install the Solver before opening the model. The user can check whether the Solver is installed or not by clicking “Tools > Add-Ins…” (Figure 4). If the “Solver Add-in” checkbox is not checked in the “Add-Ins” window shown below, the Solver is not installed. The user can install the Solver by checking the “Solver Add-in” checkbox and clicking “OK”.
[pic]
Figure 4. Installing Solver add-in.
3.5. Opening the model
After the above settings are made, the user can open the GWLFXL model by clicking on “File > Open…” and locating the “GWLFXL1.2.1.xls” file. Before the model is opened, a “Security Warning” window will be shown (Figure 5):
[pic]
Figure 5. Security warning window.
Clicking on “Enable Macros” will open the “GWLFXL1.2.1.xls” workbook with the following message box (Figure 6), indicating that the model is opened successfully:
[pic]
Figure 6. Menu creation message box.
The model workbook will look as shown in Figure 7. Note that a new menu “GWLFXL” is added immediately to the left of the “Help” menu. This menu is removed automatically when closing the model.
[pic]
Figure 7. Opening GWLFXL model.
Note that the GWLFXL model that can be downloaded from the World Wide Web at is saved in Excel 2003 (Microsoft Office 11). The user may experience some difficulties when opening the model from other versions of Excel. In general, the GWLFXL model saved in one Excel version may require some additional setup processes, as described in the Appendix of this document, when opening from another version. (The version number of the Excel program can be found by clicking on “Help > About Microsoft Excel”.)
4. User Interface Overview
The GWLFXL user interface consists of menu items and worksheets. The worksheets contain information relevant to the current use of the model such as model parameters, simulation outputs, and special features (e.g., calibration). The menu items are used to carry out specific tasks that often result in updating the contents of the relevant worksheets, such as running a simulation, creating a parameter worksheet, and performing calibration. The menu items and worksheets are briefly described below.
4.1. Menu items
All the menu items are available through the “GWLFXL” menu located immediately to the left of the “Help” menu. Clicking on the “GWLFXL” reveals the pull-down menu (Figure 8). The first three menu options are used to make a standard run of the GWLFXL model, and the remaining options reveal secondary pull-down menus.
[pic]
Figure 8. GWLFXL menu items and worksheets.
All the menu items available in GWLFXL are summarized in Table 1. Detailed description of each item is available in subsequent sections specified in Table 1.
Table 1. List of GWLFXL menu items.
|Primary Menu Item |Secondary Menu Item |Description |Section |
|Run GWLFXL (hydrology - std | |Make a standard run of the GWLFXL model simulating |Section 6 |
|run) | |hydrology only | |
|Run GWLFXL (hydrology, | |Make a standard run of the GWLFXL model simulating |Section 6 |
|sediments - std run) | |hydrology and sediments | |
|Run GWLFXL (hydrology, | |Make a standard run of the GWLFXL model simulating |Section 6 |
|sediments, nutrients - std | |hydrology, sediments, and nutrients | |
|run) | | | |
|Run GWLFXL (other) |Run from GWLF input files |Run the GWLFXL model reading parameters from files |Section 6.3 |
| |Run from input worksheets |Run the GWLFXL model reading parameters from |Section 6.3 |
| | |spreadsheets | |
| |Run calibration on streamflow |Perform calibration using streamflow data |Section 7 |
| |Run calibration on sediment |Perform calibration using sediment data |Section 7 |
| |Run calibration on nitrogen |Perform calibration using N export data |Section 7 |
| |Run uncertainty analysis (hydrology) |Run uncertainty analysis on the simulated hydrology |Section 8.1 |
| |Run uncertainty analysis (hydrology, |Run uncertainty analysis on the simulated hydrology |Section 8.1 |
| |sediments) |and sediments | |
| |Run uncertainty analysis (hydrology, |Run uncertainty analysis on the simulated hydrology, |Section 8.1 |
| |sediments, nutrients) |sediments, and nutrients | |
| |Run sensitivity analysis (hydrology) |Run sensitivity analysis on the simulated hydrology |Section 8.2 |
| |Run sensitivity analysis (hydrology, |Run sensitivity analysis on the simulated hydrology |Section 8.2 |
| |sediments) |and sediments | |
| |Run sensitivity analysis (hydrology, |Run sensitivity analysis on the simulated hydrology, |Section 8.2 |
| |sediments, nutrients) |sediments, and nutrients | |
|Tools |Generate new template |Create template worksheet for model parameters |Section 9 |
| |Generate GWLF nutrient file |Create nutrient parameter file from “nutrient” |Section 9 |
| | |worksheet | |
| |Generate GWLF transport file |Create transport parameter file from “transport” |Section 9 |
| | |worksheet | |
| |Create nutrient parameter worksheet |Create “nutrient” parameter worksheet from nutrient |Section 9 |
| | |file | |
| |Create transport parameter worksheet |Create “transport” parameter worksheet from transport|Section 9 |
| | |file | |
| |Setup stochastic parameter worksheet |Prepare “stochastic” worksheet for uncertainty and |Section 8 |
| | |sensitivity analysis | |
| |Generate stochastic parameters |Draw random numbers from parameter distributions for |Section 8.1 |
| | |uncertainty analysis | |
| |Generate sensitivity parameters |Generate parameter values for simple sensitivity |Section 8.2 |
| |(simple) |analysis | |
| |Generate sensitivity parameters (batch)|Generate parameter values for batch sensitivity |Section 8.2 |
| | |analysis | |
|Print output |Print transport parameters |Print “transport” worksheet |Section 10 |
| |Print nutrient parameters |Print “nutrient” worksheet |Section 10 |
| |Print tabulated annual results |Print “annual” worksheet |Section 10 |
| |Print tabulated monthly results |Print “monthly” worksheet |Section 10 |
| |Print tabulated daily results |Print “daily” worksheet |Section 10 |
|Help |Help contents |Not available in the current version |Section 11 |
| |Units |Display units for the model variables |Section 11 |
| |Documentation (GWLF users manual) |Open GWLF user’s manual (“GWLFUsersManual.doc”) |Section 11 |
| |Call for help |Display contact information |Section 11 |
| |About GWLFXL (GWLF for EXCEL) |Display developer information |Section 11 |
4.2. Worksheets
The GWLFXL model has 11 default worksheets (Figure 8). An error could occur if any of the default worksheets is missing or named incorrectly. These worksheets are grouped into three categories, each associated with a distinct color.
• Yellow worksheets are for model inputs and parameters. These include:
- “notes and parameters” (standard parameter worksheet, see Section 5.1)
- “transport” (input worksheet for transport parameters, see Section 5.2)
- “nutrient” (input worksheet for nutrient parameters, see Section 5.3)
• Blue worksheets are for model outputs, updated after running a simulation. The user can choose whether specific types of model outputs will be reported to the relevant worksheets or not. The blue worksheets include:
- “annual” (annual output worksheet, see Section 6.2)
- “monthly” (monthly output worksheet, see Section 6.2)
- “daily” (daily output worksheet, see Section 6.2)
- “bycategory” (categorical output worksheet, see Section 6.2)
- “bycategory2” (another categorical output worksheet, see Section 6.2)
- “uncertainty” (worksheet reporting the result of uncertainty and sensitivity analysis, see Section 8)
• Red worksheets are for special features of the model. These include:
- “calibrate” (worksheet for running calibration, see Section 7)
- “stochastic” (worksheet for generating parameter values for uncertainty and sensitivity analysis, see Section 8)
It is important to note that the users can create additional worksheets and reserve them for their use, as long as the above 11 default worksheets are present and named correctly. For example, if it is desired to keep the annual output from previous model run before making a new simulation, the user may create a new worksheet, give it an appropriate name (e.g., “old_annual”), and copy the contents from the “annual” standard worksheet.
5. Model Inputs
GWLFXL is governed by several sets of parameters which describe watershed, land use, and management characteristics among other things. The model also requires daily weather data over the period to be simulated in order to generate watershed hydrology, sediment, and nutrient fluxes. Basic simulation characteristics are specified in the “notes and parameters” worksheet, described in Section 5.1 below. Additional parameters necessary to characterize the watershed are organized into two categories: transport parameters and nutrient parameters. (This is an artifact of historical precedent in the development of the model.) These categories of parameters may be specified either in worksheets (named appropriately) or in text files. The organizational structure of these worksheets or files is described in Sections 5.2 and 5.3 below. Finally, the weather data specifications and file structure, as well as possible sources of such data, are described in Section 5.4 below.
5.1. Specifying simulation options
The user can specify various options through the “notes and parameters” worksheet, as shown in Figure 7. The spreadsheet describes basic simulation characteristics that can be modified depending on the desired type of analysis. Below is a list of all the simulation options provided in the “notes and parameters” worksheet. Note that options that are not implemented in the current version (1.2.1) of the model are dimmed with gray color in the worksheet (Figure 7).
• Number of subwatersheds: This option is designed to handle multiple watersheds using only one nutrient and transport worksheet. This feature is not yet implemented in GWLFXL version 1.2.1, and currently the model can handle only one (sub)watershed at a time.
• Read from file or spreadsheet?: The user can choose to read the transport and nutrient parameters either from text files or from spreadsheets. If it is desired to have the model read these parameters from text files, the user should enter “f” in cell D2, and specify the folder where the files are located in cell D7 and the file names in cells D27 (transport parameters) and D28 (nutrient parameters). If these parameters are read from spreadsheets, enter “s” in cell D2 and set up the transport and nutrient parameters in the “transport” and “nutrient” worksheets, respectively. It is important to note that the text parameter files have the traditional GWLF input file format. Thus some features new to GWLFXL may not be simulated adequately if these features require additional parameters that can only be read from spreadsheets. For example, since parameters for the denitrification process is not included in the traditional GWLF input file, prediction of denitrification rates can only be done by reading from spreadsheets. Likewise, the traditional GWLF model does not have trajectory parameters, and the trajectory simulation can only be performed by reading from spreadsheets (see “use trajectory?” option below).
• Default and last run conditions: This option is designed to permit the user to run a simulation and then use the conditions at the end of the simulation as the initial condition for the next simulation. This feature is not yet implemented in GWLFXL version 1.2.1. Currently, the user can specify the initial conditions for the simulation using the “spinup” option (see “spinup period” and “repeat spinup data?” options below).
• Connectivity matrix: Connectivity matrix is a way to “connect” or “weight” outputs from multiple subwatersheds to obtain an overall watershed output. The matrix may be used either as a simple weighting scheme or to specify inputs into a network to which different retention factors could be applied. This feature is not yet implemented in the current version (1.2.1) of the model, which can handle only one (sub)watershed at a time.
• Spinup period: This option is designed to specify the initial conditions for the simulation. The intent of “spinup period” is to simulate the model for a specified period before actually starting to produce results for output. The idea is to use an initial period to allow the model to equilibrate, so that initial conditions will not matter so much in the simulation. For example, if the user enters “1” to cell D5, the model will run for one year before making actual simulation. Entering “0” means no spinup period.
• Repeat spinup data?: This option determines how the weather data for the spinup period will be read. If the user enters “y” to cell D6, the model will read some weather data to run the model for the spinup period, and then start the simulation from the beginning of the spinup period (i.e., repeat the same weather data from the beginning of the spinup period for the simulation). This choice is useful if the user has only limited weather data and wishes to remove the effect of initial conditions (for example, the user wants to run the model from 1986 but does not have 1985 weather data for the spinup simulation). If the user enters “n” to cell D6, the model will read some weather data and run the model for the spinup period, and then continue to read the data for the actual simulation period. In this case, the user should provide the weather data for the spinup period. For example, if the model will run from 1986 and the spinup period is 1, the user should provide the weather data starting from 1985.
• Default foldername: In cell D7, the user should provide the name of default data folder where the weather data file and transport and nutrient parameter files are located (if these parameters are read from text files; see “read from file or spreadsheet?” option above). The default folder is also where the output file “results.txt” is created after the standard simulation is performed. Since the weather data are always read from a text file, the user should make sure that the correct folder name is provided before running the model.
• Title of simulation: If desired, the user may provide a simulation title in cell D8. This title will appear in the beginning of the output file “results.txt”, which is created after the simulation is performed.
• Number of years of simulation: This option in cell D9 specifies the number of years to run the model. The user should make sure that the provided weather data are long enough to run the model for the specified period.
• Daily report, monthly report, annual report, report by category, and report by category 2: The user can enter either “y” or “n” in cells D10 to D14 to select or deselect the annual, monthly, daily, or categorical reports to the output worksheets. Turning off some reporting options (e.g., daily report) may shorten the simulation time significantly. Details of simulation output are given in the Section 6.2 below.
• Use trajectory?: This feature, newly introduced in GWLFXL, is intended to interpolate (linearly, at this stage) parameter values between initial and final simulation periods. Although conceptually all parameters may change over time during the simulation run, only areas of different land uses are allowed to change in the current version (1.2.1) of the model, affecting various simulation results such as denitrification rates. Since the original version of GWLF doesn’t allow the trajectory option to proceed, the user cannot make the trajectory simulation if the model reads parameters from text files (see “read from file or spreadsheet?” option above). The simulated change of areas of different land uses are reported to various standard output worksheets. For example, if the user enters “y” in cell D15, the change of land use over time will be reported beginning from column AA of the “annual” output worksheet. If the user chooses not to make the trajectory simulation, the initial areas stay the same throughout the simulation period, even though the final areas are specified as model input.
• Use latitude to calc daylength: If the user enters “y” in cell D16, the hours of daylight will be calculated by the model based on the latitude. In this case, the user should provide the latitude of the simulation site in cell D17. If the user enters “n” in cell D16, the daylight length data for the simulation site should be provided as model input.
• Latitude, degrees: If the user chooses to calculate the daylight lengths from the latitude (see “use latitude to calc daylength” option above), the latitude (in degrees) of the simulation site should be provided in cell D17.
• Initial year: Provide the initial year of simulation in cell D20. This information is used for reporting purpose.
• Initial month: Provide the initial month of simulation in cell D21. It should be noted that the simulation run and the summary of results are made on a yearly basis. Thus, for example, if the user starts the simulation from October 1986, the simulation and annual summary are made from Oct 1986-Sep 1987, Oct 1987-Sep 1988, and so on. Also, in this example, if the user wants to give one year of spinup period without repeating the simulation (see “spinup period” and “repeat spinup data?” options above), the weather data from Oct 1985 will need to be provided.
• Transport parameters, nutrient parameters, and weather data filename: Provide the file names for the transport parameters, nutrient parameters, and weather data in cells D27, D28, and D29, respectively. If it is desired to read the transport and nutrient parameters from spreadsheets instead of text files (see “read from file or spreadsheet?” option above), the user can leave cells D27 and D28 empty. However, the name of the weather data file must be provided in cell D29 to run a simulation.
• Transport parameters (final year) and nutrient parameters (final year): The file names provided in cells D31 and D32 are used to interpolate values of various model parameters between initial and final simulation periods when the trajectory simulation is made (see “use trajectory?” option above). This feature is not yet implemented in the current version (1.2.1) of the model, in which only areas of different land uses are allowed to change.
• Default number of realizations: This option in cell D34 is used by the model to specify the number of realizations when generating parameter values for the uncertainty analysis (see Section 8.1 below for generating stochastic parameters).
• Annual or monthly (a or m)?: This option is used to specify how the batch sensitivity results are summarized (see Section 8.2 below for running sensitivity analysis). If the user enters “a” in cell D36, the annual results are summarized in the “uncertainty” worksheet after running batch sensitivity analysis. If the user enters “m”, monthly results are summarized.
• Specific year (or blank for all): When performing a batch sensitivity analysis, the user may choose to summarize the simulation results for a specific year, instead of all years, by entering the year number in cell D37 (see Section 8.2 below for running sensitivity analysis). If this cell is left blank, the simulation results for all years are summarized. Note that the year number starts with 1, not the initial calendar year specified in cell D20 (See “Initial year” option above).
• Specific month (or blank for all): When performing a batch sensitivity analysis, the user may choose to summarize the simulation results for a specific month, instead of all months, by entering the month number in cell D38 (see Section 8.2 below for running sensitivity analysis). If this cell is left blank, the simulation results for all months are summarized. Note that the first month corresponds to the month specified in cell D21 (See “Initial month” option above). Thus, for example, if the user starts the simulation from October, only the December results are summarized when the user enters “3” in cell D38.
5.2. Specifying transport parameters
Transport parameters are used to simulate hydrology, erosion, and sediment yield. Transport parameters can be specified either in the spreadsheet (“transport” worksheet, Figure 9) or as a text file (Figure 10). The transport text file should be stored in the default data folder and the name of the transport file and location of the default folder should be specified in the “notes and parameters” worksheet (see Section 5.1). The text file follows the classic GWLF transport file format. New parameters introduced in the GWLFXL include the fractional groundwater denitrification rate, fractional denitrification loss from each land use type, and the area of each land use type in the final simulation year (Figure 9). These new transport parameters can only be specified from the “transport” worksheet. When running the model from files, these parameters are assumed to be zero and, consequently, the simulated denitrification rates will be zero and there will be no change in areas of different land uses during simulation (see Section 5.1).
[pic]
Figure 9. Transport worksheet.
[pic]
Figure 10. Text file containing transport parameters shown in Figure 9.
Brief description of transport parameters is given below. Detailed discussion of each parameter can be found in the documentation of original GWLF model.
• Number of rural and urban landuses: The number of rural and urban land uses should be specified separately, since the methods for calculating nutrient loads are different between these types. Urban nutrient loads are assumed to be entirely solid phase (based on washoff functions), whereas rural nutrient loads include both solid (based on sediment nutrient concentration) and dissolved (based on runoff nutrient concentration) phases. Detailed description of nutrient sources in GWLF can be found in the original documentation of the model.
• Recession and seepage coefficients: The recession and seepage coefficients specify the proportions of groundwater added to the stream and lost to the deep aquifer, respectively, for each day of simulation. The recession coefficient can be estimated by a standard hydrograph separation technique or using the calibration feature of GWLFXL (see Section 7 for running calibration). Typical values for the recession coefficient range from 0.01 to 0.2. No standard technique is available for estimating the seepage coefficient. The most conservative approach is to assume that the seepage coefficient is zero. Detailed description of recession and seepage coefficients can be found in the original GWLF documentation (page 23).
• Init unsat storage, init sat storage, and init snow: These parameters specify the initial conditions for unsaturated zone soil moisture, shallow saturated zone soil moisture, and snowpack size, respectively. GWLFXL has a “spinup period” option, running the model for a specified period before actually starting to produce results for output, as an alternative to estimating these parameters (see Section 5.1). More discussion on specifying the initial conditions can be found in the original GWLF documentation (page 29).
• Sed delivery ratio: The sediment yield is calculated as the product of erosion (computed using the Universal Soil Loss Equation) and sediment delivery ratio. The sediment delivery ratio is most commonly estimated from the area-based relationship available in Figure B-2 of the original GWLF documentation (page 25).
• Gw denit fraction: This parameter specifies the fractional denitrification loss of groundwater nitrogen (Equations 14 and 15). This parameter may be estimated from fraction of riverine fluxes that is lost due to denitrification. The value should range between 0 and 1. Note that this parameter is newly introduced in GWLFXL and can only be specified by reading inputs from spreadsheet.
• Antecedent rain + melt for initial 5 days: These parameters specify the initial conditions for antecedent water input (rainfall + snowmelt) for the five previous days. The model calculates the runoff curve number as a function of antecedent moisture, as shown in Figure A-1 of the original GWLF documentation (page 8). GWLFXL has a “spinup period” option, running the model for a specified period before actually starting to produce results for output, as an alternative to estimating these parameters (see Section 5.1). The original GWLF documentation (page 29) provides more discussion on specifying the initial conditions.
• ET cover factor: The evapotranspiration cover factor is multiplied by the potential evapotranspiration (estimated as a function of daylight hours, saturated water vapor pressure, and daily temperature) to calculate daily evapotranspiration rate. Although the theoretical range of the cover factor is between 0 and 1, the lower limit is typically around 0.2 to 0.3 as the evapotranspiration includes evaporation of soil water. Suggested values for this parameter can be obtained from Tables B-6, B-7, and B-8 of the original GWLF documentation (pages 21 and 22). More discussion on the evapotranspiration cover factor can be found in the original GWLF documentation (pages 20-22).
• Daylight hrs: The mean monthly values of daylight hours may be provided as model input or calculated by the model as a function of the latitude of the simulation site (see “use latitude to calc daylength” option in Section 5.1). Suggested values for this parameter can be obtained from Table B-9 of the original GWLF documentation (page 22).
• Growing season: The user should enter either “1” or “0” to specify whether each month is growing or dormant season, respectively. The estimated curve number differs between these two seasons, affecting the runoff calculation. Details of curve number estimation are given in the original GWLF documentation (pages 8-9).
• Erosivity coefficient: The erosivity coefficient is used to calculate the rainfall erosivity, which is a function of the erosivity coefficient and rainfall amount (Equation A-12 in the original GWLF documentation). The rainfall erosivity is in turn used to calculate the erosion from source area using the Universal Soil Loss Equation (Equation A-11 in the original GWLF documentation). This parameter can be estimated from a table of rainfall erosivity coefficients (Table B-14 in the original GWLF documentation) and a map of rainfall erosivity zones (Figure B-1 in the original GWLF documentation).
• Initial and final land areas: Trajectory simulation is newly introduced in GWLFXL. If trajectory simulation is desired, the user should specify the final land area for each land use type and enter “y” in cell D15 of the “notes and parameters” worksheet (see “use trajectory?” option in Section 5.1). Note that the final land areas can only be specified by reading inputs from spreadsheet. Final land areas are ignored if the user selects “n” for the “use trajectory?” option.
• Runoff curve no: The runoff curve number is used in runoff calculation (Equations A-6, A-8, and A-9 in the original GWLF documentation). This parameter can be obtained from Tables B-1 to B-5 in the original GWLF documentation, and also from U.S. Soil Conservation Service’s Technical Release No. 55, second edition (attached as Appendix).
• USLE parameters: The USLE parameter is specified as the product of K (soil erodibility factor), LS (topographic factor), C (cover and management factor), and P (supporting practice factor). This parameter is multiplied by the rainfall erosivity (see “erosivity coeff.” above) to calculate erosion from source area (Equation A-11 in the original GWLF documentation). The values of K, C, and P can be obtained from Tables B-10, B-11 and B-12, and B-13 in the original GWLF documentation. The LS can be calculated using Equations B-6 and B-7 in the original GWLF documentation.
• Denit loss fraction: This parameter specifies the fractional denitrification loss from each land use type (Equations 5, 6, 8, 9, 11, and 12). This parameter may be estimated from fraction of nitrogen inputs that is lost due to denitrification. The value should range between 0 and 1. Note that this parameter is newly introduced in GWLFXL and can only be specified by reading inputs from spreadsheet.
5.3. Specifying nutrient parameters
Nutrient parameters are used to simulate nitrogen and phosphorous loading. Nutrient parameters can be specified either in the spreadsheet (“nutrient” worksheet, Figure 11) or as a text file (Figure 12).
[pic]
Figure 11. Nutrient worksheet.
[pic]
Figure 12. Text file containing nutrient parameters shown in Figure 11.
The nutrient text file should be stored in the default data folder and the name of the nutrient file and location of the default folder should be specified in the “notes and parameters” worksheet (see Section 5.1). The text file follows the classic GWLF nutrient file format. New parameters introduced in the GWLFXL include total atmospheric N deposition and dry N deposition fraction (Figure 11). These new nutrient parameters can only be specified from the “nutrient” worksheet. When running the model from files, these parameters are assumed to be zero and, consequently, the simulated wet and dry N depositions will be zero. Brief description of nutrient parameters is given below. Detailed discussion of each parameter can be found in the documentation of original GWLF model.
• Watershed name and watershed number: These parameters are designed to be used when handling multiple watersheds. These parameters are not read by the current version (1.2.1) of GWLFXL, which can handle only one (sub)watershed at a time.
• N and P in sediment: Nitrogen and phosphorus in sediment (mg/kg) are used to calculate solid-phase nutrient loss due to erosion from rural sources. These parameters are estimated as the average soil nutrient content (shown in Figures B-3 and B-4 of the original GWLF documentation) multiplied by an enrichment ratio. The default value for the enrichment ratio is 2.0. For example, if the average soil nitrogen content in the simulated region is estimated to be 0.15 percent, an enrichment ratio of 2.0 will give 0.15 / 100 × 106 × 2 = 3000 mg/kg N in sediment (Figure 11).
• Gw N and P conc: These parameters are nitrogen and phosphorus concentrations in groundwater. The groundwater nutrient concentrations may be estimated from mean concentrations obtained from monthly streamflow samples in watersheds free of point sources. Suggested default values for these parameters are given in Table B-16 of the original GWLF documentation. More discussion can be found in the original documentation of the model (page 32).
• Atmos N dep and dry N dep fraction: Measurements for total N deposition (Equations 1, 2, and 4) may be found on the World Wide Web at . In the absence of better information, one can assume that half the total N deposition is dry deposition (Equations 1, 2, and 4). Note that these parameters are newly introduced in GWLFXL and can only be specified by reading inputs from spreadsheet.
• Number of manured areas: The number of manured areas should be specified if manure is spread during winter months on any rural area. The number of manured areas should not be greater than the total number of rural landuses (Section 5.2), as n manured areas correspond to the first n rural landuses. For example, if there are three rural land use types, CORN, PASTURE, and FOREST (Figure 9), and the number of manured areas is two (Figure 11), it means that the first two landuses, CORN and PASTURE, are manured.
• First and last manure months: When specifying the first and last manure months, it should be noted that the month numbering starts with the first month of the simulation as shown in the “transport” worksheet (Figure 9) or transport parameter file (Figure 10). For example, if the first month of simulation is April and the first and last manure months are 10 and 12, respectively, it means that the manure is spread from January to March (Figure 11), not from October to December.
• N and P for each landuse category: The meanings and units of the parameters in this group depend on the land use category specified in each row. For rural landuses, these are N and P concentrations in runoff water (mg/L). Default concentrations of dissolved nitrogen and phosphorus in agricultural runoff are given in Table B-15 of the original GWLF documentation (page 31). The runoff concentrations from forest areas may be estimated from streamflow nutrient concentrations in a forested watershed (Table B-16 of the original GWLF documentation). For urban landuses, these parameters are urban nutrient accumulation rates (kg/ha/day) used by the washoff function. Default values for urban N and P accumulation rates are provided in Table B-17 of the original GWLF documentation. Finally, if manure is spread during winter months on any rural area, the N and P concentrations in snowmelt runoff (mg/L) should be specified for each manured land. During the manure months, runoff concentrations normally assigned to the specified rural areas are replaced by these values (for example, in Figure 11, the runoff N concentration from the CORN areas will be 12.2 mg/L from January to March, instead of 2.9 mg/L). Default values for the snowmelt runoff concentrations from manured lands can be found in Table B-15 of the original GWLF documentation. More discussion can also be found in the original GWLF documentation (pages 31-32).
• Pt src N and P loads: These parameters are monthly nutrient loads from point sources. Point sources of nitrogen and phosphorus are assumed to be in dissolved form and must be specified as constant mass loads for each month (kg/month). Point source information may be obtained from the data describing municipal and industrial wastewater treatment plants.
• Septic systems: The septic (on-site wastewater disposal) system parameters include the number of people served by four different types of septic systems (normal, short-circuited, ponded, and direct discharge), per capita daily N and P loads in septic tank effluent (g/day), and per capita daily N and P uptake by plants (g/day). The population data may be obtained from local public health officials or through a septic system survey. Table B-18 of the original GWLF documentation provides default values for per capita nutrient loads and uptake. Detailed description of septic system simulation and discussion on parameter estimation can be found in pages 13-14 and 33-34, respectively, of the original GWLF documentation.
• Dsflag: This parameter specifies how the point source nutrient loads and septic system parameters are organized. Three options, 1, 2, or 3, are available. When the dsflag is one, monthly point source nutrient loads (specified in “pt src N and P loads” above) are repeated each year of simulation, and the septic system parameters are followed by the dsflag value, as shown in Figures 11 and 12. When the dsflag is two, the monthly point source nutrient loads should be specified each year of simulation, and the point source information from the second year of simulation to the last year is added after the dsflag value. The septic systems are assumed to be nonexistent when the dsflag is two, and the septic information is not included in the nutrient parameters (see Figures 13 and 15). When the dsflag is three, the monthly point source nutrient loads should again be specified each year of simulation, and the point source information from the second year of simulation to the last year is added after the septic information, which is followed by the dsflag value (see Figures 14 and 16).
[pic]
Figure 13. Nutrient worksheet when dsflag = 2.
[pic]
Figure 14. Nutrient worksheet when dsflag = 3.
[pic]
Figure 15. Text file containing nutrient parameters shown in Figure 13.
[pic]
Figure 16. Text file containing nutrient parameters shown in Figure 14.
5.4. Providing weather data
GWLFXL requires two daily weather data variables to drive a simulation: daily precipitation (cm water/day) and daily average temperature, typically considered as the arithmetic mean of the daily temperature maximum and minimum (ºC). Data should span the entire period of the desired simulation, and if possible, some initial “spinup period” prior to the simulation period to free the simulation of the effects of any spurious initial conditions (see Section 5.1). A year of “spinup” is sufficient to remove the influence of initial conditions. While calendar year simulations are often desired, it is worth considering simulating on the basis of water year (i.e., beginning on October 1, rather than January 1), as this represents a natural hydrological period for Northern hemisphere temperate conditions. Other “water years” appropriate to local latitude, climate, and elevation may be appropriate. Weather data are read from a simple comma-delimited text file containing the temperature and precipitation for each day on a separate line in sequence (Figure 17):
[pic]
Figure 17. Text file containing weather data.
The weather data filename is specified in the “notes and parameters” worksheet in cell D29 (Figure 7). The file should reside in the default data folder for the simulation, which is specified in the “notes and parameters” worksheet in cell D7 (see Section 5.1). For several of our projects, weather data have been obtained from the National Climate Data Center network (). For large watersheds (1000s of km2) several weather stations are typically found. It is important to have at least one reasonably complete weather dataset representative of the weather of each watershed being simulated, and it is desirable to have several which can be spatially averaged for very large watersheds. Weather data can be averaged spatially in various ways. Experience shows that while the details of the method affect the outcome of simulation, especially for erosion and sediment transport which is particularly sensitive to the magnitude of precipitation, they are not the among most sensitive aspects of simulating watershed fluxes. The decision criteria for whether a station represents watershed weather should consider whether it falls within (or near to) watershed boundaries, and the completeness of the weather record. As an example, for an analysis of several watersheds in the Northeastern US, candidate stations for each watershed were selected from the 2,928 weather stations in the NCDC database for this region. While the stations could have first been screened on the basis of whether they fall within watershed boundaries, an alternative criterion was used based on generating Thiessen polygons for the network of weather stations using ArcView™ 3.2. Data from stations with polygons intersecting a watershed and with > 95% complete records (daily temperature and precipitation) were averaged to obtain a representative weather record for each watershed. Missing temperature data for each station were replaced with averages of the daily records immediately preceding and following the missing interval; dates with missing precipitation values were assigned zero precipitation, under the assumption that it is more important to maintain the episodic nature of precipitation and suffer a possible negative bias than to replace missing precipitation data with averages.
6. Running GWLFXL
6.1. Making a standard run
Clicking on the “GWLFXL” tab reveals the pull-down menu (Figure 8). The user can make a standard run of the GWLFXL model with parameter values as specified in the “notes and parameters” worksheet by clicking on any of the first three menu options (Table 1). Three different types of standard run can be made. Clicking on the first option “Run GWLFXL (hydrology - std run)” simulates hydrology only. The second option “Run GWLFXL (hydrology, sediments - std run)” simulates hydrology and sediment transport, and the third option “Run GWLFXL (hydrology, sediments, nutrients - std run)” simulates hydrology, sediment transport and nutrient flux. Before running the model, make sure that the name of the default folder which contains data files required to run the model is correctly specified in cell D7 of the “notes and parameters” worksheet. Also, the text file containing weather data must be correctly specified in cell D29 of the “notes and parameters” worksheet, since the weather data must be read from a text file. When running the model from text files, the transport and nutrient filenames should be specified in cells D27 and D28 of the “notes and parameters” worksheet, respectively. The user can choose to read the transport and nutrient parameters from worksheets instead of from text files (see Section 5.1 for specifying simulation options). In case the user experiences any difficulty in making a standard run, troubleshooting information is available in the Appendix below.
6.2. Examining simulation output
Results of a standard run of the GWLFXL model can be reported to the standard output worksheets (“annual”, “monthly”, “daily”, “bycategory”, and “bycategory2”). The user can enter “y” or “n” in cells D10 to D14 of the “notes and parameters” worksheet to select or deselect annual, monthly, daily, or categorical reports to output worksheets. For example, if the user enters “y” in cell D10 (“daily report”), the daily simulation result will be reported to the “daily” worksheet. The daily output worksheet (Figure 18) reports the following results each day of simulation (Table 2):
Table 2. Output variables reported to daily worksheet.
|Column |Heading |Reported Variable |Unit |Related Standard Run |
|A |day |day number | | |
|B |month |month name | | |
|C |precip |precipitation |cm |hydrology |
|D |rain |rainfall |cm |hydrology |
|E |melt |snowmelt |cm |hydrology |
|F |water |rainfall + snowmelt |cm |hydrology |
|G |snow |snow |cm |hydrology |
|H |qtotal |daily runoff |cm |hydrology |
|I |temp |air temperature |ºC |hydrology |
|J |amc5 |5-day antecedent rainfall + snowmelt |cm |hydrology |
[pic]
Figure 18. Daily output worksheet.
As Table 2 shows, only the hydrology simulation result is reported to the daily output worksheet. Thus, the output variables reported to the “daily” worksheet is the same regardless of the type of the standard run. The monthly output worksheet (Figure 19) reports the following results each month of simulation (Table 3):
Table 3. Output variables reported to monthly worksheet.
|Column |Heading |Reported Variable |Unit |Related Standard Run |
|A |year |year number | | |
|B |calendar year |calendar year | | |
|C |month |month number | | |
|D |month name |month name | | |
|E |days no |number of days in the month | | |
|F |precip |precipitation |cm |hydrology |
|G |et |evapotranspiration |cm |hydrology |
|H |runoff |monthly runoff |cm |hydrology |
|I |streamflow |streamflow |cm |hydrology |
|J |groundwater |groundwater flow |cm |hydrology |
|K |erosion |erosion from source area |106 kg |sediments |
|L |sediment yield |monthly sediment yield |106 kg |sediments |
Table 3 (Continued).
|Column |Heading |Reported Variable |Unit |Related Standard Run |
|M |total N flux |total streamflow N flux (dissolved + particulate) |103 kg |nutrients |
|N |diss N flux |dissolved N flux (rural runoff + deposition + |103 kg |nutrients |
| | |groundwater + point source + septic) | | |
|O |part N flux |particulate N flux (rural sediment + urban washoff) |103 kg |nutrients |
|P |gw N flux |groundwater N flux |103 kg |nutrients |
|Q |pt src N flux |point source N flux |103 kg |nutrients |
|R |septic N flux |septic N flux |103 kg |nutrients |
|S |wet dep N flux |wet deposition N flux |103 kg |nutrients |
|T |dry dep N flux |dry deposition N flux |103 kg |nutrients |
|U |total denit flux |total denitrification flux |103 kg |nutrients |
|V |gw denit flux |groundwater denitrification flux |103 kg |nutrients |
|W |total P flux |total streamflow P flux (dissolved + particulate) |103 kg |nutrients |
|X |diss P flux |dissolved P flux (rural runoff + groundwater + point |103 kg |nutrients |
| | |source + septic) | | |
|Y |part P flux |particulate P flux (rural sediment + urban washoff) |103 kg |nutrients |
|Z |gw P flux |groundwater P flux |103 kg |nutrients |
|AA |pt src P flux |point source P flux |103 kg |nutrients |
|AB |septic P flux |septic P flux |103 kg |nutrients |
[pic]
Figure 19. Monthly output worksheet.
Depending on the particular setting of the simulation environment, additional simulation results may be reported to the monthly output worksheet. For example, if the user enters “y” in cell D15 (“use trajectory?”) of the “notes and parameters” worksheet, the change of land use over time will be reported beginning from column AC (see Section 5.1 for specifying simulation options). The annual output worksheet (Figure 20) reports the following results each year of simulation (Table 4):
Table 4. Output variables reported to annual worksheet.
|Column |Heading |Reported Variable |Unit |Related Standard Run |
|A |year |year number | | |
|B |calendar year |calendar year at start | | |
|C |precip |precipitation |cm |hydrology |
|D |et |evapotranspiration |cm |hydrology |
|E |runoff |yearly runoff |cm |hydrology |
|F |streamflow |streamflow |cm |hydrology |
|G |groundwater |groundwater flow |cm |hydrology |
|H |erosion |erosion from source area |106 kg |sediments |
|I |sediment yield |yearly sediment yield |106 kg |sediments |
|J |total N flux |total streamflow N flux (dissolved + particulate) |103 kg |nutrients |
|K |diss N flux |dissolved N flux (rural runoff + deposition + |103 kg |nutrients |
| | |groundwater + point source + septic) | | |
|L |part N flux |particulate N flux (rural sediment + urban washoff) |103 kg |nutrients |
|M |gw N flux |groundwater N flux |103 kg |nutrients |
|N |pt src N flux |point source N flux |103 kg |nutrients |
|O |septic N flux |septic N flux |103 kg |nutrients |
|P |wet dep N flux |wet deposition N flux |103 kg |nutrients |
|Q |dry dep N flux |dry deposition N flux |103 kg |nutrients |
|R |total denit flux |total denitrification flux |103 kg |nutrients |
|S |gw denit flux |groundwater denitrification flux |103 kg |nutrients |
|T |total P flux |total streamflow P flux (dissolved + particulate) |103 kg |nutrients |
|U |diss P flux |dissolved P flux (rural runoff + groundwater + point |103 kg |nutrients |
| | |source + septic) | | |
|V |part P flux |particulate P flux (rural sediment + urban washoff) |103 kg |nutrients |
|W |gw P flux |groundwater P flux |103 kg |nutrients |
|X |pt src P flux |point source P flux |103 kg |nutrients |
|Y |septic P flux |septic P flux |103 kg |nutrients |
|Z |air temperature |annual air temperature |ºC |hydrology |
In addition, the annual averages of these variables over the whole simulation period are reported in the first four rows of the annual output worksheet (Figure 20). Again, if the user enters “y” in cell D15 (“use trajectory?”) of the “notes and parameters” worksheet, the land use change over time will be reported beginning from column AA.
[pic]
Figure 20. Annual output worksheet.
The yearly simulation results can be summarized in two categorical output worksheets. The land area, runoff, erosion, total and dissolved N fluxes, and total and dissolved P fluxes are reported in tables broken down by land use classes in the “bycategory” worksheet (Figure 21), whereas these are broken down by simulated variables in the “bycategory2” worksheet (Figure 22). In both the categorical worksheets, the annual averages over the whole simulation period are reported in the first five rows. If desired, any output worksheet can be renamed and stored for comparisons with simulations of alternative scenarios. The user can also create various plots of simulated variables on the standard output worksheets (Figure 1). If the model is run again, the plot will be regenerated with the new values of the variables.
[pic]
Figure 21. Categorical output worksheet by land use classes.
[pic]
Figure 22. Categorical output worksheet by simulated variables.
Each time a standard run is made, a text file “results.txt” is created in the default folder where the weather data and other parameter files are located (Figure 23). The text file contains basic simulation information, such as the simulation title, number of simulation years, number of rural, urban, and total landuses, and the type of simulation (1 = hydrology; 2 = hydrology and sediments; 3 = hydrology, sediments, and nutrients), as well as monthly streamflow, precipitation, evapotranspiration, groundwater flow, runoff, erosion, sediment yield, groundwater N flux, groundwater P flux, dissolved N flux, total N flux, dissolved P flux, and total P flux. The output text file also contains the simulated yearly runoff, erosion, dissolved N flux, total N flux, dissolved P flux, and total P flux for each land use class.
[pic]
Figure 23. Output text file “results.txt” opened with Excel.
6.3. Secondary pulldown menus
As described in Section 6.1, clicking on the “GWLFXL” tab reveals the pull-down menu (Figure 8), and the first three menu options are used to make a standard run of the GWLFXL model. The remaining options reveal secondary pulldown menus (Table 1). The first secondary pulldown menu, “Run GWLFXL (other)”, enables the user to make special purpose simulations alternative to the standard run, such as calibration (see Section 7 below), uncertainty analysis (see Section 8.1), and sensitivity analysis (see Section 8.2). The first item of the “Run GWLFXL (other)” pulldown menu, “Run from GWLF input files”, specifies that the model be run using conventional GWLF format parameter files. This is equivalent to running the model with cell D2 of the “notes and parameters” worksheet specified as “f” (Section 5.1). If this option is selected, the files are read from specified default folder and the nutrient and transport parameters are written to the “nutrient” and “transport” parameter worksheets, respectively. The second item, “Run from input worksheets”, specifies that the model be run using parameters read from the “nutrient” and “transport” parameter worksheets. This is equivalent to running the model with cell D2 of the “notes and parameters” worksheet specified as “s”. The following set of three options in the “Run GWLFXL (other)” pulldown menu specifies that the model be run in calibration mode (Section 7). The next and last sets of three options are used for the uncertainty (Section 8.1) and sensitivity (Section 8.2) analyses, respectively. The calibration, uncertainty, and sensitivity analyses require that the model be run multiple times with varying parameter values.
7. Running Calibration
The calibration feature of GWLFXL makes use of a robust feature of Excel, Solver, to fit one or more parameters to monthly datasets specified in the “calibrate” worksheet (Figure 24):
[pic]
Figure 24. “Calibrate” worksheet.
This worksheet contains 3 groups of information: parameters to be varied (columns A to C), objective functions (sum of squared deviations from data) to be minimized (columns D to F), and monthly data to calibrate the model (columns G to K). In the current version (1.2.1) of the model, three types of data, streamflow (column I), sediment yield (column J), and total N flux (column K), can be used for calibration. To perform the calibration, first the user should select parameters to vary by entering “y” in the “include?” column (column C). Up to 5 parameters may be selected out of 15 model parameters:
• Recession coefficient: recession coefficient (Section 5.2)
• Seepage coefficient: seepage coefficient (Section 5.2)
• Init unsat storage: initial water content in the unsaturated zone (Section 5.2)
• Init sat storage: initial water content in the saturated zone (Section 5.2)
• Init snow: initial snow (Section 5.2)
• Sed delivery ratio: sediment delivery ratio (Section 5.2)
• ET cover factor multiplier: multiplier for evapotranspiration cover factors (Section 5.2)
• Erosivity coefficient multiplier: multiplier for erosivity coefficients (Section 5.2)
• N in sediment: nitrogen in sediment (Section 5.3)
• P in sediment: phosphorus in sediment (Section 5.3)
• Gw N conc: groundwater N concentration (Section 5.3)
• Gw P conc: groundwater P concentration (Section 5.3)
• Atmos N dep: total atmospheric N deposition (Section 5.3)
• Dry N dep fraction: dry N deposition fraction (Section 5.3)
• Gw denit fraction: groundwater denitrification fraction (Section 5.2)
At least one model parameter should be selected. Otherwise, a warning message indicating that the objective function cannot be evaluated will be shown. The starting value for each of the selected model parameters should be provided in the “current value” column (column B). User’s best guess or default value, if any, for the parameter may be used as the starting value. If the simulation that will be used for calibration (streamflow, sediment yield, or total N flux) is sensitive to the parameter, the specific choice of starting value usually doesn’t affect the calibration result. If the parameters are not selected, their values are not read from column B but from standard text files or spreadsheets as specified by the user (see Section 5.1 for specifying simulation options). Monthly data to calibrate the model should be provided in the appropriate column (see Table 3 for description of monthly output variables):
• Column I: monthly streamflow (cm)
• Column J: monthly sediment yield (106 kg)
• Column K: monthly total N flux (103 kg)
The monthly data must be provided starting from the first month of the first year of simulation period. Note that the month numbering starts with the first month of the simulation as shown in the “transport” worksheet (e.g., “APR” in Figure 9) or transport parameter file (e.g., “APR” in Figure 10). For the months when the measured data are not available, the cells should be left blank. (Do not use 0 or -9999 to indicate missing data.) After providing the calibration dataset, the user can run the calibration by choosing one of three calibration options (Table 1): streamflow (“Run calibration on streamflow” in the “Run GWLFXL (other)” pulldown menu), sediment yield (“Run calibration on sediment”), and total N flux (“Run calibration on nitrogen”). As the optimization proceeds, progressive iterations are displayed in the lower-left corner of the Excel window (Figure 25):
[pic]
Figure 25. Calibration process displayed in Excel window.
During the calibration process, the model is driven by the Solver, and the parameter(s) being fit are varied systematically until a result which best fits the dataset is found. When the fitting procedure is complete, the Solver displays a popup window, which reports whether the procedure was successful, and permits the user to select optional reports (Figure 26):
[pic]
Figure 26. Solver results window.
If the user selects “Keep Solver Solution”, the new best-fit parameter values are displayed in column B, replacing the original starting values of selected model parameters. The “Restore Original Values” will cancel the calibration and keep the starting values. The Solver also provides some optional reports written into new worksheets. Figure 27 below is an example of spreadsheet generated after selecting the “Answer” report:
[pic]
Figure 27. Optional report generated by Solver.
After running the calibration, the sum of squared deviations from data will be updated in the appropriate row of the column E (second row for the streamflow, third row for the sediment yield, and fourth row for the total N flux). The objective function used for the calibration will be shown in the column F without an equal sign (“=”). For example, the objective function used for calibrating the streamflow dataset shown in Figure 24 should read as (cell F2):
objfunc($I$3:$I$26,1,$B$2:$B$16)
If desired, the user may check that the objective function is specified without any error. The first argument of the objective function ($I$3:$I$26) specifies the range of calibration dataset, the second argument (1) specifies the type of model run (1 = hydrology; 2 = hydrology and sediments; 3 = hydrology, sediments, and nutrients), and the third argument ($B$2:$B$16) specifies the range containing starting values of model parameters.
In summary, the calibration feature of GWLFXL makes use of a robust feature of Excel, Solver, to fit one or more parameters to monthly data. In the current version (1.2.1) of the model, up to 5 parameters can be selected to be fit simultaneously, from 9 transport parameters and 6 nutrient parameters. Others will be added as the model is further developed. Currently, streamflow, sediment yield, or total N flux can be fit. While the calibration process is imperfect, due to the complex, multiple-optimum nature of the objective function, and sometimes requires multiple attempts by the user to obtain an “optimal” parameter value, preliminary testing of the calibration procedure suggests that the feature is useful and can provide good parameter estimates from reasonable initial guesses.
8. Uncertainty and Sensitivity Analysis
8.1. Running uncertainty analysis
The parameter uncertainty mode is a set of procedures which permit the user to: (1) select a set of model parameters, (2) assign probability distributions to each of the parameters independently, (3) sample a selected number of values from each of the parameter distributions, (4) repeat the simulation for each realization of the parameter set, and (5) calculate means and standard deviations of model variables over all realizations of the parameter set. To start an uncertainty analysis, first the user needs to generate a list of model parameters in the “stochastic” worksheet by clicking on “GWLFXL > Tools > Setup stochastic parameter worksheet”. This will fill the first three columns of the “stochastic” worksheet (Figure 28):
[pic]
Figure 28. “Stochastic” worksheet.
The first column (column A) indicates the month of seasonal parameters or other descriptive information. The second column (column B) indicates the parameter name, and the third column (column C) provides the default values of the parameters read from “nutrient” and “transport” spreadsheets (not from text files even if cell D2 of the “notes and parameters” worksheet is specified as “f”). The fourth and fifth columns provide the user-specified values of the parameters necessary to define the distribution. The fourth column (column D) typically contains the standard deviation of the parameter. In the present version (1.2.1), the fifth column (column E) is blank, although more sophisticated distributions requiring additional parameters could make use of this column. The sixth column (column F) contains a descriptor for the type of distribution desired. Valid distribution types include:
• “N”: normal distribution
• “LN”: lognormal distribution
• “TN”: truncated normal distribution
• “U”: uniform distribution
• “G”: gamma distribution
• “EXP”: exponential distribution
In the case of normal, lognormal, and gamma distributions, the samples drawn from these probabilistic distributions will have the mean of the first parameter in column C and the standard deviation of the second parameter in column D. The truncated normal distribution has the same shape as the normal distribution, except that the samples are taken only from the positive part. Thus, the first and second parameters no longer represent the actual mean and standard deviation of the truncated normal distribution, respectively. For the uniform distribution, the first and second parameters represent the lower and upper ends of the distribution range, respectively. The exponential distribution has only one parameter, which represents both the mean and standard deviation of the distribution. Subsequent columns contain the values of the model parameters drawn from the specified distributions, up to a maximum number of 250 realizations. To generate the values for each of the specified parameters, the user should click on “GWLFXL > Tools > Generate stochastic parameters”. For each parameter, if either the distribution type in column F is left blank or the second distribution parameter in column D is zero or left blank, then the parameter will be considered constant, and the value given in column C will be used in all replicate runs. (This means that the user should provide any non-zero value in column D when it is desired to generate exponential distribution, even though the value will not be used.) The user will be prompted to provide the number of desired samples to be drawn from the parameter distributions (Figure 29). The default value is specified in cell D34 of the “notes and parameters” worksheet (see Section 5.1 for specifying simulation options).
[pic]
Figure 29. Input window for iteration number.
Figure 30 below is an example of stochastic parameter generation, assuming that the recession coefficient, initial water content in the saturated zone, and groundwater denitrification fraction have Uniform (0.1, 0.3), Exponential (3.0), and Lognormal (0.4, 0.6) distributions, respectively. All other parameters are assumed to be constant.
[pic]
Figure 30. Generating stochastic parameters.
After generating stochastic parameters, the user can initiate the simulation process for the uncertainty analysis by clicking on “GWLFXL > Run GWLFXL (other)” and selecting one of the three options, depending upon whether hydrology (“Run uncertainty analysis (hydrology)”), sediment yield (“Run uncertainty analysis (hydrology, sediments)”), or nutrient fluxes (“Run uncertainty analysis (hydrology, sediments, nutrients)”) are desired. After running the uncertainty analysis, annual values of means and standard deviations of simulated variables over all replicate runs are displayed in the “uncertainty” worksheet (Figure 31). Most of the variables available in the “annual” worksheet (Table 4) are reported to the “uncertainty” worksheet, as well as a brief summary of simulation information such as iteration number, simulation time (in seconds), type of analysis, and list of variables that are varied. The user can create various plots of the variables reported on the worksheet (Figure 31). If the uncertainty analysis is run again, the plot will be regenerated with the new values of the variables. In summary, the parameter uncertainty analysis feature of GWLFXL permits relatively rapid analysis of the effect of parameter uncertainty on simulation uncertainty. Currently, any model parameters can be specified, including those for which such analysis is not meaningful. Future versions will incorporate a more sophisticated parameter selection process. Currently, the statistical sampling of each model parameter is assumed independent of all other parameters. If parameter interdependence is desired, then the user could generate parameter values using other means and manually specify parameter values in the “stochastic” worksheet. Similarly, other statistical distributions could be used by manually entering the appropriate parameter values into the “stochastic” worksheet.
[pic]
Figure 31. “Uncertainty” worksheet reporting results of uncertainty analysis.
8.2. Running sensitivity analysis
GWLFXL 1.2.1 offers two different ways of running sensitivity analysis: simple and batch. The simple sensitivity analysis feature of GWLFXL is a variation of the uncertainty analysis, where each of the model parameters may be given three different values (minimum, best estimate, and maximum) instead of random numbers drawn from probabilistic distributions. To perform the simple sensitivity analysis, first the user should generate a list of model parameters in the “stochastic” worksheet by clicking on “GWLFXL > Tools > Setup stochastic parameter worksheet”, in the same way as in the uncertainty analysis (see Section 8.1 above for generating stochastic parameters). Then the user should specify deviation from the best estimate for each of the selected parameters in column D of the “stochastic” worksheet. To generate values for each of the specified parameters, click on “GWLFXL > Tools > Generate sensitivity parameters (simple)”. Figure 32 below is an example of simple sensitivity parameter generation, created by perturbing the recession coefficient by 0.05 from its best estimate 0.2, while keeping all other parameters constant. The first (column G), second (column H), and third (column I) columns of the sensitivity parameters are the best estimate, minimum, and maximum values, respectively. The column displaying the distribution type (column F) is automatically specified as “s” to indicate the sensitivity analysis. The process of running the model and displaying the simulation result is the same as that in the uncertainty analysis (see Section 8.1 above).
[pic]
Figure 32. Generating simple sensitivity parameters.
The batch sensitivity analysis feature of GWLFXL, newly introduced in version 1.2.1, allows the user to run the sensitivity analysis for all available parameters of the model, in contrast to the simple sensitivity analysis described above, which runs the analysis only for one parameter at a time. The batch sensitivity analysis permits all parameters to be analyzed, by running the model repeatedly while varying each parameter, one at a time, above and below baseline values at the user-specified level. The model makes 2p+1 realizations, where p is the number of active parameters being evaluated. (The 2p+1 corresponds to a simulation for each lower and upper value for each parameter, typically corresponding to ±10% of a baseline value, plus single baseline simulation in which all parameters take their default values, determined from the transport and nutrient worksheets.) The model iterates through each parameter, first choosing the lower value, then the upper, with all other parameters set at baseline values, for a total of 2p+1 simulations. The “notes and parameters” worksheet (Figure 7) has 3 additional entries for the batch sensitivity analysis, corresponding to whether an annual or monthly sensitivity analysis is being conducted (see “Annual or monthly” option in Section 5.1), and whether a particular year (see “Specific year” option in Section 5.1) or month (see “Specific month” option in Section 5.1) is being specified for the analysis, or the full period of simulation. Cell D36 specifies whether sensitivity of annual or monthly resolution is to be performed (“a” or “m”, respectively). By default, the full period of the simulation will be used to estimate either average annual or average monthly values (and their standard deviations) over the period of the simulation. However, if an entry is made in cell D37, variables for only this year will be used in the sensitivity analysis (i.e., if an annual analysis is requested, only the annual value for the year will be used; if a monthly analysis is requested, the 12 months of the year will be used, unless a particular month is specified in cell D38). If no year is specified, but a month is specified in cell D38, the average for the month over all years simulated is used. Note that years are specified as year of simulation, not calendar or water years (i.e., one through the number of years simulated). Months are specified from 1 to 12, relative to the initial month of the simulation. To perform the batch sensitivity analysis, first the user should generate a list of model parameters in the “stochastic” worksheet by clicking on “GWLFXL > Tools > Setup stochastic parameter worksheet”, in the same way as in the uncertainty analysis (see Section 8.1 above for generating stochastic parameters). Then the user should generate values for all parameters, by clicking on “GWLFXL > Tools > Generate sensitivity parameters (batch)”. An input window asking the magnitude of perturbation (in percent) will be displayed (Figure 33):
[pic]
Figure 33. Input window for magnitude of perturbation.
Figure 34 below is an example of batch sensitivity parameter generation, created by perturbing all the parameters by 10 percent from their best estimates. Again, the first (column G), second (column H), and third (column I) columns of the sensitivity parameters are the best estimates, minimum, and maximum values, respectively. All the cells in column F displaying the distribution type are automatically specified as “s” to indicate the sensitivity analysis. After generating batch sensitivity parameters, the user can initiate the simulation process for the sensitivity analysis by clicking on “GWLFXL > Run GWLFXL (other)” and selecting one of the bottom three options, depending upon whether hydrology (“Run sensitivity analysis (hydrology)”), sediment yield (“Run sensitivity analysis (hydrology, sediments)”), or nutrient fluxes (“Run sensitivity analysis (hydrology, sediments, nutrients)”) are desired. After running the batch sensitivity analysis, simulation results are summarized in the “uncertainty” worksheet (Figure 35). In addition to reporting the annual or monthly averages and standard deviations, the model calculates sensitivities of simulated variables for the upper and lower limits and reports them to the “uncertainty” worksheet. Sensitivity of a variable, y, to a parameter x, is evaluated by examining the effect of a change of x on the response of y relative to baseline values. The sensitivity here is defined as the proportional change of variable y, relative to baseline yb, divided by the proportional change in parameter x, relative to baseline value xb:
[pic] (16)
For example, if a 10% change in parameter x relative to its baseline results in a 10% change in y relative to its baseline, then S=1. Smaller responses yield values less than 1 and larger responses are greater than 1.
[pic]
Figure 34. Generating batch sensitivity parameters.
[pic]
Figure 35. “Uncertainty” worksheet reporting results of batch sensitivity analysis.
In summary, the sensitivity analysis feature of GWLFXL is useful in determining directions for model improvements, i.e., in determining approaches for improving agreement with observations, as well as for inferring effective management options. Here we are mainly concerned with the effect of changing a single parameter at a time, though a generalization of the above definition permits evaluating the sensitivity of multiple parameters simultaneously. Note that, in general, the sensitivity is not constant for all regions of parameter space or for all values of model variables, so it is important to establish what the values of variables and parameters are where the sensitivity of a particular parameter is evaluated. Determining the regions of parameter space where a particular parameter is especially sensitive may provide important clues to model behavior. Also, in general, the sensitivity of the parameter depends on the magnitude and sign of the parameter perturbation, i.e., the sensitivity calculation is different for the response to a 10% deviation from baseline values than for a 20% deviation, even though the magnitude of the deviation is taken into account in the calculation; similarly, the response to -10% deviation may not be symmetric to the response of a +10% deviation. (If the model is continuous in its parameters, then the sensitivity can be defined in terms of partial derivatives with respect to parameters instead of finite deviations, in which case this is not an issue.)
9. “Tools” Pulldown Menu
The “Tools” item brings up the secondary pulldown menu with options to generate parameter files and worksheets useful for analyses (Figure 36):
[pic]
Figure 36. “Tools” pulldown menu.
The first item generates a “template” worksheet (a single worksheet containing information corresponding to multiple watersheds or scenarios), copied from the “nutrient” and “transport” worksheets. The template worksheet can be created by clicking on “GWLFXL > Tools > Generate new template”. The following window will appear if the “template” worksheet already exists (Figure 37):
[pic]
Figure 37. Input window for creating template worksheet.
Entering “y” will delete the existing worksheet. Otherwise, the existing worksheet will be given a generic name (e.g., “sheet 15”). After providing the number of subwatersheds, maximum number of landuse types, and maximum number of manured areas, a new “template” worksheet will be created (Figure 38). The user can tailor this information as needed and copy the revised parameter set to the appropriate worksheets for simulation.
[pic]
Figure 38. “Template” worksheet.
The next two items of the “Tools” pulldown menu, “Generate GWLF nutrient file” and “Generate GWLF transport file”, specify the creation of conventional GWLF parameter files from the parameter information in the “nutrient” and “transport” worksheets, respectively. These options could be useful for exchanging parameter information with groups that use other implementations of GWLF. By default, the nutrient and transport files will be created as “nutrient.txt” and “transport.txt”, respectively. The user may provide different names if these files already exist (Figure 39):
[pic]
Figure 39. Input window for creating GWLF nutrient file.
Note that the following parameters in the “nutrient” and “transport” worksheets are newly introduced in GWLFXL and will not be written to conventional GWLF parameter files:
• Atmos N dep: total atmospheric N deposition (Section 5.3)
• Dry N dep fraction: dry N deposition fraction (Section 5.3)
• Gw denit fraction: groundwater denitrification fraction (Section 5.2)
• Final land area: final land area (Section 5.2)
• Denit loss fraction: fraction of denitrification loss from each land use type (Section 5.2)
The fourth and fifth items of the “Tools” pulldown menu, “Create nutrient parameter worksheet” and “Create transport parameter worksheet”, specify the creation of “nutrient” and “transport” parameter worksheets, respectively, by reading parameters from GWLF parameter files (“nutrient.txt” and “transport.txt”, respectively). Again, these options could be useful for exchanging parameter information with groups that use other implementations of GWLF. New parameters introduced in GWLFXL (listed above) are not provided in conventional GWLF parameter files and must be entered manually by the user. If the “nutrient” and “transport” parameter worksheets already exist, the user may choose to provide different names (Figure 40):
[pic]
Figure 40. Input window for creating nutrient parameter worksheet.
The final 3 items of the “Tools” pulldown menu (“Setup stochastic parameter worksheet”, “Generate stochastic parameters”, and “Generate sensitivity parameters”) are concerned with setting up the “stochastic” worksheet for uncertainty and sensitivity analysis. They are described in detail in Section 8 above.
10. “Print Output” Pulldown Menu
The “Print output” item brings up the secondary pulldown menu with options to print model parameters or simulation output from worksheets (Figure 41):
[pic]
Figure 41. “Print output” pulldown menu.
The first two items, “Print transport parameters” and “Print nutrient parameters”, permit printouts of the parameters given in the “transport” and “nutrient” worksheets, respectively. For example, clicking on “GWLFXL > Tools > Print transport parameters” will bring up the “print preview” mode for the “transport” worksheet, allowing the user to tailor the printout as desired (Figure 42):
[pic]
Figure 42. Print preview mode for transport worksheet.
The last three items, “Print tabulated annual results”, “Print tabulated monthly results”, and “Print tabulated daily results”, permit printouts of model output as organized in the “annual”, “monthly”, and “daily” worksheets, respectively. These printout options are obviously only conveniences. Alternatively, the user may choose to printout any selection from any worksheet by using the “Print Preview” option from the “File” menu.
11. “Help” Pulldown Menu
The “Help” pulldown menu contains some useful information for the user, such as units of simulated variables, developer’s name, and contact information, although a full-blown help feature is not yet available in the current version (1.2.1) of the model. Clicking on “GWLFXL > Help > Documentation (GWLF users manual)” will open the user’s manual for the original GWLF model (Figure 43), provided that the document file (“GWLFUsersManual.doc”) has been downloaded from the website with the model and is located in the default data folder specified in cell D7 of the “notes and parameters” worksheet (see Section 5.1 for specifying default folder name):
[pic]
Figure 43. GWLF user’s manual.
12. VBA Implementation
The GWLFXL model, coded in Visual Basic for Applications (VBA), is accessible by clicking on “Tools > Macro > Visual Basic Editor” (note that the code is currently password protected to prevent tampering with the downloadable version), as shown in Figure 44 below:
[pic]
Figure 44. GWLFXL VBA code.
The code is organized into four modules:
• Model: contains most of the simulation code
• Optimization: contains code used in calibration mode
• Printout: contains code handling various printout options
• Stochastic: contains code used in uncertainty and sensitivity analysis
For further information including a request for the password, contact Dennis P. Swaney at dps1@cornell.edu or visit .
Appendix A: Troubleshootings
A.1. Opening GWLFXL model
The GWLFXL model is developed in the Microsoft Office 2003 environment (version 11.0) and uses the Microsoft Excel Solver Add-in. The user may run into problems when trying to open the model for the first time using different versions of Excel. Typically, when there is an error in opening the model, the following error message is displayed (Figure 45), instead of the message box shown in Figure 6:
[pic]
Figure 45. Message box indicating an error in opening the model.
Even though an error occurs while opening the model, the model workbook is still displayed as shown in Figure 8. However, the user will not find a new menu “GWLFXL” on the left of the “Help” menu. (Note that the “GWLFXL” menu is not shown when a chart is activated, since the menu is added to the Worksheet Menu Bar, not the Chart Menu Bar. Thus, if no error message is displayed when the model workbook is opened but the “GWLFXL” menu cannot be found, try clicking on any cell in the spreadsheet and the menu may appear.) When the user is using different versions of Excel, it is a good idea to check if there is any missing or outdated reference used by the Microsoft Visual Basic Editor. This can be checked by clicking on “Tools > Macro > Visual Basic Editor”. The Visual Basic Editor for the model “GWLFXL1.2.1.xls” will be opened as shown in Figure 46:
[pic]
Figure 46. Visual Basic Editor for GWLFXL model.
Double-click on “VBAProject (GWLFXL1.2.1.xls)” in the Project Explorer sub-window located on the upper left side of the Visual Basic Editor. Note that the model is password protected. (Contact Dennis P. Swaney at dps1@cornell.edu for password request.) Next, click on “Tools > References…” from the menu and the “References” window will be displayed. For example, if the user downloaded the model from the World Wide Web and tried to open it in the Microsoft Office XP environment (version 10.0), the “References” window may look like below (Figure 47):
[pic]
Figure 47. VBA references window.
The user should remove all the references that start with “MISSING:…” (uncheck the checkboxes) and add required references to the list if they are not checked. The following is a list of required references. (The XXs in the list represent the version number specific to the Microsoft Office environment. The Microsoft Office 2003, XP, 2000, and 97 have version numbers of 11.0, 10.0, 9.0, and 8.0, respectively.)
• Visual Basic For Applications
• Microsoft Excel XX Object Library
• Microsoft Office XX Object Library
• Microsoft Word XX Object Library
• Microsoft Forms 2.0 Object Library
• OLE Automation
• SOLVER
The user may need to update the reference list whenever the version of Microsoft Office under which the model is being used is changed. Errors may also occur when the Solver is not installed as an Excel Add-In. (By default, Solver may not be installed with the basic Excel installation.) In this case, the following warning message may be displayed when opening the model (Figure 48):
[pic]
Figure 48. Message box indicating that Solver is not installed.
The procedure for installing the Solver Add-in is described in Section 3.4. In case that the user cannot find “SOLVER” from the reference list even when the Solver is installed, try uninstalling then reinstalling the Solver using the “Add-Ins” window (Figure 4). It is a good idea to close the model workbook (“GWLFXL1.2.1.xls”) and reopen it after installing the Solver, so that the change will take effect.
A.2. Making a standard run
There are some common error messages that the user might encounter when making a standard run of the GWLFXL model. For example, the following error message (Figure 49) is caused by missing data files, often weather data, or other parameter files if being read from text files:
[pic]
Figure 49. Message box indicating missing data files.
When this error message is encountered, the user should first check that the default folder name, specified in cell D7 of the “notes and parameters” worksheet, is given properly and without spelling errors. For example, “C:\GWLFXL\Data” is not a correct folder name, because it should end with a backslash (“C:\GWLFXL\Data\”). Second, make sure that the weather data file name specified in cell D29 is correct, as well as other parameter file names specified in cells D27 (e.g., “transport.txt”) and D28 (e.g., “nutrient.txt”) if these files are being read. Similarly, the following error message (Figure 50) probably indicates that the default folder name in cell D7 is incorrect or missing:
[pic]
Figure 50. Message box indicating incorrect default folder name.
In some cases, if specified default folder name does not exist, the user may see the following input window asking for the correct name (Figure 51). When this error message is encountered, the user may type in the correct folder name or click “Cancel” to cancel the simulation.
[pic]
Figure 51. Input window for default folder name.
The following error (Figure 52) may result if a standard worksheet has been renamed or deleted and the program is attempting to read or write information to it:
[pic]
Figure 52. Message box indicating incorrect standard worksheet name.
When this error message is encountered, the user should check if all the standard worksheets are present and named correctly. A list of all the standard worksheets (as shown in Figure 8) is given in Section 4.2. If one or more standard worksheets are missing, simply create a new worksheet with the proper name and try running the model again. Finally, the user may encounter the following error message indicating a problem in reading files (Figure 53):
[pic]
Figure 53. Message box indicating an error in reading files.
Typically, this problem occurs because the user did not provide the weather data that are long enough to run the model for the specified simulation period. When this error message is encountered, the user may try running the model for the shorter time period or add more weather data. Also, make sure that the correct format is used for the transport parameter file (e.g., Figure 10) and the nutrient parameter file (e.g., Figures 12, 15, and 16), if these files are read by the model.
Appendix B: Glitch Fix Report (Modifications from GWLFXL 1.1 to 1.2)
Glitch Fix Report #1
Date: 26 December 2005
Module: Model
Procedure: gwlf4
Original Code:
If UCase$((Worksheets("notes and parameters").Cells(2, 4).value) = "F") Then
Problem and Suggested Fix:
There is an unnecessary parenthesis in the above code. As a result, the model reads transport and nutrient inputs from spreadsheets even though the user enters “f” in the D2 cell of the “notes and parameters” worksheet to read inputs from files. To fix this glitch, the unnecessary parenthesis is removed.
New Code:
If UCase$(Worksheets("notes and parameters").Cells(2, 4).value) = "F" Then ' Modified by Bongghi on Dec 26, 2005
Glitch Fix Report #2
Date: 26 December 2005
Module: Model
Procedure: readtransportfile
Original Code:
Sub readtransportfile(filename$)
'********************************************************************
' READ TRANSPRT.DAT FILE
'*********************************************************************
Open filename$ For Input As #1
Input #1, nrur, nurb
nlu = nrur + nurb
Input #1, RECESSCOEF, SEEPCOEF, UNSATSTOR, SATSTOR, snow, SEDELRATIO
For k = 1 To 5
Input #1, ANTMOIST(k)
Next k
For i = 1 To 12
Input #1, month$(i, 4), cv(i), dayhrs(i), grow(i), acoef(i)
Next i
For l = 1 To nlu
'Input #1, landuse$(l, 12), AREA(l), CN(2, l), KLSCP(l), areafinal(l) - areafinal is not an original standard gwlf parameter
Input #1, landuse$(l, 12), AREA(l), CN(2, l), KLSCP(l)
Next l
Close #1
End Sub
Problem and Suggested Fix:
Reading transport inputs from files causes an error in running the model, because there are some variables (“gwdenitfrac”, “areafinal”, and “denitfrac”) that are read from spreadsheets but not from text files. These variables are not original standard GWLF parameters but newly introduced when GWLFXL is developed. To fix this problem, these variables are assumed to be zero when running the model from files (setting denitrification fractions to 0 and the final area same as the initial area).
New Code:
Sub readtransportfile(filename$)
'********************************************************************
' READ TRANSPRT.DAT FILE
'*********************************************************************
Open filename$ For Input As #1
Input #1, nrur, nurb
nlu = nrur + nurb
ReDim denitfrac(nlu) ' Added by Bongghi Dec 26, 2005
Input #1, RECESSCOEF, SEEPCOEF, UNSATSTOR, SATSTOR, snow, SEDELRATIO
gwdenitfrac = 0 ' Added by Bongghi Dec 26, 2005
For k = 1 To 5
Input #1, ANTMOIST(k)
Next k
For i = 1 To 12
Input #1, month$(i, 4), cv(i), dayhrs(i), grow(i), acoef(i)
Next i
For l = 1 To nlu
'Input #1, landuse$(l, 12), AREA(l), CN(2, l), KLSCP(l), areafinal(l) - areafinal is not an original standard gwlf parameter
Input #1, landuse$(l, 12), AREA(l), CN(2, l), KLSCP(l)
areafinal(l) = AREA(l) ' Added by Bongghi Dec 26, 2005
denitfrac(l) = 0 ' Added by Bongghi Dec 26, 2005
Next l
Close #1
End Sub
Glitch Fix Report #3
Date: 26 December 2005
Module: Model
Procedure: reporttransporttoworksheet
Original Code:
Set range1 = Range(Cells(offsetxt + 1, offsetyt + OffsetY + 1), Cells(offsetxt + OffsetX + 27 + nlu, offsetyt + OffsetY + 7))
Problem and Suggested Fix:
Some fraction of the “notes and parameters” sheet is deleted if the model is run from files because of the above code in the “reporttransporttoworksheet” procedure. This problem is corrected by providing the worksheet name when referring to “Cell”, so the transport parameter information is reported in the transport worksheet as intended.
New Code:
' Modified by Bongghi on Dec 26, 2005
Set range1 = Range(Worksheets(reportworksheetname$).Cells(offsetxt + 1, offsetyt + OffsetY + 1), Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 27 + nlu, offsetyt + OffsetY + 7))
Glitch Fix Report #4
Date: 26 December 2005
Module: Model
Procedure: gwlf4
Original Code:
1720 For m = 1 To i
1730 If EROSION(y, m) > 0 Then SEDYIELD(y, i) = SEDYIELD(y, i) + (EROSION(y, m) / BSED(m)) ' if condition added by dps 8/9/02 to eliminate prob with bsed=0
1740 Next m
Problem and Suggested Fix:
The above code may create “division by zero” error when simulating sediments. To eliminate this error generated when “BSED” is zero, the code is modified as shown below.
New Code:
1720 For m = 1 To i ' Modified (Changed to BSED(m) > 0) by Bongghi December 2005
1730 If BSED(m) > 0 Then SEDYIELD(y, i) = SEDYIELD(y, i) + (EROSION(y, m) / BSED(m)) ' if condition added by dps 8/9/02 to eliminate prob with bsed=0
1740 Next m
Glitch Fix Report #5
Date: 26 December 2005
Module: Model
Procedure: gwlf4
Original Code:
If spinuprepeat And y 0 Then
' report daily data if requested
' Added by Bongghi on Jan 16, 2006
If numday = 1 Then Call clearworksheet("daily")
If UCase$(Worksheets("notes and parameters").Cells(10, 4).value) = "Y" Then
'Call clearworksheet("daily")
Call reportdailytoworksheet("daily", numday, y, i, j)
End If
End If ' report
Glitch Fix Report #9
Date: 16 January 2006
Module: Model
Procedure: reportmonthlytoworksheet
Original Code:
For mo = 1 To 12
...
If opt > 1 Then
...
If opt > 2 Then
...
If trajectory Then
For l = 1 To nlu
monarray(mo, OffsetY + 26 + l) = AREA(l)
Next l
End If ' trajectory
End If ' opt > 2
End If ' opt > 1
Next mo
Problem and Suggested Fix:
In the trajectory simulation, the monthly land use change is not reported when running “hydrology” or “hydrology and sediments”, even if the user enters “y” to the D15 cell of the “notes and parameters” worksheet (“use trajectory?”). The monthly land use change is reported only when the model is running the “hydrology, sediments, and nutrients”, although column headings are shown in all cases. To fix this glitch, the code reporting the land use change is moved outside the “opt > 1” loop.
New Code:
For mo = 1 To 12
...
If opt > 1 Then
...
If opt > 2 Then
...
End If ' opt > 2
End If ' opt > 1
' Below code moved from above to below 'End If' by Bongghi Jan 16, 2006
If trajectory Then
For l = 1 To nlu
monarray(mo, OffsetY + 26 + l) = AREA(l)
Next l
End If ' trajectory
Next mo
Glitch Fix Report #10
Date: 16 January 2006
Module: Model
Procedure: writeresultsfile
Original Code:
Sub writeresultsfile(filename$)
...
2200 Write #1, simtitle$, "nyrs= ", nyrs, nrur, nurb, nlu, opt
...
End Sub
Problem and Suggested Fix:
When the model is run, a text file “output.txt” is created in the default directory. The model tries to write the “simtitle$” (simulation title) and “opt” (simulation option) among other outputs, but this cannot be done because they are not public variables. This problem is fixed by accepting these variables as input arguments. Of course, the code calling this procedure (within gwlf4) also needs to be changed, as reported in the next Glitch Fix below.
New Code:
Sub writeresultsfile(filename$, simtitle$, opt) ' Arguments simtitle$ and opt Added by Bongghi Jan 16, 2006
...
2200 Write #1, simtitle$, "nyrs= ", nyrs, nrur, nurb, nlu, opt
...
End Sub
Glitch Fix Report #11
Date: 16 January 2006
Module: Model
Procedure: gwlf4
Original Code:
If report Then
Call writeresultsfile(foldername$ & "results.txt")
Worksheets("notes and parameters").Cells(3, 9).value = "Simulation complete"
End If
Problem and Suggested Fix:
As described in the Glitch Fix Report #10 above, the code calling the writeresultsfile procedure is changed, providing the “simtitle$” (simulation title) and “opt” (simulation option) as input arguments.
New Code:
If report Then
Call writeresultsfile(foldername$ & "results.txt", simtitle$, opt) ' Arguments simtitle$ and opt Added by Bongghi Jan 16, 2006
Worksheets("notes and parameters").Cells(3, 9).value = "Simulation complete"
End If
Glitch Fix Report #12
Date: 17 January 2006
Module: Model
Procedure: gwlf4
Original Code:
Sub gwlf4(opt, Optional optimize As Boolean, Optional unsens As Boolean)
Problem and Suggested Fix:
Default values are not specified for optional arguments (“optimize” and “unsens”) in the gwlf4 procedure. This doesn’t seem to create a noticeable problem, but it doesn’t hurt to add the default values (“false”) for the optional arguments.
New Code:
' Default Values (False) Added by Bongghi on Jan 17, 2006
Sub gwlf4(opt, Optional optimize As Boolean = False, Optional unsens As Boolean = False)
Glitch Fix Report #13
Date: 17 January 2006
Module: Model
Procedure: rung1, rung2, rung3, frung3, srung3
Original Code:
Sub rung1()
Call gwlf4(1)
End Sub
Sub rung2()
Call gwlf4(2)
End Sub
Sub rung3()
Call gwlf4(3)
End Sub
Sub frung3()
Worksheets("notes and parameters").Cells(2, 4).value = "f"
Call gwlf4(3)
End Sub
Sub srung3()
Worksheets("notes and parameters").Cells(2, 4).value = "s"
Call gwlf4(3)
End Sub
Problem and Suggested Fix:
When the model is first opened and run, the result is not reported in the spreadsheet and/or text file. (The result is reported when the model is run second time.) This problem is caused because at the first run the calibration procedure is called, not the standard simulation. It is related to the objective function in the “calibrate” worksheet. This problem of “calibration being performed at the first run when there was no change after the model is opened” can be fixed by executing manual updating using the command “Calculate” every time before the standard run is made.
New Code:
Sub rung1()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(1)
End Sub
Sub rung2()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(2)
End Sub
Sub rung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(3)
End Sub
Sub frung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Worksheets("notes and parameters").Cells(2, 4).value = "f"
Call gwlf4(3)
End Sub
Sub srung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Worksheets("notes and parameters").Cells(2, 4).value = "s"
Call gwlf4(3)
End Sub
Glitch Fix Report #14
Date: 20 January 2006
Module: Model
Procedure: gwlf4
Original Code:
If optimize Then gout(monthct) = TOTNITR(y, i) / 1000
Problem and Suggested Fix:
A “#VALUE!” error is generated when running the objective function for “total N Load” (=objfunc(K3:K26,3,B2:B16)) in the “calibrate” worksheet, if the spinup period is specified to a value greater then 0. This problem can be corrected if the calculation of “gout” is not performed during the spinup period (i.e., y > 0).
New Code:
' "And y > 0" Added by Bongghi on Jan 20, 2006
If optimize And y > 0 Then gout(monthct) = TOTNITR(y, i) / 1000
Glitch Fix Report #15
Date: 20 January 2006
Module: Model
Procedure: declarations, gwlf4
Original Code:
Public gout(120) As Single
Problem and Suggested Fix:
A “#VALUE!” error is generated when running the objective function in the “calibrate” worksheet, when the “number of years of simulation” (simulation period) in “notes and parameters” sheet is greater than 10 years (not when the number of calibration month is greater than 120). In other words, even if the user wants to use only 1 year of data for calibration, when the total simulation period is greater than 10 years, the “#VALUE!” error (out of index error) is generated. To solve this problem, the variable “gout” is redefined right after the number of simulation years is obtained.
New Code:
Public gout() As Single ' Modified by Bongghi Hong on Jan 20, 2006
...
nyrs = Worksheets("notes and parameters").Cells(9, 4).value
ReDim gout(nyrs * 12) As Single ' Added by Bongghi on Jan 20, 2006
Glitch Fix Report #16
Date: 23 January 2006
Module: Optimization
Procedure: objfunc
Original Code:
For i = 1 To c1
gout(i) = 0
Next i
Problem and Suggested Fix:
As described in the Glitch Fix Report #15 above, initialization of “gout” is done within the “gwlf4” procedure. Thus, the above code (which may cause a “#VALUE!” error when the simulation period is greater than 10 years) is not necessary.
New Code:
' Below Three Lines Removed by Bongghi on Jan 23, 2006
'For i = 1 To c1
'gout(i) = 0
'Next i
Glitch Fix Report #17
Date: 24 January 2006
Module: Optimization
Procedure: solvessq
Original Code:
SolverSolve UserFinish = True
Problem and Suggested Fix:
The “:” is missing when specifying the “UserFinish” argument. The “False” value is given as the argument, so that when the fitting procedure is complete the solver will display a “Solver Results” window which reports whether the procedure was successful, and permits the user to select optional reports.
New Code:
SolverSolve UserFinish:=False ' Modified by Bongghi Hong on Jan 24, 2006
Glitch Fix Report #18
Date: 25 January 2006
Module: Model
Procedure: readnutrientfile
Original Code:
Sub readnutrientfile(filename$)
...
End Sub
Problem and Suggested Fix:
When the model is opened and run from the file, the predicted dry and wet deposition of N is zero. However, if the model is first run from the spreadsheet and then run again from the file, the predicted N deposition is greater than zero. This glitch occurs because the values for the model parameters “ndeptot” (total N deposition) and “pdry” (fraction of dry deposition) are not assigned when these parameters are read from the file. (The model is using values from the previous run.) This problem can be fixed by adding the following code at the end of the “readnutrientfile” procedure, setting both parameters to 0.
New Code:
Sub readnutrientfile(filename$)
...
ndeptot = 0 ' Added by Bongghi on Jan 25, 2006
pdry = 0 ' Added by Bongghi on Jan 25, 2006
End Sub
Glitch Fix Report #19
Date: 25 January 2006
Module: Model
Procedure: rung1, rung2, rung3, frung3, srung3
Original Code:
Sub rung1()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(1)
End Sub
Sub rung2()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(2)
End Sub
Sub rung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(3)
End Sub
Sub frung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Worksheets("notes and parameters").Cells(2, 4).value = "f"
Call gwlf4(3)
End Sub
Sub srung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Worksheets("notes and parameters").Cells(2, 4).value = "s"
Call gwlf4(3)
End Sub
Problem and Suggested Fix:
It is not clear whether the values of model variables are retained after the simulation and affect the next simulation results when multiple simulations are made. The “End” statements are added after the gwlf4 is called, to prevent this potential carryover effect.
New Code:
Sub rung1()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(1)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub rung2()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(2)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub rung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Call gwlf4(3)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub frung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Worksheets("notes and parameters").Cells(2, 4).value = "f"
Call gwlf4(3)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub srung3()
Calculate ' Added by Bongghi on Jan 17, 2006
Worksheets("notes and parameters").Cells(2, 4).value = "s"
Call gwlf4(3)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Glitch Fix Report #20
Date: 25 January 2006
Module: Optimization
Procedure: solve1, solve2, solve3
Original Code:
Sub solve1()
solvessq (1)
End Sub
Sub solve2()
solvessq (2)
End Sub
Sub solve3()
solvessq (3)
End Sub
Problem and Suggested Fix:
It is not clear whether the values of model variables are retained after the simulation and affect the next simulation results when multiple simulations are made. The “End” statements are added after the solvessq is called, to prevent this potential carryover effect.
New Code:
Sub solve1()
solvessq (1)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub solve2()
solvessq (2)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub solve3()
solvessq (3)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Glitch Fix Report #21
Date: 25 January 2006
Module: Stochastic
Procedure: stoch1, stoch2, stoch3
Original Code:
Sub stoch1()
Call drivestoch(1)
End Sub
Sub stoch2()
Call drivestoch(2)
End Sub
Sub stoch3()
Call drivestoch(3)
End Sub
Problem and Suggested Fix:
It is not clear whether the values of model variables are retained after the simulation and affect the next simulation results when multiple simulations are made. The “End” statements are added after the drivestoch is called, to prevent this potential carryover effect.
New Code:
Sub stoch1()
Call drivestoch(1)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub stoch2()
Call drivestoch(2)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub stoch3()
Call drivestoch(3)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Glitch Fix Report #22
Date: 25 January 2006
Module: Model
Procedure: reportnutrienttoworksheet
Original Code:
If IsNumeric(ndeptot) Then Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 2, offsetyt + OffsetY + 5).value = ndepttot
Problem and Suggested Fix:
The “ndepttot” is a typo and the correct variable name is “ndeptot”.
New Code:
If IsNumeric(ndeptot) Then Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 2, offsetyt + OffsetY + 5).value = ndeptot ' "ndepttot" Corrected by Bongghi on Jan 25, 2006
Glitch Fix Report #23
Date: 25 January 2006
Module: Model
Procedure: reporttransporttoworksheet
Original Code:
arrayout(OffsetX + 1, OffsetY + 1) = nrur
arrayout(OffsetX + 1, OffsetY + 2) = nurb
...
arrayout(OffsetX + 4, OffsetY + 1) = RECESSCOEF
arrayout(OffsetX + 4, OffsetY + 2) = SEEPCOEF
arrayout(OffsetX + 4, OffsetY + 3) = UNSATSTOR
arrayout(OffsetX + 4, OffsetY + 4) = SATSTOR
arrayout(OffsetX + 4, OffsetY + 5) = snow
arrayout(OffsetX + 4, OffsetY + 6) = SEDELRATIO
If (IsNumeric(gwdenitfrac)) Then arrayout(OffsetX + 4, OffsetY + 7) = gwdenitfrac
arrayout(OffsetX + 5, OffsetY + 1) = "antecedent moisture for initial 5 days"
For k = 1 To 5
arrayout(OffsetX + 5 + k, OffsetY + 1) = ANTMOIST(k)
Next k
...
For i = 1 To 12
arrayout(OffsetX + 13 + i, OffsetY + 1) = month$(i, 4)
arrayout(OffsetX + 13 + i, OffsetY + 2) = cv(i)
arrayout(OffsetX + 13 + i, OffsetY + 3) = dayhrs(i)
arrayout(OffsetX + 13 + i, OffsetY + 4) = grow(i)
arrayout(OffsetX + 13 + i, OffsetY + 5) = acoef(i)
Next i
...
For l = 1 To nlu
arrayout(OffsetX + 27 + l, OffsetY + 1) = landuse$(l, 12)
arrayout(OffsetX + 27 + l, OffsetY + 2) = AREA(l)
arrayout(OffsetX + 27 + l, OffsetY + 3) = CN(2, l)
arrayout(OffsetX + 27 + l, OffsetY + 4) = KLSCP(l)
If (IsNumeric(areafinal(l))) Then arrayout(OffsetX + 27 + l, OffsetY + 5) = areafinal(l)
If (IsNumeric(denitfrac(l))) Then arrayout(OffsetX + 27 + l, OffsetY + 6) = denitfrac(l)
Next l
Problem and Suggested Fix:
When the model is run from the file, the “transport” worksheet is updated (showing parameter values read from the file) in a way that some parameters have dollar ($) signs in front of the values. For example, the recession coefficient is shown as “$0.2”, not “0.2”. This problem is caused by the way the variables are defined. For example, in the following code: “Public RECESSCOEF, SEEPCOEF, UNSATSTOR, SATSTOR, snow, SEDELRATIO As Single”, only the “SEDELRATIO” is defined as Single, not the “RECESSCOEF” (recession coefficient). This problem can be fixed by specifying the type of variables when reported to the worksheet.
New Code:
arrayout(OffsetX + 1, OffsetY + 1) = CInt(nrur) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 1, OffsetY + 2) = CInt(nurb) ' Conversion Added by Bongghi on Jan 25, 2006
...
arrayout(OffsetX + 4, OffsetY + 1) = CSng(RECESSCOEF) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 4, OffsetY + 2) = CSng(SEEPCOEF) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 4, OffsetY + 3) = CSng(UNSATSTOR) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 4, OffsetY + 4) = CSng(SATSTOR) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 4, OffsetY + 5) = CSng(snow) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 4, OffsetY + 6) = CSng(SEDELRATIO) ' Conversion Added by Bongghi on Jan 25, 2006
If (IsNumeric(gwdenitfrac)) Then arrayout(OffsetX + 4, OffsetY + 7) = CSng(gwdenitfrac) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 5, OffsetY + 1) = "antecedent moisture for initial 5 days"
For k = 1 To 5
arrayout(OffsetX + 5 + k, OffsetY + 1) = CSng(ANTMOIST(k)) ' Conversion Added by Bongghi on Jan 25, 2006
Next k
...
For i = 1 To 12
arrayout(OffsetX + 13 + i, OffsetY + 1) = CStr(month$(i, 4)) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 13 + i, OffsetY + 2) = CSng(cv(i)) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 13 + i, OffsetY + 3) = CSng(dayhrs(i)) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 13 + i, OffsetY + 4) = CSng(grow(i)) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 13 + i, OffsetY + 5) = CSng(acoef(i)) ' Conversion Added by Bongghi on Jan 25, 2006
Next i
...
For l = 1 To nlu
arrayout(OffsetX + 27 + l, OffsetY + 1) = CStr(landuse$(l, 12)) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 27 + l, OffsetY + 2) = CSng(AREA(l)) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 27 + l, OffsetY + 3) = CSng(CN(2, l)) ' Conversion Added by Bongghi on Jan 25, 2006
arrayout(OffsetX + 27 + l, OffsetY + 4) = CSng(KLSCP(l)) ' Conversion Added by Bongghi on Jan 25, 2006
If (IsNumeric(areafinal(l))) Then arrayout(OffsetX + 27 + l, OffsetY + 5) = CSng(areafinal(l)) ' Conversion Added by Bongghi on Jan 25, 2006
If (IsNumeric(denitfrac(l))) Then arrayout(OffsetX + 27 + l, OffsetY + 6) = CSng(denitfrac(l)) ' Conversion Added by Bongghi on Jan 25, 2006
Next l
Glitch Fix Report #24
Date: 14 March 2006
Module: Model
Procedure: gwlf4
Original Code:
If UCase$(Worksheets("notes and parameters").Cells(2, 4).value) = "F" Then ' Modified by Bongghi on Dec 26, 2005
Call readtransportfile(foldername$ & Worksheets("notes and parameters").Cells(27, 4).value) ' transport filename
Call clearworksheet("transport")
Call reporttransporttoworksheet("transport", 0, 0)
Call readnutrientfile(foldername$ & Worksheets("notes and parameters").Cells(28, 4).value) ' nutrient filename
Call clearworksheet("nutrient")
Call reportnutrienttoworksheet("nutrient", "watershed", 1, 0, 0)
Else
Call readtransportworksheet("transport")
Call readnutrientworksheet("nutrient")
End If
Problem and Suggested Fix:
The user cannot run a calibration if the model reads data from files (i.e., when “read from file or spreadsheet?” is specified as “f”). The problem is created because the “reporttransporttoworksheet” and “reportnutrienttoworksheet” procedures make changes in the spreadsheet. These procedures should not be called when GWLF is called as a function. The following fix will ensure that the spreadsheet is not updated when the value of the “report” variable is false. (The “report” is false when running calibration or doing uncertainty analysis.)
New Code:
If UCase$(Worksheets("notes and parameters").Cells(2, 4).value) = "F" Then ' Modified by Bongghi on Dec 26, 2005
Call readtransportfile(foldername$ & Worksheets("notes and parameters").Cells(27, 4).value) ' transport filename
If report Then ' If & End If Added by Bongghi on 14 March 2006
Call clearworksheet("transport")
Call reporttransporttoworksheet("transport", 0, 0)
End If
Call readnutrientfile(foldername$ & Worksheets("notes and parameters").Cells(28, 4).value) ' nutrient filename
If report Then ' If & End If Added by Bongghi on 14 March 2006
Call clearworksheet("nutrient")
Call reportnutrienttoworksheet("nutrient", "watershed", 1, 0, 0)
End If
Else
Call readtransportworksheet("transport")
Call readnutrientworksheet("nutrient")
End If
Glitch Fix Report #25
Date: 16 March 2006
Module: Stochastic
Procedure: fillmeanparamsinstoch2
Original Code:
rangeout.value = temparray
Set rangeout = Range(Cells(3, 1), Cells(156 + 6 * nlu + 2 * manuredareas, 5))
Call colorrange(rangeout, 36)
Worksheets("stochastic").Cells.NumberFormat = "General"
Problem and Suggested Fix:
This is not really a glitch fix, but some format changes are made in the “fillmeanparamsinstoch2” procedure called by the “GWLFXL > Tools > Setup stochastic parameter worksheet” command. The new code just clears the spreadsheet before updating, add some column headings, auto-adjust the column width, and make sure that the cells become yellow to the end of the row.
New Code:
Call clearworksheet("Stochastic") ' Added by Bongghi on 16 March 2006
rangeout.value = temparray
Set rangeout = Range(Cells(3, 1), Cells(Range("B3").End(xlDown).Row, 6)) ' Modified by Bongghi on 16 March 2006
Call colorrange(rangeout, 36)
Worksheets("stochastic").Cells.NumberFormat = "General"
' Added by Bongghi on 16 March 2006
Cells(2, 2) = "Parameters"
Cells(2, 3) = "1st para. (mean)"
Cells(2, 4) = "2nd para. (std)"
Cells(2, 5) = "3rd para."
Cells(2, 6) = "Dist type"
Cells(1, 6) = "Replicate no"
Range(Cells(1, 1), Cells(2, 6)).Font.Bold = True
Columns("A:F").EntireColumn.AutoFit
Glitch Fix Report #26
Date: 16 March 2006
Module: Stochastic
Procedure: setrealizations
Original Code:
Set rangeout = Range(Cells(3, 7), Cells(num, 256))
rangeout.Clear
Problem and Suggested Fix:
A small format change is made in the “setrealizations” procedure. When random numbers drawn from probabilistic distributions are displayed in the “stochastic” worksheet, the replicate numbers will be shown in the first row. First, the above original code is modified to clean up the cells reporting the replicate numbers. Then, the code displaying the replicate numbers is added.
New Code:
Set rangeout = Range(Cells(1, 7), Cells(numRow, 256)) ' Modified by Bongghi on 16 March 2006 (Format Change 1/2)
rangeout.Clear
...
' Added by Bongghi on 16 March 2006 (Format Change 2/2)
Cells(1, j + 6) = j
Cells(1, j + 6).Font.Bold = True
Glitch Fix Report #27
Date: 16 March 2006
Module: Stochastic
Procedure: setrealizations
Original Code:
p = Rnd()
Select Case pcode
Case "TN"
pval = Application.WorksheetFunction.NormInv(p, p1, p2) ' p1 = mean, p2= stdev
Problem and Suggested Fix:
Previously, random samples were drawn from a normal distribution even if the user specified the distribution type as “TN” (truncated normal) in the “stochastic” worksheet. The above code is revised so that random samples are drawn from a truncated normal distribution as originally intended.
New Code:
p = Rnd()
Select Case pcode
Case "TN"
' Do...Loop Added by Bongghi on 16 March 2006
' Here TN simply draws samples from the positive part of the normal distribution
' Thus the mean and variance parameters no longer represent the actual mean and variance of the distribution
Do
p = Rnd()
pval = Application.WorksheetFunction.NormInv(p, p1, p2) ' p1 = mean, p2= stdev
Loop While pval Tools > Generate Stochastic Parameters” doesn’t work if the user tries it right after the model is opened. The problem is occurring in the above code within the “setrealizations” procedure. The code is perfectly okay except that the variable “num” (which represents the number of effective rows in the spreadsheet) is already being used as a global variable representing the number of parameters for calibration. In the normal case, this should not be a problem because VB can distinguish between global and local variables even if they have the same name. What’s happening here is that when the “rangeout.Clear” is executed, the spreadsheet is updated, and the objective function in the “calibrate” sheet is re-calculated. Consequently, the “setparms2” procedure is called, which calculates the number of parameters for calibration and stores the result in the global variable “num”. For example, when there is one calibration parameter selected, “num” becomes 1 by the time “ReDim plist(num - 2, nreal)” is executed, and this generates an error (since num-2=-1). For some reason, this doesn’t happen once the spreadsheet is manually updated. The best way to avoid this problem is to change the variable name in “setrealizations” procedure from “num” to “numRow”.
New Code:
numRow = 3 ' Variable name modified by Bongghi on 17 March 2006
Do
numRow = numRow + 1 ' Variable name modified by Bongghi on 17 March 2006
Loop Until Worksheets("Stochastic").Cells(numRow, 2).value = "" ' Variable name modified by Bongghi on 17 March 2006
numRow = numRow - 1 ' Variable name modified by Bongghi on 17 March 2006
Set rangeout = Range(Cells(1, 7), Cells(numRow, 256)) ' Modified by Bongghi on 16 March 2006 (Format Change 1/2) ' Variable name modified by Bongghi on 17 March 2006
rangeout.Clear
If sensitivity Then ' fill code column with s to indicate sensitivity analysis
Set rangecode = Range(Cells(3, 6), Cells(numRow, 6)) ' Variable name modified by Bongghi on 17 March 2006
rangecode.value = "s"
End If
ReDim plist(numRow - 2, nreal) ' Variable name modified by Bongghi on 17 March 2006
For i = 1 To numRow - 2 ' Variable name modified by Bongghi on 17 March 2006
...
Set rangeout = Range(Cells(3, 7), Cells(numRow, nreal + 6)) ' Variable name modified by Bongghi on 17 March 2006
Glitch Fix Report #29
Date: 17 March 2006
Module: Model
Procedure: createparameterworksheet
Original Code:
Sub createparameterworksheet(num)
...
If num = 1 Then
filename$ = Worksheets("notes and parameters").Cells(28, 4).value ' nutrient file
...
If num = 1 Then
Call readnutrientfile(foldername$ & filename$)
...
Problem and Suggested Fix:
The variable “num” is used as local variable in the “createparameterworksheet” procedure. Here, the “num” is an argument containing either 1 (representing nutrient spreadsheet) or 2 (representing transport spreadsheet). It is not clear if it creates the same problem described in the Glitch Fix Report #28 above. However, to be on the safe side, the variable name is changed from “num” to “numSheetName”. There are no other places where “num” is used as a local variable.
New Code:
Sub createparameterworksheet(numSheetName) ' Variable Name Modified by Bongghi on 17 March 2006
...
If numSheetName = 1 Then ' Variable Name Modified by Bongghi on 17 March 2006
filename$ = Worksheets("notes and parameters").Cells(28, 4).value ' nutrient file
...
If numSheetName = 1 Then ' Variable Name Modified by Bongghi on 17 March 2006
Call readnutrientfile(foldername$ & filename$)
...
Glitch Fix Report #30
Date: 20 March 2006
Module: Model
Procedure: makegwlftemplate
Original Code:
Worksheets("template").Cells(1, 2).value = resp
Problem and Suggested Fix:
The above code writes the number of subwatersheds (“resp”) in the (1, 2) cell of the “template” worksheet. However, another variable (watershed name) will be written on the same cell, erasing the value of “resp”. Now the “resp” is written in the (2, 1) cell, which will not be overwritten.
New Code:
Worksheets("template").Cells(2, 1).value = resp ' Modified by Bongghi on 20 March 2006
Glitch Fix Report #31
Date: 20 March 2006
Module: Model
Procedure: makegwlftemplate
Original Code:
For j = 1 To resp
Call reportnutrienttoworksheet("template", "Hudson1", j, 0, (j - 1) * 8)
Call reporttransporttoworksheet("template", 40 + maxnlu + maxma, (j - 1) * 8)
Next j
Problem and Suggested Fix:
When generating a template worksheet, the above code calling “reporttransporttoworksheet” overwrites the transport parameters on the nutrient parameters if the sum of “maxnlu” and “maxma” is smaller than the sum of current number of land use and manured lands. Below code corrects this overwriting problem.
New Code:
For j = 1 To resp
Call reportnutrienttoworksheet("template", "Hudson1", j, 0, (j - 1) * 8)
Call reporttransporttoworksheet("template", 40 + Application.Max(maxnlu + maxma, nlu + manuredareas) + 1, (j - 1) * 8) ' Modified by Bongghi on 20 March 2006
Next j
Glitch Fix Report #32
Date: 23 May 2006
Module: Model
Procedure: makegwlftemplate
Original Code:
resp1$ = getstrg$("The template sheet already exists. Overwrite (y/n)?)", "y")
Problem and Suggested Fix:
An extra closing parenthesis is removed from the above code.
New Code:
resp1$ = getstrg$("The template sheet already exists. Overwrite (y/n)?", "y") ' Modified by Bongghi on 23 May 2006
Glitch Fix Report #33
Date: 24 May 2006
Module: Printout
Procedure: printtransport
Original Code:
Call printrange("transport", "a1:g32")
Problem and Suggested Fix:
Fixed range (a1:g32) is given as an argument when calling printrange procedure. Thus, sometimes not all the used range in the spreadsheet is selected for printout. This problem can be fixed by using “UsedRange” property of the worksheet. Now the non-blank cells in the transport spreadsheet are selected for printout.
New Code:
Call printrange("transport", Sheets("transport").UsedRange.Address) ' Modified by Bongghi on 24 May 2006
Glitch Fix Report #34
Date: 24 May 2006
Module: Printout
Procedure: printnutrient
Original Code:
Call printrange("nutrient", "a1:g44")
Problem and Suggested Fix:
Fixed range (a1:g44) is given as an argument when calling printrange procedure. Thus, sometimes not all the used range in the spreadsheet is selected for printout. This problem can be fixed by using “UsedRange” property of the worksheet. Now the non-blank cells in the nutrient spreadsheet are selected for printout.
New Code:
Call printrange("nutrient", Sheets("nutrient").UsedRange.Address) ' Modified by Bongghi on 24 May 2006
Glitch Fix Report #35
Date: 31 May 2006
Module: ThisWorkbook, Model
Procedure: Workbook_Open, AddReference
Original Code:
Private Sub Workbook_Open()
Call CreateMenu
MsgBox "A new menu (GWLFXL) was created.", vbInformation
End Sub
Problem and Suggested Fix:
The current version of the model suffers from a compatibility problem among different versions of Excel. This glitch-fix attempts to correct this problem by automatically updating the references that the model uses when the model is opened. In some cases, the user may still need to update some references manually. Note that the code updating Microsoft Word reference is comment-blocked because the code requires the user to enter the password every time the model is opened. (Currently, the code-protected version of the model automatically updates only the Microsoft Office reference.)
New Code:
Private Sub Workbook_Open()
Call AddReference ' Added by Bongghi on 31 May 2006
Call CreateMenu
MsgBox "A new menu (GWLFXL) was created.", vbInformation
End Sub
...
' Added by Bongghi on 31 May 2006
Sub AddReference()
' Find Office Version Number
OfficeMajor = 2
ExcelVersion = Val(Application.Version)
Select Case ExcelVersion
Case 8 ' Office 8.0 (Excel 97)
OfficeMinor = 0
Case 9 ' Office 9.0 (Excel 2000)
OfficeMinor = 1
Case 10 ' Office 10.0 (Excel 2002)
OfficeMinor = 2
Case 11 ' Office 11.0 (Excel 2003)
OfficeMinor = 3
Case Else
MsgBox "Cannot Determine Excel Version"
Exit Sub
End Select
' Remove Current Office Reference which could be Outdated Version
For Each Ref In ActiveWorkbook.VBProject.References
If Ref.Name = "Office" Then
ActiveWorkbook.VBProject.References.Remove Ref
Exit For
End If
Next Ref
' Add Office Reference with the Same Version as Excel
On Error Resume Next
ActiveWorkbook.VBProject.References.AddFromGuid _
"{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}", OfficeMajor, OfficeMinor
On Error GoTo 0
'' Find Word Version Number
'WordMajor = 8
'ExcelVersion = Val(Application.Version)
'Select Case ExcelVersion
'Case 8 ' Office 8.0 (Excel 97)
'WordMinor = 0
'Case 9 ' Office 9.0 (Excel 2000)
'WordMinor = 1
'Case 10 ' Office 10.0 (Excel 2002)
'WordMinor = 2
'Case 11 ' Office 11.0 (Excel 2003)
'WordMinor = 3
'Case Else
'MsgBox "Cannot Determine Excel Version"
'Exit Sub
'End Select
'' Remove Current Word Reference which could be Outdated Version
'For Each Ref In ActiveWorkbook.VBProject.References
'If Ref.Name = "Word" Then
'ActiveWorkbook.VBProject.References.Remove Ref
'Exit For
'End If
'Next Ref
'' Add Word Reference with the Same Version as Excel
'On Error Resume Next
'ActiveWorkbook.VBProject.References.AddFromGuid _
'"{00020905-0000-0000-C000-000000000046}", WordMajor, WordMinor
'On Error GoTo 0
End Sub
Glitch Fix Report #36
Date: 31 May 2006
Module: ThisWorkbook, Model
Procedure: Workbook_Open, AddSolver
Original Code:
Private Sub Workbook_Open()
Call AddReference ' Added by Bongghi on 31 May 2006
Call CreateMenu
MsgBox "A new menu (GWLFXL) was created.", vbInformation
End Sub
Problem and Suggested Fix:
This glitch-fix attempts to establish the Solver reference when the model is opened. However, currently the code automatically updating the Solver reference is comment-blocked because the user still needs to close the model and open it again even if the model successfully establishes the Solver reference. (At this moment, only warning messages with some instructions are shown if the Solver is not appropriately installed.)
New Code:
Private Sub Workbook_Open()
Call AddReference ' Added by Bongghi on 31 May 2006
Call AddSolver ' Added by Bongghi on 31 May 2006
Call CreateMenu
MsgBox "A new menu (GWLFXL) was created.", vbInformation
End Sub
...
' Added by Bongghi on 31 May 2006
Sub AddSolver()
' Check if Solver is Included in the Add-In Collection
IsSolverPresent = False
For Each AddInItem In AddIns
If UCase(AddInItem.Name) = "SOLVER.XLA" Then
IsSolverPresent = True
End If
Next AddInItem
If IsSolverPresent = False Then
MsgBox "Solver is not included in the add-in list!" & _
Chr(13) & Chr(13) & "To include Solver:" & Chr(13) & _
"(1) Click Tools > Add-Ins..." & Chr(13) & _
"(2) Click Browse... and locate SOLVER.XLA" & Chr(13) & _
"(3) Close the model and open it again", vbExclamation
Exit Sub
End If
' Check if Solver is Installed
For Each AddInItem In AddIns
If UCase(AddInItem.Name) = "SOLVER.XLA" Then
If AddInItem.Installed = False Then
MsgBox "Solver is not installed!" & _
Chr(13) & Chr(13) & "To install Solver:" & Chr(13) & _
"(1) Click Tools > Add-Ins..." & Chr(13) & _
"(2) Check Solver Add-in in the Add-Ins window" & Chr(13) & _
"(3) Close the model and open it again", vbExclamation
End If
End If
Next AddInItem
'' Remove Current Office Reference which could be Outdated Version
'For Each Ref In ActiveWorkbook.VBProject.References
'If Ref.Name = "SOLVER" Then
'ActiveWorkbook.VBProject.References.Remove Ref
'Exit For
'End If
'Next Ref
'' Establish Reference to Solver Add-In
'SolverFullName = ""
'For Each AddInItem In AddIns
'If UCase(AddInItem.Name) = "SOLVER.XLA" Then
'SolverFullName = AddInItem.FullName
'End If
'Next AddInItem
'On Error Resume Next
'ActiveWorkbook.VBProject.References.AddFromFile SolverFullName
'On Error GoTo 0
End Sub
Glitch Fix Report #37
Date: 2 June 2006
Module: Model
Procedure: reportannualtoworksheet
Original Code:
annrange.value = annarray
Problem and Suggested Fix:
When the annual output is reported to the “annual” worksheet, some values at the last year of simulation have dollar ($) signs. This problem is corrected by specifying the format of reported values as “General”.
New Code:
annrange.value = annarray
annrange.NumberFormat = "General" ' Added by Bongghi June 2, 2006
Glitch Fix Report #38
Date: 2 June 2006
Module: Printout
Procedure: printoutdata
Original Code:
Sub printoutdata(sheetname$)
Sheets(sheetname$).Select
Range("A8").Select
vd = Selection.End(xlDown).Address ' find the bottom line of the current range of cells
Range(vd).Select
vr = Selection.End(xlToRight).Address ' find the rightmost cell of the bottom line
Range("a1", vr).Select ' select the rectangle bounded by a1 and bottom right
nn = Application.WorksheetFunction.CountA(Range("a3", vr)) ' counts number of nonblank cells
If nn = 0 Then MsgBox " No data! Printout halted...hit ok to continue", vbInformation: End
Selection.Printout preview:=True ' print selection with preview (can opt out from there)
End Sub
Problem and Suggested Fix:
When printing out the output worksheet (“daily”, “monthly”, or “annual”), sometimes not all the used range in the spreadsheet is selected for printout. To correct this problem, one line of code is added using “UsedRange” property of the worksheet, so that all the non-blank cells in the output worksheet are selected for printout.
New Code:
Sub printoutdata(sheetname$)
Sheets(sheetname$).Select
Range("A8").Select
vd = Selection.End(xlDown).Address ' find the bottom line of the current range of cells
Range(vd).Select
vr = Selection.End(xlToRight).Address ' find the rightmost cell of the bottom line
Range("a1", vr).Select ' select the rectangle bounded by a1 and bottom right
nn = Application.WorksheetFunction.CountA(Range("a3", vr)) ' counts number of nonblank cells
If nn = 0 Then MsgBox " No data! Printout halted...hit ok to continue", vbInformation: End
' Added by Bongghi June 2, 2006
Range(Sheets(sheetname$).UsedRange.Address).Select
Selection.Printout preview:=True ' print selection with preview (can opt out from there)
End Sub
Glitch Fix Report #39
Date: 13 June 2006
Module: Model
Procedure: gwlf4
Original Code:
defaultdrive$ = Left(ThisWorkbook.Path, 2) ' get drive location of workbook
foldername$ = Worksheets("notes and parameters").Cells(7, 4).value
If Not PathExists(foldername$) Then ' if default foldername is invalid, try the drive letter of the workbook
Folder$ = defaultdrive$ & Mid(foldername$, 3, Len(foldername$))
If PathExists(Folder$) Then
foldername$ = Folder$ ' if the new foldername is ok, reset it
Worksheets("notes and parameters").Cells(7, 4).value = Folder$
End If
End If
Do While Not PathExists(foldername$)
foldername$ = getstrg$("Old foldername doesn't exist. New foldername?", foldername$)
If Len(foldername$) = 0 Then
MsgBox " Simulation halted...hit ok to continue", vbInformation
End
End If
Loop
Worksheets("notes and parameters").Activate
If optimize Then
report = False
unsens = False
Else
report = True
Worksheets("notes and parameters").Cells(7, 4).value = foldername$
End If
If unsens Then
report = False
optimize = False
End If
Problem and Suggested Fix:
When running calibration or uncertainty analysis, if the data folder specified in the D7 cell of the “notes and parameters” worksheet doesn’t exist, an input box asking the correct folder name appears each time the individual simulation is made. (Calibration and uncertainty analysis make multiple simulations.) This problem occurs because the “report” variable is set to “False” when these simulations are made, and the correct folder name specified by the user cannot be written to the spreadsheet. To correct this glitch, the code checking data folder name is separated from the “gwlf4” procedure and moved to a new procedure “CheckDataFolderName”, which is called by the “gwlf4”. When running calibration or uncertainty analysis, this procedure is called by “solve1”, “solve2”, “solve3”, “stoch1”, “stoch2”, or “stoch3”, before the multiple simulations are made. Another modification within the “CheckDataFolderName” is that the code checks if the folder separator (“\”) exists at the end of the folder name and adds it if it is not present.
New Code:
Worksheets("notes and parameters").Activate
If optimize Then
report = False
unsens = False
Else
report = True
End If
If unsens Then
report = False
optimize = False
End If
' Added by Bongghi on June 13, 2006
If report = True Then
Call CheckDataFolderName
End If
foldername$ = Worksheets("notes and parameters").Cells(7, 4).value
...
' Moved by Bongghi on June 13, 2006
Sub CheckDataFolderName()
foldername$ = Trim(Worksheets("notes and parameters").Cells(7, 4).value)
If Not Right(foldername$, 1) = "\" Then
foldername$ = foldername$ & "\"
Worksheets("notes and parameters").Cells(7, 4).value = foldername$
End If
foldername$ = Worksheets("notes and parameters").Cells(7, 4).value
If Not PathExists(foldername$) Then ' if default foldername is invalid, try the drive letter of the workbook
defaultdrive$ = Left(ThisWorkbook.Path, 2) ' get drive location of workbook
Folder$ = defaultdrive$ & Mid(foldername$, 3, Len(foldername$))
If PathExists(Folder$) Then
foldername$ = Folder$ ' if the new foldername is ok, reset it
Worksheets("notes and parameters").Cells(7, 4).value = Folder$
End If
End If
Do While Not PathExists(foldername$)
foldername$ = getstrg$("Old foldername doesn't exist. New foldername?", foldername$)
If Len(foldername$) = 0 Then
MsgBox " Simulation halted...hit ok to continue", vbInformation
End
End If
Loop
Worksheets("notes and parameters").Cells(7, 4).value = foldername$
End Sub
...
Sub solve1()
Call CheckDataFolderName ' Added by Bongghi on June 13, 2006
solvessq (1)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub solve2()
Call CheckDataFolderName ' Added by Bongghi on June 13, 2006
solvessq (2)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub solve3()
Call CheckDataFolderName ' Added by Bongghi on June 13, 2006
solvessq (3)
End ' Added by Bongghi on Jan 25, 2006
End Sub
...
Sub stoch1()
Call CheckDataFolderName ' Added by Bongghi on June 13, 2006
Call drivestoch(1)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub stoch2()
Call CheckDataFolderName ' Added by Bongghi on June 13, 2006
Call drivestoch(2)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Sub stoch3()
Call CheckDataFolderName ' Added by Bongghi on June 13, 2006
Call drivestoch(3)
End ' Added by Bongghi on Jan 25, 2006
End Sub
Glitch Fix Report #40
Date: 13 June 2006
Module: Optimization
Procedure: solvessq
Original Code:
Sub solvessq(opt)
Dim zzz(20) As String, objectivefunccell As String
...
End Sub
Problem and Suggested Fix:
The objective functions specified in the “calibrate” worksheet call “gwlf4” as a function, creating a number of problems. For example, if any cell value is changed after the model is opened, the entire workbook is updated including the objective functions, calling “gwlf4” (i.e., running the model) several times. If the data folder specified in the D7 cell of the “notes and parameters” worksheet doesn’t exist, the user will see an input box asking the correct folder name several times. Also, the presence of unnecessary objective functions makes some model performance slow (e.g., while running the calibration). Another negative aspect of the objective function is that the user needs to write the equation and specify input arguments (e.g., “=objfunc(J3:J26,2,B2:B16)”), which can be a potential source of error. The new code added to the “solvessq” procedure writes the objective function to the “calibrate” worksheet just before the Solver is executed (based on the available data and the number of parameters), and replaces the objective function with the sum of square values (calculated by the objective function) right after the Solver is executed.
New Code:
Sub solvessq(opt)
Dim zzz(20) As String, objectivefunccell As String
' Added by Bongghi on June 13, 2006
Sheets("calibrate").Activate
EndDataRowNum = Cells(65536, 8 + opt).End(xlUp).Row
DataAddress = Range(Cells(3, 8 + opt), Cells(EndDataRowNum, 8 + opt)).Address
EndParaRowNum = Cells(65536, 2).End(xlUp).Row
ParaAddress = Range(Cells(2, 2), Cells(EndParaRowNum, 2)).Address
objStr = "=objfunc(" + DataAddress + "," + CStr(opt) + "," + ParaAddress + ")"
Cells(1 + opt, 5) = objStr
...
' Added by Bongghi on June 13, 2006
ssqValue = Cells(1 + opt, 5)
Cells(1 + opt, 5) = ssqValue
End Sub
Glitch Fix Report #41
Date: 29 June 2006
Module: Model, Stochastic
Procedure: gwlf4, readnutrientworksheet, reportnutrienttoworksheet, readnutrientfile, writenutrientfile, reportannualtoworksheet, reportmonthlytoworksheet, calcmeans, getstochparms, fillmeanparamsinstoch2
Original Code:
Public pointnitr(0 To 12) As Single, pointphos(0 To 12) As Single
...
pointnitr(0) = 0
pointphos(0) = 0
...
BNITR = GROUNDNITR(y, i) + pointnitr(i) + (wetdepnitr(y, i) + drydepnitr(y, i)) * (1 - denitav)
BPHOS = GROUNDPHOS(y, i) + pointphos(i)
...
If dsflag = 2 Or dsflag = 3 Then
pointnitr(0) = pointnitr(0) + pointnitr(12 * (y - 1) + i)
pointphos(0) = pointphos(0) + pointphos(12 * (y - 1) + i)
Else
pointnitr(0) = pointnitr(0) + pointnitr(i)
pointphos(0) = pointphos(0) + pointphos(i)
End If
...
pointnitr(i) = Worksheets(nutrientworksheetname$).Cells(OffsetX + 6 + nlu + manuredareas + i, OffsetY + 2).value
pointphos(i) = Worksheets(nutrientworksheetname$).Cells(OffsetX + 6 + nlu + manuredareas + i, OffsetY + 3).value
...
pointnitr(12 * (y - 1) + i) = Worksheets(nutrientworksheetname$).Cells(dsoff + 12 * (y - 2) + i, OffsetY + 1).value
pointphos(12 * (y - 1) + i) = Worksheets(nutrientworksheetname$).Cells(dsoff + 12 * (y - 2) + i, OffsetY + 2).value
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 7 + nlu + manuredareas + i, offsetyt + OffsetY + 1).value = pointnitr(i)
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 7 + nlu + manuredareas + i, offsetyt + OffsetY + 2).value = pointphos(i)
...
Worksheets(reportworksheetname$).Cells(dsoff + 12 * (y - 2) + i, offsetyt + OffsetY + 1).value = pointnitr(12 * (y - 1) + i)
Worksheets(reportworksheetname$).Cells(dsoff + 12 * (y - 2) + i, offsetyt + OffsetY + 2).value = pointphos(12 * (y - 1) + i)
...
5670 Input #1, pointnitr(i), pointphos(i)
...
Input #1, pointnitr(12 * (y - 1) + i), pointphos(12 * (y - 1) + i)
...
4600 Write #1, pointnitr(i), pointphos(i)
...
Write #1, pointnitr(12 * (y - 1) + i), pointphos(12 * (y - 1) + i)
...
annarray(1, 14) = pointnitr(0) / 1000#
...
annarray(1, 24) = pointphos(0) / 1000#
...
monarray(mo, OffsetY + 15) = pointnitr(mo) / 1000#
...
monarray(mo, OffsetY + 25) = pointphos(mo) / 1000#
...
vblarray(y, 14) = pointnitr(0)
...
vblarray(y, 24) = pointphos(0)
...
pointnitr(i) = Worksheets("stochastic").Cells(75 + 7 * nlu + 2 * manuredareas + i, n + 6).value
pointphos(i) = Worksheets("stochastic").Cells(87 + 7 * nlu + 2 * manuredareas + i, n + 6).value
...
pointnitr(12 * (y - 1) + i) = Worksheets("stochastic").Cells(dsoff + 12 * (y - 2) + i, n + 6).value
pointphos(12 * (y - 1) + i) = Worksheets("stochastic").Cells(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i, n + 6).value
...
temparray(73 + 7 * nlu + 2 * manuredareas + i, 3) = pointnitr(i)
...
temparray(85 + 7 * nlu + 2 * manuredareas + i, 3) = pointphos(i)
...
temparray(dsoff + 12 * (y - 2) + i, 3) = pointnitr(12 * (y - 1) + i)
...
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i, 3) = pointphos(12 * (y - 1) + i)
Problem and Suggested Fix:
The “pointnitr” and “pointphos” variables, containing point source N and P, respectively, are defined as a vector with 13 elements (0 to 12). This creates a “subscript out of range” error when the value of “dsflag” is 2 or 3 (i.e., point source N and P are specified for each month of simulation). To avoid this problem, the “pointnitr” and “pointphos” variables are redefined after obtaining the spin-up period and number of simulation years. Now these variables are a matrix having (year, month) subscripts in consistent with other flux variables.
New Code:
Public pointnitr() As Single, pointphos() As Single ' Modified by Bongghi Hong on June 29, 2006
...
pointnitr(y, 0) = 0 ' Modified by Bongghi Hong on June 29, 2006
pointphos(y, 0) = 0 ' Modified by Bongghi Hong on June 29, 2006
...
BNITR = GROUNDNITR(y, i) + pointnitr(y, i) + (wetdepnitr(y, i) + drydepnitr(y, i)) * (1 - denitav) ' Modified by Bongghi Hong on June 29, 2006
BPHOS = GROUNDPHOS(y, i) + pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006
...
'If dsflag = 2 Or dsflag = 3 Then
pointnitr(y, 0) = pointnitr(y, 0) + pointnitr(y, i) ' Modified by Bongghi Hong on June 29, 2006
pointphos(y, 0) = pointphos(y, 0) + pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006
'Else
'pointnitr(0) = pointnitr(0) + pointnitr(i)
'pointphos(0) = pointphos(0) + pointphos(i)
'End If
...
' Added by Bongghi Hong on June 29, 2006
nyrs = Worksheets("notes and parameters").Cells(9, 4).value
spinuplen = Worksheets("notes and parameters").Cells(5, 4).value
ReDim pointnitr(1 - spinuplen To nyrs, 0 To 12) As Single, pointphos(1 - spinuplen To nyrs, 0 To 12) As Single
...
pointnitr(1, i) = Worksheets(nutrientworksheetname$).Cells(OffsetX + 6 + nlu + manuredareas + i, OffsetY + 2).value ' Modified by Bongghi Hong on June 29, 2006
pointphos(1, i) = Worksheets(nutrientworksheetname$).Cells(OffsetX + 6 + nlu + manuredareas + i, OffsetY + 3).value ' Modified by Bongghi Hong on June 29, 2006
...
pointnitr(y, i) = Worksheets(nutrientworksheetname$).Cells(dsoff + 12 * (y - 2) + i, OffsetY + 2).value ' Modified by Bongghi Hong on June 29, 2006
pointphos(y, i) = Worksheets(nutrientworksheetname$).Cells(dsoff + 12 * (y - 2) + i, OffsetY + 3).value ' Modified by Bongghi Hong on June 29, 2006
...
' Modified by Bongghi Hong on June 29, 2006
Else ' dsflag=1
For y = 2 To nyrs
For i = 1 To 12
pointnitr(y, i) = pointnitr(1, i)
pointphos(y, i) = pointphos(1, i)
Next i
Next y
End If
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 7 + nlu + manuredareas + i, offsetyt + OffsetY + 1).value = pointnitr(1, i) ' Modified by Bongghi Hong on June 29, 2006
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 7 + nlu + manuredareas + i, offsetyt + OffsetY + 2).value = pointphos(1, i) ' Modified by Bongghi Hong on June 29, 2006
...
Worksheets(reportworksheetname$).Cells(dsoff + 12 * (y - 2) + i, offsetyt + OffsetY + 1).value = pointnitr(y, i) ' Modified by Bongghi Hong on June 29, 2006
Worksheets(reportworksheetname$).Cells(dsoff + 12 * (y - 2) + i, offsetyt + OffsetY + 2).value = pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006
...
5670 Input #1, pointnitr(1, i), pointphos(1, i) ' Modified by Bongghi Hong on June 29, 2006
...
Input #1, pointnitr(y, i), pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006
...
4600 Write #1, pointnitr(1, i), pointphos(1, i) ' Modified by Bongghi Hong on June 29, 2006
...
Write #1, pointnitr(y, i), pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006
...
annarray(1, 14) = pointnitr(y, 0) / 1000# ' Modified by Bongghi Hong on June 29, 2006
...
annarray(1, 24) = pointphos(y, 0) / 1000# ' Modified by Bongghi Hong on June 29, 2006
...
monarray(mo, OffsetY + 15) = pointnitr(y, mo) / 1000# ' Modified by Bongghi Hong on June 29, 2006
...
monarray(mo, OffsetY + 25) = pointphos(y, mo) / 1000# ' Modified by Bongghi Hong on June 29, 2006
...
vblarray(y, 14) = pointnitr(y, 0) ' Modified by Bongghi Hong on June 29, 2006
...
vblarray(y, 24) = pointphos(y, 0) ' Modified by Bongghi Hong on June 29, 2006
...
pointnitr(1, i) = Worksheets("stochastic").Cells(75 + 7 * nlu + 2 * manuredareas + i, n + 6).value ' Modified by Bongghi Hong on June 29, 2006
pointphos(1, i) = Worksheets("stochastic").Cells(87 + 7 * nlu + 2 * manuredareas + i, n + 6).value ' Modified by Bongghi Hong on June 29, 2006
...
pointnitr(y, i) = Worksheets("stochastic").Cells(dsoff + 12 * (y - 2) + i, n + 6).value ' Modified by Bongghi Hong on June 29, 2006
pointphos(y, i) = Worksheets("stochastic").Cells(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i, n + 6).value ' Modified by Bongghi Hong on June 29, 2006
...
temparray(73 + 7 * nlu + 2 * manuredareas + i, 3) = pointnitr(1, i) ' Modified by Bongghi Hong on June 29, 2006
...
temparray(85 + 7 * nlu + 2 * manuredareas + i, 3) = pointphos(1, i) ' Modified by Bongghi Hong on June 29, 2006
...
temparray(dsoff + 12 * (y - 2) + i, 3) = pointnitr(y, i) ' Modified by Bongghi Hong on June 29, 2006
...
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i, 3) = pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006
Glitch Fix Report #42
Date: 30 June 2006
Module: Model, Stochastic
Procedure: reportnutrienttoworksheet, reportannualtoworksheet, calcmeans, fillmeanparamsinstoch2
Original Code:
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + l, offsetyt + 1).value = l
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + l, offsetyt + 1).value = l
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 7 + nlu + l, offsetyt + OffsetY + 1).value = l
...
Range(Cells(1, OffsetY + 3), Cells(1, OffsetY + 23 + nlu)).value = "Average"
...
Range(Cells(1, OffsetY + 3), Cells(1, OffsetY + 23)).value = "Average"
...
Set labelout = Range(Cells(1, 2), Cells(2, (25 + nlu) * 2 - 1))
...
Dim temparray(260, 5) As Variant
...
Set rangeout = Range(Cells(3, 1), Cells(262, 5))
...
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i, 1) = month$(i, 4)
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i, 2) = "Pt src P load"
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i, 3) = pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006
Problem and Suggested Fix:
Some format changes are made in the “nutrient”, “annual”, “stochastic”, and “uncertainty” worksheets. Missing units are added, unnecessary zero values are removed, and a glitch (some simulated variables are not reported when the value of “dsflag” is 2 or 3, i.e., point source N and P are specified for each month of simulation) is fixed.
New Code:
Worksheets(reportworksheetname$).Cells(offsetxt + 3, offsetyt + OffsetY + 1).value = "(mg/kg)" ' Added by Bongghi on June 30, 2006
...
Worksheets(reportworksheetname$).Cells(offsetxt + 3, offsetyt + OffsetY + 2).value = "(mg/kg)" ' Added by Bongghi on June 30, 2006
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + l, offsetyt + 1).value = landuse$(l, 12) ' Modified by Bongghi on June 30, 2006
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + l, offsetyt + 4).value = "(rural runoff conc, mg/L)" ' Added by Bongghi on June 30, 2006
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + l, offsetyt + 1).value = landuse$(l, 12) ' Modified by Bongghi on June 30, 2006
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + l, offsetyt + 4).value = "(urban nutrient accum rate, kg/ha/day)" ' Added by Bongghi on June 30, 2006
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + nlu + l, offsetyt + OffsetY).value = landuse$(l, 12) ' Modified by Bongghi on June 30, 2006
...
Worksheets(reportworksheetname$).Cells(offsetxt + OffsetX + 6 + nlu + l, offsetyt + OffsetY + 3).value = "(rural runoff conc during manure month, mg/L)" ' Added by Bongghi on June 30, 2006
...
Range(Cells(1, OffsetY + 3), Cells(1, OffsetY + 26 + nlu)).value = "average" ' Modified by Bongghi Hong on June 30, 2006
...
Range(Cells(1, OffsetY + 3), Cells(1, OffsetY + 26)).value = "average" ' Modified by Bongghi Hong on June 30, 2006
...
vlabel(1, (25 + l) * 2) = landuse$(l, 12) + " std" ' Added by Bongghi Hong on June 30, 2006
...
vlabel(2, (25 + l) * 2) = "(ha)" ' Added by Bongghi Hong on June 30, 2006
...
If trajectory Then ' Added by Bongghi Hong on June 30, 2006
Set labelout = Range(Cells(1, 2), Cells(2, (25 + nlu) * 2 + 1)) ' Modified by Bongghi Hong on June 30, 2006
...
' Added by Bongghi Hong on June 30, 2006
Else
Set labelout = Range(Cells(1, 2), Cells(2, 25 * 2 + 1))
labelout.value = vlabel
Range(Cells(1, 1), Cells(2, 25 * 2 + 1)).Font.Bold = True
End If
...
If trajectory Then ' Added by Bongghi Hong on June 30, 2006
...
' Added by Bongghi Hong on June 30, 2006
Else
Set rangeout = Range(Cells(3, 2), Cells(nyrs + 3 - 1, 25 * 2 + 1))
End If
...
Dim temparray(50000, 5) As Variant ' Modified by Bongghi on June 30, 2006
...
Set rangeout = Range(Cells(3, 1), Cells(50000 + 2, 5)) ' Modified by Bongghi on June 30, 2006
...
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i + 12, 1) = month$(i, 4) + " (year " + CStr(y) + ")" ' Modified by Bongghi on June 30, 2006
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i + 12, 2) = "pt src P load (kg/month)" ' Modified by Bongghi on June 30, 2006
temparray(dsoff + 12 * (nyrs - 2) + 12 * (y - 2) + i + 12, 3) = pointphos(y, i) ' Modified by Bongghi Hong on June 29, 2006 ' Modified by Bongghi on June 30, 2006
Glitch Fix Report #43
Date: 1 July 2006
Module: Optimization
Procedure: solvessq
Original Code:
objStr = "=objfunc(" + DataAddress + "," + CStr(opt) + "," + ParaAddress + ")"
Cells(1 + opt, 5) = objStr
Problem and Suggested Fix:
The objective function is now shown in the “calibrate” worksheet (on the column “F”), so that the user can check if the correct range is covered by the objective function when running calibration.
New Code:
objStr = "objfunc(" + DataAddress + "," + CStr(opt) + "," + ParaAddress + ")" ' Modified by Bongghi on July 1, 2006
Cells(1 + opt, 5) = "=" + objStr ' Modified by Bongghi on July 1, 2006
Cells(1 + opt, 6) = objStr ' Added by Bongghi on July 1, 2006
Glitch Fix Report #44
Date: 2 July 2006
Module: Optimization
Procedure: setparms2, objfunc
Original Code:
For i = 2 To 16 ' need to change if parameters are added
If Not Worksheets("Calibrate").Cells(i, 3).value = "" Then
num = num + 1
nlist(num) = i - 1
plist(num) = Worksheets("Calibrate").Cells(i, 2).value
End If
Next i
...
If IsNumeric(c.value) Then ssq = ssq + (c.value - gout(i)) ^ 2
Problem and Suggested Fix:
To choose a calibration parameter, the user should now enter “Y” or “y” in the column “C” of the “calibrate” worksheet (previously, entering “N” or “n” was also interpreted as choosing the parameter, possibly creating some confusion). Also, now the blank cells in the calibration dataset are interpreted as “missing data” (previously, the blank cells were interpreted as measurements having zero values).
New Code:
For i = 2 To 16 ' need to change if parameters are added
If UCase$(Worksheets("Calibrate").Cells(i, 3).value) = "Y" Then ' Modified by Bongghi Hong on July 2, 2006
num = num + 1
nlist(num) = i - 1
plist(num) = Worksheets("Calibrate").Cells(i, 2).value
End If
Next i
...
If IsNumeric(c.value) And Not IsEmpty(c.value) Then ssq = ssq + (c.value - gout(i)) ^ 2 ' Modified by Bongghi Hong on July 2, 2006
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- order of operations worksheet bloomfield college
- amortization schedule step by step instructions
- safety health management system guidelines
- math grade 6 ratios rates percents model curriculum unit
- the university of tennessee at chattanooga university
- gwlfxl user s manual cornell university
- solver furman university
- home phoenixville area high school
- worksheet madison west high school
Related searches
- cornell university data analytics program
- cornell university data analytics certificate
- cornell university business analytics
- cornell university business
- cornell university johnson business school
- cornell university college of business
- cornell university college report
- cornell university reputation
- cornell university data analytics
- cornell university dyson business school
- cornell university johnson
- cornell university johnson school