Scrutinizing Strings with InStr and Str Comp

VBA has a number of built-in functions for working with strings. InStr and StrComp are used to either look for the presence of one string inside another string or to compare two strings for equivalence.

InStr returns a variant (the subtype is long) that indicates the position of the first occurrence of one string inside another. The syntax of InStr is as follows:

InStr([start, ]string1, string2[, compare])

The start parameter is optional and specifies where InStr should begin searching for string2 within string1. If omitted, the search begins at the first character position. I prefer to explicitly put a 1 there even though it is the default.

The compare parameter is also optional and is used to specify a text comparison (A = a) or a binary comparison (A < a). You can use the defined constants vbTextCompare or vbBinaryCompare. The default if omitted is vbUseCompareOption, which performs a comparison using the setting of the Option Compare statement. If you don't use the Option Compare statement at the top of your modules, the default is to perform a binary comparison.

If either string is null, InStr returns null. If string1 is zero length, then InStr returns 0. If string2 is zero-length, then InStr returns whatever was specified as the start parameter. The only time you receive a number greater than zero is if string2 is found within string1.

Before I move on to StrComp, I suppose I should mention InStr's backward brother InStrRev, which works just like InStr except it starts at the end of a string if start is omitted and works right to left.

StrComp, meanwhile, is used to test for string equivalence. The syntax of StrComp is as follows:

StrComp(string1, string2[, compare])

The optional compare parameter is used in the same manner as it was for InStr. If string1<string2 then Str-Comp returns -1. If string1=string2 then StrComp returns 0. Finally, if string1>string2 then StrComp returns 1. The only exception to these return values is in the event that either string1 or string2 is null, in which case StrComp also returns null.

to access a Workbook object through the Workbooks object by referring to it by name and the workbook is not open, a run-time error occurs. Because we specified On Error Resume Next, when an error occurs, the procedure executes the next line that sets IsWorkbookOpen to false. If the workbook is open, StrComp returns 0, and the function returns the default value (IsWorkbookOpen = True) that you set at the beginning of the procedure.

Specifying Specific Collection Objects

Say "specifying specific" three times as fast as you can. If you can do that, you can understand this section. You need to build on your understanding of collection objects and their relationship to the objects they contain. In particular, you need to come to grips with the different ways to work with individual items within a collection.

As I mentioned earlier in the chapter, all collection objects have an Item property that you can use to refer to individual items within the collection. For the vast majority of collections, the Item property is the default property. This means that you can access the property without specifying it. The following example demonstrates the various ways you could refer to an item within a collection. This example uses the Worksheets collection object.

Sub ReferringToItems()

' Refer to a worksheet by index number Debug.Print ThisWorkbook.Worksheets(1).Name ' once again, but with feeling Debug.Print ThisWorkbook.Worksheets.Item(1).Name

' Refer to a worksheet by name

Debug.Print ThisWorkbook.Worksheets("Sheet1").Name ' and again using Item...

Debug.Print ThisWorkbook.Worksheets.Item("Sheet1").Name End Sub

Each line in this procedure refers to the same item within the Worksheets object. You can refer to an item by its position or index within the collection or by using its name.

Now you are probably thinking, "Steve, you have told me three times already that it is best to be explicit." It is. That is still good advice. Referring to individual items within a collection is such a common occurrence within your procedures, and using the default Item property without specifying it is such a frequently used and understood practice that, in this instance, it is OK to ignore this guideline.

NOTE Understanding how to refer to individual items within a collection object is crucial to using the Excel object model. You'll see this technique used throughout the rest of the book.

Was this article helpful?

0 0

Post a comment