Click for next page ( 33


The National Academies | 500 Fifth St. N.W. | Washington, D.C. 20001
Copyright © National Academy of Sciences. All rights reserved.
Terms of Use and Privacy Statement



Below are the first 10 and last 10 pages of uncorrected machine-read text (when available) of this chapter, followed by the top 30 algorithmically extracted key phrases from the chapter as a whole.
Intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text on the opening pages of each chapter. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

Do not use for reproduction, copying, pasting, or reading; exclusively for search engines.

OCR for page 32
32 APPENDIX M User Operational Manual

OCR for page 32
33 TABLE OF CONTENTS 1. Requirements...................................................................................................................34 2. Starting the Program ......................................................................................................34 2.1. Excel 2003 .....................................................................................................................34 2.2. Excel 2007 .....................................................................................................................35 3. Tool Input and Execution...............................................................................................36 3.1. Tool Input Parameters ...................................................................................................36 3.2. Reset the Tool................................................................................................................39 3.3. Running the Tool...........................................................................................................39 4. Output and Interpretation .............................................................................................39 4.1. Color Coding .................................................................................................................40 4.2. Parameter Notes ............................................................................................................42 4.3. Screening Model ...........................................................................................................43 4.4. Emergency Response Guidebook (ERG 2008) .............................................................45 5. Troubleshooting ..............................................................................................................46 5.1. Adjusting Macro Security .............................................................................................46 5.2. Adding the ERROR Function for Screening Model .....................................................48 5.3. Spreadsheet-Related Problems ......................................................................................52 5.4. General ..........................................................................................................................53

OCR for page 32
34 1. REQUIREMENTS - Excel (Program tested using versions 2003 and 2007) - A Microsoft Windows PC (Currently not available in Mac) - At least 256 MB of system RAM - Recommended: 3.0+ Ghz CPU (2.0+ Ghz Parallel or Multi-core CPU) 2. STARTING THE PROGRAM The tool is designed in Excel using Visual Basic for Application (VBA), requiring Excel Macros to operate. Macros are written inside the tool to operate the toolbar buttons or help icons and repeat the steps of common calculations. 2.1. Excel 2003 Depending on your macro security settings, you may see one of the following three messages when you open the Excel file: 1) 2) 3)

OCR for page 32
35 If you see the first dialog box, please select the "Enable Macros" button and the tool will initialize. The second and the third dialogue boxes will require you to reduce your security level in Excel (Please see the troubleshooting section 5.1 for detailed assistance). Select the following: "Menu," then "Tools," then "Options," then "Security," and then click on "Macro Security." Lower the security level to "Medium or Low." This step requires that the user restart the Excel Software and reopen the Chemical Mixture Tool. Once the macro has been allowed to run according to the instructions above, the tool will show the front page in Excel as shown below. Clicking the "RUN CMT" button will show the input form and continue the tool process. 2.2. Excel 2007 Depending on your macro security settings, you may see the following warning when you open the Excel file:

OCR for page 32
36 Click the "Options" button, and then choose the option: "Enable this content." Click "Okay." If you do not see this item and did not receive the above warning, your security settings are too strict (please refer to the troubleshooting section 5.1 for assistance). Once the macro has been allowed to run per the above instructions, you should see the tool front page in Excel as below. Clicking the "RUN CMT" button will show the input form and continue the tool process. 3. TOOL INPUT AND EXECUTION 3.1. Tool Input Parameters Interface and Help Button

OCR for page 32
37 On the Tool input interface, click the "Help" button on the right top corner and the Help menu will open to illustrate the input interface functions as shown below. 1) Mixture Name: Enter the mixture name. The default is "Mixture, m." 2) Temperature (Required): Enter the desired temperature. 3) Temperature Unit: Unit of associated temperature. Default is Kelvin. 4) Component Fraction: Method by which ratio of components will be defined. 5) Page Selection: Once a certain number of components have been entered, additional pages will appear and can be toggled between here. 6) Component Input: Enter the component by either name or CAS number. 7) Ratio of Components: Based on (4), enter the amount of each component in the mixture. 8) Function Buttons: 1. Search: Allows a search of the registry by CAS or name (details below). 2. Clear Selections: Resets the form. 3. Run: Begins the calculation process and produces output. 9) Total: Displays the sum of (7). Must be 100 % by Mass or Volume; 1.0 by Molar Fraction for the tool to proceed. 10) Reset Tool: Click to reset the tool at various mixture-water ratios.

OCR for page 32
38 11) Help: Illustrate the Input Interface and functions. Searching the Compound Registry When the "Search" button is clicked on the main form, the above window will open. Using the radio buttons, select which registry you would like to search. The drop down menu will allow you to limit the way in which you search the registry to items which begin with your data entry, items which end with your data entry, or items which contain your data entry at any point. After completing it, click the "Search" button on this form, and results will be displayed (note: entering nothing in the input box will result in all items in the registry being returned alphanumerically). To return a component to the main form, select it from the resulting list and click the "Add to Mixture" button. Only one anonym of a chemical is in the tool database, therefore, searching by CAS# will be the most convenient means to find an input component. If you do not see the component you are looking for, you may use the "Clear" button to reset this form and try again. If a desired chemical cannot be found either by chemical name or CAS# search, the desired chemical is not in the tool database.

OCR for page 32
39 3.2. Reset the Tool The nonaqueous phase liquid (NAPL)-groundwater interfacial equilibria are environmentally significant for the fate and transport of a solute in the field. The solubility or partitioning of a solute in a mixture is a function of the fraction of the compound in the mixture, the presence of cosolvents in the mixture, and the mixture-to-water ratio. For example, ethanol in ethanol- blended gasoline will partition into aqueous phase and, only at certain concentration levels (i.e., 5% volume fraction in water), subsequently increase the solubility of hydrocarbons in gasoline. Conservatively, the tool sets the default NAPL-water volume ratio as 1:1 to simulate the scenarios of large volume spills or the near contaminant zone, where significant cosolvent may occur. The actual ratio may range from 1:1 to 1:10 depending on the incident scenarios. Every time when the tool is opened, it is automatically reset to a 1:1 ratio. Please enter the new ratio for other cases. 3.3. Running the Tool After the "Run" button is clicked on the main form, a series of simple checks will be performed to ensure the input is complete. If it is, an action bar and series of status messages will appear to inform you of the current action the tool is taking and to let you know that it is working. This may take several minutes based on the number of components, path through the tool, and CPU speed of your computer. 4. OUTPUT AND INTERPRETATION After the tool has finished running, an output interface will be displayed as shown as below. The default output page will provide a summary table of the fate and transport property parameters of your input mixture as well as a number of calculated values further to the right of what is shown here. In addition, there are four buttons (1 through 4) and a "Help" button (5) that will display a basic summary of each button's function from within the tool itself.

OCR for page 32
40 4.1. Color Coding The "Color Coding" button (1) is designed to compare the properties of a component to its pure phase using different colors. Clicking the button will toggle the color coding on and off. The "Color Coding" button (1) will shade parameter Water Solubility through Anaerobic Half-Life time based on the specific parameter in that column in the mixture compared to that same parameter as a pure compound. The Lime Green color represents the ratio of the property less than 5.0, which indicates that there are no significant changes in the property of the mixture compared to the pure chemical. The Orange color represents the ratio of the property between 5.0 and 10.0, indicating that there are slight changes in the property of the mixture compared to the pure chemical. The Red color represents the ratio of the property greater than 10.0, indicating that the chemical mixture may have a dramatic effect on the property of a component. The Blue color highlights the major mixture NAPL transport properties in the unsaturated zone.

OCR for page 32
41

OCR for page 32
42 In addition, a note (shown above) will be added to each Orange/Red cell, which will show the associated value when the compound is not mixed as well as the ratio between the two. It should be noted that even though the color change from green to red indicates the severity of the property change of a component in a mixture compared to its pure phase, the red color does not guarantee the mixture is not acceptable considering the uncertainty of the tool. 4.2. Parameter Notes The "Parameter Notes" button (2) is designed to interpret the physical and chemical parameters and terms used in the output table. When this button is selected, a note box will open as below to

OCR for page 32
43 interpret the parameters and definitions in the output table. Click the "Return to Table" button to return to the output interface. 4.3. Screening Model The "Go to Component Plume" button (3) is designed to simulate a component plume in a saturated groundwater aquifer with default hydrogeologic characteristics. Select any component and then click the "Go to Component Plume" button and it will take the user to an interface of the Domenico Analytical Model designed by the research team, where a plume of the component is shown. The default simulation time is one year. The "Return to Table" button below the plume illustration will return the user to the main output sheet. Located to the right of the plume illustration are the entry parameters used to generate it. These are the generic parameters, which can be adjusted to fit the specific conditions in question. The concentration, partition coefficient, and half-life are calculated by the mixture tool (blue borders with a red font) and should not be changed. Please use the "Help" button below the input area to access detailed data entry instructions as shown below. Click the "Return to Plume" button to return to the screening model interface.

OCR for page 32
44

OCR for page 32
45 4.4. Emergency Response Guidebook (ERG 2008) The "Emergency Response Guide" button (4) is designed to assign each chemical component an Emergency Response Guidebook (ERG 2008), which was developed by the United States Department of Transportation (U.S. DOT). Select one component and click the "Emergency Response Guide" button, and it will take the user to the interface of appropriate emergency response guides according to the U.S. DOT Hazardous Class and United Nations Identification Number (UN#) assigned to the component for proper shipping as shown below. Clicking the "Return to Table" button at the bottom of the ERG will take the user to the current output interface.

OCR for page 32
46 5. TROUBLESHOOTING 5.1. Adjusting Macro Security Excel 2003: From the menu, select "Tools" then "Options" to open the above screen. Select the Security Tab from the groups at the top of this menu, and then click the "Macro Security" button from the bottom of the Security Tab. This will open the following menu: Here the user can change the security settings. "Medium" is the recommended setting which will result in the prompt mentioned above. A higher security setting will not allow the tool to run, and a lower security setting may allow potentially malicious macros to run without prompting, if the user is concerned. Once this setting has been changed, the tool must be closed and re-opened in order to proceed.

OCR for page 32
47 Excel 2007: Using the Office button (pictured to left) will show the following: Here, the user will select the highlighted button at the bottom (Excel Options), which will allow access to a number of application settings. On the next window, navigate first to the "Trust Center" (1), and then open the "Trust Center Settings" (2).

OCR for page 32
48 Finally, the user can change the security setting under "Macro Settings." Selecting "Disable all macros with notification" is recommended and will result in the same behavior described in this document. Anything higher will prevent the tool from running, while anything lower will permit potentially malicious macros to run without prompt. Once this setting has been changed, the tool must be closed and re-opened in order to proceed. 5.2. Adding the ERROR Function for Screening Model The tool will automatically turn on the Analysis Toolpak in Excel as an Add-in to run the screening model. If it fails to turn on the ERROR Function in the Analysis Toolpak, a note will show as pictured below. If this function is not available, the data source calculations for the plume will result in "#VALUE!" Or "#NAME?" errors, and no chemical plume will be shown.

OCR for page 32
49 Next, install and load the Analysis ToolPak add-in following the instructions on the following page. Excel 2003: From the menu, select "Tools" and then "Add-Ins" to open the second screen below. From this menu, check the boxes next to both "Analysis ToolPak" and "Analysis ToolPak VBA," then click OK. Close Excel and reopen it, then run the Chemical Mixture Tool; the issue associated with the screening model plumes should be gone.

OCR for page 32
50

OCR for page 32
51 Excel 2007: Using the Office button (pictured to left) will show the following: Here, the user will select the highlighted button at the bottom (Excel Options) which will allow access to a number of application-wide settings. Once the "Options" window is open, select the "Add-Ins" tab on the right. This will display a window which looks like the image below. Select the "Go" button at the bottom of this window.

OCR for page 32
52 The following menu will open. Simply check the boxes next to "Analysis ToolPak" and "Analysis ToolPak VBA," then select "OK." Close Excel and reopen it; then run the Chemical Mixture Tool, and the issue associated with the screening model plumes should be gone. 5.3. Spreadsheet-Related Problems The Tool was built in the Excel spreadsheet environment and spreadsheet-related problems may occur for either the cell/table format or the component plume due to the calculation process. Below are some anticipated problems and interpretations: 1) #### is displayed in a number box in the Output Table: The cell format is not compatible with the value, (e.g., the number is too big to fit into the window). To fix this, select the cell, pull down the format menu, select "Cells" and click on the "Number" tab. Change the format of the cell until the value is visible. If the values still cannot be read, select the format menu, select "Cells," and click on the "Font" tab. Reduce the font size until the value can be read. 2) #DIV/0! is displayed in the raw data for the screening model plume: The raw data for the screening model is located in the bottom of the screening model interface. The most common cause of this problem is that some input data in the screening model are missing. In some cases, entering a zero in a box will cause this problem. Doublecheck to make certain that all of the input cells required for your run have data. In addition, as described in 5.2, the ERROR Function required for the Screening Model may cause this issue (Please see the troubleshooting section 5.1 for detailed assistance).

OCR for page 32
53 3) The plume graphs seem to move around or change size: The screening model plume is generated by Excel Standard Chart Wizard. This is a feature of Excel. When graph scales are altered to accommodate different plotted data, the physical size of the graphs will change slightly, sometimes resulting in a graph that spreads out over the fixed axis legends. The research team has adjusted the graph scale (i.e., axis scale and the plume display contours) according to the plume sizes. However, users still can manually resize the graph to make it look nice again by double-clicking on the graph and resizing it (refer to the Excel User's Manual). 5.4. General Most unexpected, abnormal behavior can be resolved by: Restarting Excel, Rebooting your computer, or Returning to the original zipped document and re-extracting a fresh copy of the tool. This tool's main limitation is that it cannot be used for all chemical substances, with the database of 740 components derived from literary research and interviews with professional personnel based on hazardous material classification and commodity flow survey and incident reports. The intended application domain is for liquid organic chemicals, particularly petroleum and related compounds. Inorganic and organometallic chemicals generally are outside the tool's domain. If the desired chemical is out of the tool database, the current tool will not be able to generate the property for the particular mixture and simulate the fate and transport in subsurface. Detailed information concerning the development, performance, and application of the tool, as well as the individual programs (e.g., Universal Functional Activity Coefficient (UNIFAC) model, Raoult's Law, and the Cosolvency-Log Kow Model) contained within it, can be found within the theoretical section of the tool development manual. If there are any issues that are not resolved by the manual, please contact HSA Engineers & Scientists at (239) 936-0789 or hsa- ftm@hsa-env.com .