National Academies Press: OpenBook
« Previous: Appendix H - Tool Design Process Example
Page 32
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 32
Page 33
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 33
Page 34
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 34
Page 35
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 35
Page 36
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 36
Page 37
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 37
Page 38
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 38
Page 39
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 39
Page 40
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 40
Page 41
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 41
Page 42
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 42
Page 43
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 43
Page 44
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 44
Page 45
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 45
Page 46
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 46
Page 47
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 47
Page 48
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 48
Page 49
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 49
Page 50
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 50
Page 51
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 51
Page 52
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 52
Page 53
Suggested Citation:"Appendix M - User Operational Manual." National Academies of Sciences, Engineering, and Medicine. 2010. Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents. Washington, DC: The National Academies Press. doi: 10.17226/14472.
×
Page 53

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

32 A P P E N D I X M User Operational Manual

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 Problem s ...................................................................................... 52 5.4. General .......................................................................................................................... 53

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)

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:

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

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.

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.

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.

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.

41

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

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.

44

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.

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.

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).

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.

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.

50

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.

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).

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.

Next: List of Acronyms and Symbols »
Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents Get This Book
×
MyNAP members save 10% online.
Login or Register to save!
Download Free PDF

TRB’s Hazardous Materials Cooperative Research Program (HMCRP) Report 2: Assessing Soil and Groundwater Impacts of Chemical Mixture Releases from Hazardous Materials Transportation Incidents explores a tool to help assess, classify, predict, and quickly communicate fate and transport characteristics of chemical mixtures released into the soil and groundwater as a result of hazardous materials transportation incidents. The tool can also be used to determine whether shipping certain chemicals separately or in mixtures will have significantly higher costs if an incident occurs and to estimate relative costs and timeframes of cleanup after an incident occurs.

The chemical mixture tool, a user guide, and the contractor’s final report for the project that produced HMCRP Report 2 are available on a CD-ROM that is included with the printed publication.

The CD-ROM included as part of HMCRP Report 2 is also available for download from TRB’s website as an ISO image. Links to the ISO image and instructions for burning a CD-ROM from an ISO image are provided below.

Help on Burning an .ISO CD-ROM Image

Download the .ISO CD-ROM Image

(Warning: This is a large file that may take some time to download using a high-speed connection.)

  1. ×

    Welcome to OpenBook!

    You're looking at OpenBook, NAP.edu's online reading room since 1999. Based on feedback from you, our users, we've made some improvements that make it easier than ever to read thousands of publications on our website.

    Do you want to take a quick tour of the OpenBook's features?

    No Thanks Take a Tour »
  2. ×

    Show this book's table of contents, where you can jump to any chapter by name.

    « Back Next »
  3. ×

    ...or use these buttons to go back to the previous chapter or skip to the next one.

    « Back Next »
  4. ×

    Jump up to the previous page or down to the next one. Also, you can type in a page number and press Enter to go directly to that page in the book.

    « Back Next »
  5. ×

    To search the entire text of this book, type in your search term here and press Enter.

    « Back Next »
  6. ×

    Share a link to this book page on your preferred social network or via email.

    « Back Next »
  7. ×

    View our suggested citation for this chapter.

    « Back Next »
  8. ×

    Ready to take your reading offline? Click here to buy this book in print or download it as a free PDF, if available.

    « Back Next »
Stay Connected!