Part 4: Advanced VBA in C#

Generating Denso QR Bar Code in C# Part 4: Advanced VBA

9

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)

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

Manipulating Data with VBA likely won t be working through an Excel worksheet to find that substring. Instead, you might have received rows of data that, through no fault of your own, were imported in Excel as a single cell per row. What s worse, the data fields aren t of a fixed length, so you can t use the MID function without a bit of tweaking. However, even if you are ever unfortunate enough to see data such as OI1800230IT7801CI486SPFX2D in a single worksheet cell, you can still find a way to read it if you re clever. Rather than keep you in suspense, you should know that the data actually breaks out this way: OrderID OI1800230, Item IT7801, CustomerID CI486, Shipping FedEx Second Day. But how to you find that out The method is equal parts cleverness and skill. The clev erness comes from marking the beginning of each field with a distinct code. In the example string just shown, the first seven characters represent the OrderID, OI1800230. The OrderID begins with the letters OI, which you can assume for the purposes of this example won t occur anywhere else in the string. The same marking technique is used to call out the Item number (IT), the CustomerID (CI), and the Shipping method (SP). Note

You could guard against any stray occurrences of the marker sequences by putting brackets around the marker, such as <OI> or <IT>. It s the same sort of markup system used in the Hypertext Markup Language (HTML) and the Extensible Markup Language (XML), and it works well for other systems that don t need to encode brackets as values.

When it comes to locating strings within other strings, having skill is operationally defined as knowing about the SEARCH and FIND functions. Both functions return the number of the character within a string at which a specific character or text string is found, but there are minor but important differences in how the functions operate. Here are the functions syntaxes:

SEARCH(find_text, within_text, start_num) FIND(find_text, within_text, start_num)

find_text is the text you want to find. If you use the SEARCH function, you can use the wildcard characters, question mark ( ) and asterisk (*), in find_text. A question mark matches any single character, whereas an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. The SEARCH function isn t case-sensitive, so searching for e will also find E. The FIND function is case-sensitive and doesn t allow wildcards you can think of it as a more precise version of the SEARCH function. With FIND, searching for e will not find E.

within_text is the text in which you want to search for find_text. start_num is the character number in within_text at which you want to start searching.

If you leave this parameter blank, the search will start from the beginning of the string (that is, start_num = 1).

9

Microsoft Office Excel 2003 Programming Inside Out The benefit of the SEARCH and FIND functions really comes to the fore when you combine them with the MID function. Once you ve used the SEARCH or FIND function to locate the start of two consecutive fields, you can use the MID function to draw in the part of the string you want. There is one more subtlety of which you need to be aware. Although the MID function is part of the standard VBA package, the SEARCH and FIND functions are not, so you ll once again need to use the Application.WorksheetFunction object to call the functions, as in the following example:

Application.WorksheetFunction.Search("IT", ActiveCell.Value)

Note The VBA function INSTR also returns the position of the character where a string begins within another string, but the function is the equivalent of the FIND function in that the INSTR function is case-sensitive. If you reconsider the nightmare scenario where order item records were imported incor rectly, you could use the SEARCH and the MID functions to find the beginning and the end of each field s values and write the values into a cell, as in the following procedure: