These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 9, 2010

VIN Decoder

Executive Summary

The Vehicle Identification Number (VIN) is a unique serial number that identifies every automobile manufactured from 1981 on to today. Prior to 1981, there was no accepted standard for these numbers, so different manufacturers used different formats. Since 1981, VINs consist of 17 characters which do not include the letters I (i), O (o), or Q (q) (to avoid confusion with numerals 1 and 0).The VIN serves as a serial number that contains information about the vehicle manufacturer, where the vehicle was manufactured, the manufacture year, and etc.

Although there are several websites that allow you to decode a VIN, none of the websites allow you to enter a list of VINs and have that list decoded. Dr. Snow of the Supply Chain department needed to decode over 250,000 VINs and it would take him a very long time to enter each one into a website, have it decode the VIN, copy the information, then sort it.

Since the information to write a VIN decoder is not easily available I wrote a program that reads a text file that contains multiple VINs, validates the VINs and uses a website (http://www.decodethis.com/) to decode the VIN. Once the VIN is decoded this information is downloaded and summarized. Once the list of VINs has been decoded the user can use a form to build a custom list of VINs and decoded information given the make, model, year, trim level, manufacture location, body style, engine type, and drive line.

http://files.gove.net/shares/files/10f/aec57/Online_VIN_Decoder.pdf

http://files.gove.net/shares/files/10f/aec57/Online_VIN_Decoder.xlsm

http://files.gove.net/shares/files/10f/aec57/Online_VIN_Decoder_-_Small_List_of_VINs.txt

http://files.gove.net/shares/files/10f/aec57/Online_VIN_Decoder_-_Medium_List_of_VINs.txt


5 comments:

  1. This is pretty awesome and could potentially save me hundreds of hours. I've gotten as far as validating the VINs, but when I go to Decode them I run into a problem. If I'm able to figure out what the problem is and fix it, then I'm sending presents to Dr. Allen and the student behind this project, and sending my niece to Brigham Young for college. Seriously.

    Unfortunately I have no idea what I'm doing. When I click on the Decode button a Microsoft Visual Basic message pops up that says, "Run-time error: '9':
    Subscript out of range," and gives me the option to click End or Debug. When I click Debug, another page pops up that has a lot of code looking stuff on it. On that page there is a yellow arrow pointing to a highlighted line in the code looking stuff. The highlighted text says, "Sheets("vin data").visible = True." I don't know what to do from here... If anyone here knows what to do I would really appreciate the help. Email me at whitneycollins@force911.com, or call 678.919.7911

    I LOVE THIS BLOG!

    ReplyDelete
  2. Hi, thanks for the summary. BTW, here's a free web service VIN decoder. It provides VIN decoder API for developers.

    ReplyDelete
  3. This explains more about a certain vin decoder and what information are included in a car vin lookup report: https://www.youtube.com/watch?v=o4EYMXAFReI

    ReplyDelete
  4. Hello - I tried to access the links above, but they did not open. Does someone have this information? Thank you - John

    ReplyDelete

Blog Archive