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 33
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 34
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 35
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 36
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 37
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 38
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 39
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 40
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 41
41
OCR for page 42
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 43
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 44
44
OCR for page 45
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 46
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 47
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 48
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 49
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 50
50
OCR for page 51
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 52
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 53
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 .
