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.