##################################
# The Scour Risk Assessment Tool #
# released June 30, 2006 #
##################################
--------------------
CONTACT INFORMATION
--------------------
GKY & Associates, Inc.
5411-E Backlick Road
Springfield, VA 22151
703-642-5080
http://www.gky.com
--------------------
GENERAL INFORMATION
--------------------
The Excel workbook entitled "Scour Risk Analysis Tool with VBA.xls" was
developed in Microsoft Excel 2003 for Windows XP. It contains VBA macros for
adding and editing bridge records that are designed to help the user correctly
enter and manage the input data. Thus these macros must be enabled when the
workbook is opened in order to use the user input forms. Otherwise, you will
need to be careful to follow the directions for inputing records given below.
This tool (i.e. the Excel workbook) is intended to help States apply the
"Scour Risk Management Guidelines" section of the NCHRP 24-25 report entitled
"Guidelines for Risk-Based Management of Bridges with Unknown Foundations".
See the following URL's for more information:
The Project: http://www4.trb.org/trb/crp.nsf/All+Projects/NCHRP+24-25
NBI Database: http://massroads.com/nbi/
NBI Coding Guide: http://www.fhwa.dot.gov/BRIDGE/bripub.htm
The basic idea of this tool is to provide a spreadsheet application to do the
following things:
1) List the required input fields for the guidelines in an easy-to-use format.
2) Assist the user in providing the input data needed to analyze a bridge.
3) Analyze the supplied input data according to the "Scour Risk Management
Guidelines" in the report, and determine the recommended scour risk
management plan
4) Show a flow chart that illustrates the logic used to determine the
recommended scour risk management strategy
-------------
INSTRUCTIONS
-------------
These instructions assume that you know how to enable macros in an Excel
workbook, and how to generally navigate and use Excel. They also assume that
you are familiar with the reports referred to in the URL's above.
There are four visible worksheets in this workbook. The first worksheet,
entitled "Bridge Summary", contains equations that reference data stored in
the other three worksheets. All but one cell in this worksheet are protected
to prevent changes to those formulas which could "break" the logic. The
unlocked cell, C1, contains a menu that allows you to select, via Bridge ID
(NBI item 8), a bridge that is recorded in the other three worksheets. This
menu automatically updates its list to include any bridges that you enter into
this workbook, as described in the next paragraphs. The "Bridge Summary"
worksheet has three basic sections: the first section lists the input data for
the selected bridge, the second section shows the scour risk analysis
calculations and the recommended risk management plan, and the final section
shows a flow chart illustraing the logic used to obtain the recommended plan.
Note that you may have to scroll to the right or left to view the desired
section. The page layout of this worksheet is set up to print on two landscape
pages (i.e. normal 8.5" by 11" paper). You can also hover your mouse over many
of the cells to see comments about each parameter. Commented cells have a
small red arrow in the upper-right-hand corner, and these comments
generally explain either the meaning of a parameter or notes regarding its use
in the risk analysis. The meaning of the NBI parameters can be found in Report
No. FHWA-PD-96-001 and its attending Errata Sheet ("Recording and Coding Guide
for the Structure Inventory and Appraisal or the Nation's Bridges", published
in Dec. 1995; see the URL above).
The other three visible worksheets: "NBI|Priority Data", "Economic|Use Data",
and "Construction Mitigation Data", contain the actual data for the bridges.
The input data is organized such that each bridge is stored in a separate row
on these three worksheets, and each input parameter is stored in a separate
column. The three separate worksheets correspond to different categories of
input parameters. Note that the input data for a particular bridge occurs on
the same row in all three data worksheets. Also note that a few of the column
headers have comments that can be viewed by hovering the mouse above the
commented cell.
There are two basic ways to enter new records into this workbook.
1) The first, which is recommended for those who are first learning how to use
this tool, is to press the "Add new record" button on the "Bridge Summary"
worksheet. This brings up a dialog box that has input fields for all of the
required input parameters. Some of these form fields also constrain the
list of selections to ensure that the analysis tool can properly interpret
the input data. This form has three basic sections, which correpospond to
the three data worksheets. The "Step 1" section of the form asks for basic
information and certain NBI items for the bridge. The "Step 2" section asks
for information pertaining to the economic value of the bridge to different
users. There is a button in this section that will calculate and insert
default values for these economic use parameters based on the State,
functional classification, and ADT of the bridge (in "Step 1") into the
form. Note also that this button will change the High Priority setting
based on the funcional classification (NBI item 26) of the bridge. If a
bridge is not principal arterial but supports an evacuation route or
emergency services, then the user should change the High Priority setting to
"Yes". The third section asks for information regarding the cost of bridge
construction and of certain scour mitigation options. Once all of the data
has been specified, you can click the "Add new recod" button to add this
data to a new row in the three data worksheets. Once the data has been
added, the script also selects this new record in the "Bridge Summary"
worksheet to show you the analysis. Alternatively, if you change your mind
and do not want to add a new record, just click the "Cancel" button to exit
the input form.
2) The second way to enter new records is to manually type (or paste) new
records into new rows in the appropriate columns and worksheets. Just be
careful that the format of each parameter is consistent with the example
row (see the NCHRP 24-25 report).
Note that the "Bridge Summary" worksheet and the input forms will not work
correctly if you enter two bridges with the same Bridge ID. If you DO enter
more than one record with the same Bridge ID, you will need to manually
change one of the Bridge IDs before the tool will work correctly again.
Whenever you add new records to the workbook, be sure to save the workbook
when you are finished if you want to keep these records. This workbook only
allows you to see the scour risk management plan for one bridge at a time. To
see more than one at a time, you can always copy the management plan for each
bridge to another worksheet/workbook (using "Paste Values"). We sincerely hope
this tool helps you understand how to apply the scour risk management
guidelines for bridges with unknown foundations in your State!
There are several important points to remember when editting records.
1) The "Edit this record" on the "Bridge Summary" worksheet button and form is
very similar to the "Add new record" button and form, except that it allows
to you alter the bridge record that you are currently viewing in the
"Bridge Summary" worksheet.
2) The bridge data shown in row 5 (with the cyan color and Index "Ex:") of
these worksheets should not be altered or removed, because the VBA input
form references this row for its default values. The first two records
(rows 6-7) are given as an example, and record 1 (row 6) is the default
bridge initially shown in the "Bridge Summary" worksheet. Note the units
for each parameter, and try to follow the given example when entering new
records.
3) If you need to delete a record, there are two points of caution. First,
make sure you delete the record from all three yellow-tabbed data
worksheets, not just from one or two. Second, if you delete a record that
is not the last record and you want the index field in the data worksheets
to line up with the record number shown in the "Bridge Summary" worksheet,
then you may need to renumber the index field in each data worksheet
(e.g. 1,2,3,4; not 1,2,4,5).
-----------
DISCLAIMER
-----------
THIS WORKBOOK IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTY OF ANY KIND,
EITHER EXPRESSED OR IMPLIED, INCLUDING, WITHOUT LIMITATION, WARRANTIES THAT
THE WORKBOOK IS FREE OF DEFECTS, MERCHANTABLE, FIT FOR A PARTICULAR PURPOSE OR
NON-INFRINGING. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE
WORKBOOK IS WITH YOU. SHOULD ANYTHING IN THIS WORKBOOK PROVE DEFECTIVE IN ANY
RESPECT, YOU (NOT THE INITIAL DEVELOPER OR ANY OTHER CONTRIBUTOR) ASSUME THE
COST OF ANY NECESSARY SERVICING, REPAIR OR CORRECTION. THIS DISCLAIMER OF
WARRANTY CONSTITUTES AN ESSENTIAL PART OF THE LICENSE TO USE THIS WORKBOOK. NO
USE OF THE WORKBOOK IS AUTHORIZED HEREUNDER EXCEPT UNDER THIS DISCLAIMER.
UNDER NO CIRCUMSTANCES AND UNDER NO LEGAL THEORY, WHETHER TORT (INCLUDING
NEGLIGENCE), CONTRACT, OR OTHERWISE, SHALL YOU, THE INITIAL DEVELOPER, ANY
OTHER CONTRIBUTOR, OR ANY DISTRIBUTOR OF THIS WORKBOOK, OR ANY SUPPLIER OF ANY
OF SUCH PARTIES, BE LIABLE TO ANY PERSON FOR ANY INDIRECT, SPECIAL,
INCIDENTAL, OR CONSEQUENTIAL DAMAGES OF ANY CHARACTER INCLUDING, WITHOUT
LIMITATION, DAMAGES FOR LOSS OF GOODWILL, WORK STOPPAGE, COMPUTER FAILURE OR
MALFUNCTION, OR ANY AND ALL OTHER COMMERCIAL DAMAGES OR LOSSES, EVEN IF SUCH
PARTY SHALL HAVE BEEN INFORMED OF THE POSSIBILITY OF SUCH DAMAGES. THIS
LIMITATION OF LIABILITY SHALL NOT APPLY TO LIABILITY FOR DEATH OR PERSONAL
INJURY RESULTING FROM SUCH PARTY'S NEGLIGENCE TO THE EXTENT APPLICABLE LAW
PROHIBITS SUCH LIMITATION. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OR
LIMITATION OF INCIDENTAL OR CONSEQUENTIAL DAMAGES, SO THIS EXCLUSION AND
LIMITATION MAY NOT APPLY TO YOU.