• Injury and Violence Prevention Program

• Just In

• Injury Prevention through the EMS and Trauma Care System

• Injury Data Tables

• InjuryRateAnalyzer

• Publications

• Program Contacts - Technical Assistance

• Related Sites

• Alternative File Formats

• Download PDF and/or Excel Viewers

As a courtesy to other publicly health entities,  this page provides you the pieces so that you can produce InjuryRateAnalyzer  workbooks using provided hypothetical data.*  Though not absolutely necessary, having familiarity with Excel's Visual Basic Editor (VBE) is useful.

The Three Needed Pieces

Production of an InjuryRateAnalyzer application  takes three pieces:

1. an injury database,
2. CreateRateAnalyzerWorkbook.xls,
3. an InjuryRateAnalyzer application.

An Injury Database

Injury data at WSDOH is contained in a computer running SQL Server in the form of a normalized database.  This data base contains hospitalization and death record data, by individual.  Within WSDOH, this data is made available for the "CreateRateAnalyzer.xls" (see below) using a series of SQL views that denormalizes and aggregates the injury data by injury mechanism and intent.  The database also contains population data, needed to calculate rates.

For demonstration purposes, an Access database file, "SampleVitalData.mdb", with hypothetical denormalized aggregated data can be downloaded here.  In order to limit its size, it contains hypothetical 2004 data for only two entities, King county, and the rest of Washington state combined.  In addition, since the data has its roots in actual data, to ensure confidentiality, any injury counts between 1 and 4 for any given injury in any given age group have been set to 0.

In order for the CreateRateAnalyzerWorkbook.xls application to work, the SampleVitalData.mdb database needs to be placed directly under the root of the C drive (C:\).

CreateRateAnalyzer.xls

This application produces a workbook with the county or EMS region worksheets from an injury database.  It is available here in the form of a ZIP file.  The one that is available for download here.  It is hard coded to obtain its data from "SampleVitalData.mdb."

an InjuryRateAnalyzer Application

The workbooks that CreateAnalyzerWorkbook.xls creates only contain data.  The rest of the application, all the computer code, is obtained by transferring the computer code from an existing InjuryRateAnalyzer  workbook to a new workbook that only contains county or EMS region data.  InjuryRateAnalyzer  applications are available here.

Directions for Creating an InjuryRateAnalyzer Application

Create a Workbook with Injury Data

Here are the steps to create a workbook with the needed data:

1. Download and unzip SampleVitalData.mdb and place it at the root of your C drive (C:\).
2. Download and unzip the CreateRateAnalyzerWorkbook.xls.  Make sure your Excel security settings are set to allow macros and computer code to run (see previous page for information).  Open CreateRateAnalyzeWorkbook.xls.
3. Open the Visual Basic Editor (VBE), either by pressing the key combination <Alt - F11> or through the menu bar at "Tools->Macro->Visual Basic Editor".
4. Open the Immediate window in the VBE by pressing the two key combination <Ctrl+G> or through the VBE menu bar at  "View->Immediate Window"
5. Type in "modRateAnalyzer.Run" and press <Enter>.

Add in Computer Code to Workbook

A workbook created using the directions in the "Create a Workbook with Injury Data" (above) has all the worksheets and all the data for an InjuryRateAnalyzer application, but it does not have any of the necessary computer code. Follow these steps to add the code:

1. Open the new workbook you have created, open an existing InjuryRateAnalyzer  workbook.
2. Open the VBE.
3. In the Project Explorer window, copy all the forms, modules and class modules from the existing InjuryRateAnalyzer application to the new workbook.  You should copy
   • 3 forms,
   • 2 modules, and
   • 6 class modules.
4. Open the code window for the existing InjuryRateAnalyzer application's "ThisWorkbook" Excel object.
   • Copy all the code and paste it into the code window for the newly created workbook's "ThisWorkbook" Excel object.
   • You can select all the code using the two key combination <Ctrl + a> and then copy using the two key combination <Ctrl + c>.
5. Open the code window for module "modCommon" that you moved to your newly created workbook from step (3).
   • If you have created a fatal injury workbook, uncomment out the block of code (making this code block active) in modCommon that begins with "ICD-10 constants..."  and comment out the block of code (making this code block inactive) that begins with "ICD-9 constants...".
   • If you have created a nonfatal injury hospitalization workbook, then naturally do the reverse.
6. Save and close the workbook.
7. From either the existing InjuryRateAnalyzer workbook, or from this workbook, move the worksheet named "8kl(kjWE#" to the newly created workbook.
   • Hide this worksheet if you wish.
8. Save and close the workbook.
9. Open the workbook.  If all the steps above have been successfully carried out, InjuryRateAnalyzer  should start up.

Technical Notes

As mentioned in the Statistical Calculations section of the InjuryRateAnalyzer homepage, InjuryRateAnalyzer uses the Chi-squared test for a two way table to determine whether any given rate is significantly different from its complement rate in either the All Ages column or in the Washington State worksheet.  The two Z-values for this test, one for testing against the All Ages data, and one for testing against the Washington State data, are pre-calculated by CreateRateAnalyzerWorkbook.xls  when an InjuryRateAnalyzer application is being produced.  Each rate has these Z-values incorporated into the comment attached to that rate's cell.  The comments are sized so that, ordinarily, the Z-values are hidden.  To see the Z-values inside a comment, you can resize the comment by going to the Insert menu and then clicking Edit Comment.

 * WSDOH policy states that all software produced by WSDOH where ownership is not specifically retained by a concern outside of WSDOH through a contract or agreement, is available to be freely shared with publicly supported entities