Page 228 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Case "FN"
ActiveCell.Off set(0, 1).Value = "Furniture"
Case "SP"
ActiveCell.Off set(0, 1).Value = "Supplies"
Case Else
ActiveCell.O ffset(0, 1).Value = "Error"
End Select

Next MyCell
End Sub

For more information on and a practical example of looking up values from existing lists, including
those stored in databases, see Chapter 22: “Excel and ADO Data Sources.”

Returning Characters from Arbitrary Positions in a String

Life is so much easier when you know what’s coming, and working with spreadsheet data is
no exception. Well-ordered data streaming in from outside sources is one of the little joys in
an Excel programmer’s life, because it means you can reach into the data string and pull out
what you need. One such example in the United States is the Vehicle Identification Number
(VIN). VINs are 17 characters long and encode all of the pertinent information about a car:
Chapter 9
the make, the model, the color, the year manufactured, the plant where the car was manufac­
tured, and so on. When you have a known data structure, you can use the MID function to
pull out just the characters you need for a procedure.

Unlike the Left and Right functions, which pull data from the beginning or end of a string, the
MID function pulls a set number of characters from the body of the string. The MID func­
tion’s syntax is similar to both the Left and Right functions, with the only difference being
that you define the position of the first character to return and the number of characters to
be returned.

MID(string, start, length)
To pull characters in positions 4 through 8 (a total of five characters) from the value in cell
D5, you would use the following code (which assumes you created the variables strCode and
strDetails earlier):

strCode = Range("D5").Value
strDetails = MID(strCode, 4, 5)

Finding a String Within Another String

You might have read the heading for this section and wondered why in the world someone
would want to find a string within another string. In the world of genetics, you could search
for a specific protein sequence to locate a chromosome, but if you’re doing that you most

202
Part 4: Advanced VBA