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