The code after the break:
Public Function stateab(State As String)
Dim StateName(1 To 59) As String
Dim StateCode(1 To 59) As String
StateName(1) = "Alabama"
StateName(2) = "Alaska"
StateName(3) = "American Samoa"
StateName(4) = "Arizona"
StateName(5) = "Arkansas"
StateName(6) = "California"
StateName(7) = "Colorado"
StateName(8) = "Connecticut"
StateName(9) = "Delaware"
StateName(10) = "Dist. of Columbia"
StateName(11) = "Florida"
StateName(12) = "Georgia"
StateName(13) = "Guam"
StateName(14) = "Hawaii"
StateName(15) = "Idaho"
StateName(16) = "Illinois"
StateName(17) = "Indiana"
StateName(18) = "Iowa"
StateName(19) = "Kansas"
StateName(20) = "Kentucky"
StateName(21) = "Louisiana"
StateName(22) = "Maine"
StateName(23) = "Maryland"
StateName(24) = "Marshall Islands"
StateName(25) = "Massachusetts"
StateName(26) = "Michigan"
StateName(27) = "Micronesia"
StateName(28) = "Minnesota"
StateName(29) = "Mississippi"
StateName(30) = "Missouri"
StateName(31) = "Montana"
StateName(32) = "Nebraska"
StateName(33) = "Nevada"
StateName(34) = "New Hampshire"
StateName(35) = "New Jersey"
StateName(36) = "New Mexico"
StateName(37) = "New York"
StateName(38) = "North Carolina"
StateName(39) = "North Dakota"
StateName(40) = "Northern Marianas"
StateName(41) = "Ohio"
StateName(42) = "Oklahoma"
StateName(43) = "Oregon"
StateName(44) = "Palau"
StateName(45) = "Pennsylvania"
StateName(46) = "Puerto Rico"
StateName(47) = "Rhode Island"
StateName(48) = "South Carolina"
StateName(49) = "South Dakota"
StateName(50) = "Tennessee"
StateName(51) = "Texas"
StateName(52) = "Utah"
StateName(53) = "Vermont"
StateName(54) = "Virginia"
StateName(55) = "Virgin Islands"
StateName(56) = "Washington"
StateName(57) = "West Virginia"
StateName(58) = "Wisconsin"
StateName(59) = "Wyoming"
StateCode(1) = "AL"
StateCode(2) = "AK"
StateCode(3) = "AS"
StateCode(4) = "AZ"
StateCode(5) = "AR"
StateCode(6) = "CA"
StateCode(7) = "CO"
StateCode(8) = "CT"
StateCode(9) = "DE"
StateCode(10) = "DC"
StateCode(11) = "FL"
StateCode(12) = "GA"
StateCode(13) = "GU"
StateCode(14) = "HI"
StateCode(15) = "ID"
StateCode(16) = "IL"
StateCode(17) = "IN"
StateCode(18) = "IA"
StateCode(19) = "KS"
StateCode(20) = "KY"
StateCode(21) = "LA"
StateCode(22) = "ME"
StateCode(23) = "MD"
StateCode(24) = "MH"
StateCode(25) = "MA"
StateCode(26) = "MI"
StateCode(27) = "FM"
StateCode(28) = "MN"
StateCode(29) = "MS"
StateCode(30) = "MO"
StateCode(31) = "MT"
StateCode(32) = "NE"
StateCode(33) = "NV"
StateCode(34) = "NH"
StateCode(35) = "NJ"
StateCode(36) = "NM"
StateCode(37) = "NY"
StateCode(38) = "NC"
StateCode(39) = "ND"
StateCode(40) = "MP"
StateCode(41) = "OH"
StateCode(42) = "OK"
StateCode(43) = "OR"
StateCode(44) = "PW"
StateCode(45) = "PA"
StateCode(46) = "PR"
StateCode(47) = "RI"
StateCode(48) = "SC"
StateCode(49) = "SD"
StateCode(50) = "TN"
StateCode(51) = "TX"
StateCode(52) = "UT"
StateCode(53) = "VT"
StateCode(54) = "VA"
StateCode(55) = "VI"
StateCode(56) = "WA"
StateCode(57) = "WV"
StateCode(58) = "WI"
StateCode(59) = "WY"
If Len(State) = 2 Then
For i = 1 To 59
If StateCode(i) = State Then
stateab = StateName(i)
Exit For
End If
Next i
Else
For i = 1 To 59
If StateName(i) = State Then
stateab = StateCode(i)
Exit For
End If
Next i
End If
End Function
The important thing about this is that it's declared as a Function instead of Sub. It is also set up to accept a String as an argument.
The way the function works is simple. It checks the length of the String you passed it to see if it's possibly a postal code or a state name. If it's a state name, it counts through the StateName array until it finds a match and returns the value from the equivalent position in the StateCode array. If passed a postal abbreviation, it returns the full state name in a similar manner. It doesn't catch any errors or do anything fancy, but it seems to work okay. If I was going to change it, I'd probably have it trim the String that is passed to remove any extra white space. Also, I might want to add some variations of Washington, D.C.
It's very important to give your function a good, strong name. I named this function stateab, after St. Ateab, the patron saint of the post office.
If you've never created a function in Excel--as opposed to a macro--here is a quick tutorial.
When you first save your function and then try to call it in your workbook with =stateab(A1), you're going to get a Name? error. The problem with functions in Excel is that they only load correctly if you load them as an add in; otherwise, you have to specify a path to the sheet that houses the function, add it as a reference, and it still probably won't work.
Comparatively, creating an add in is dead simple. You want to create a separate workbook for your function called "YourNameFunctions" or something, but definitely don't put it in your personal.xls. Personal should be for macros, this new workbook will be for functions. You only need to save this function in the workbook using the visual basic editor, the rest of the workbook should be blank.
Once that is done, go to file and select "Save As". From the huge list of extensions, select ".xla". This is for Excel Add In. You'll want to save this file in your default Add-In directory for Excel. To find this, go to Tools > Add-Ins > Browse. The directory you start in will be the default directory, and you should put your file there. Alternately, you can use this browse button to find your file anywhere on your computer.
Once the file is saved, you should restart Excel (I think...). After that, when you go to Tools > Add-Ins, the file you just saved should show up on that list. Check the box next to it, and you're set. Test it out; the function should work fine without Name? errors or any other shenanigans. Like your other add ins, your function workbook should now load every time you start Excel.
Upcoming posts will cover basic census manipulation and a possible review of a productivity book: The 4-Hour Work Week
.-David
No comments:
Post a Comment