Microsoft Project VBA - the Instr function
The Instr
function is used to find out if one set of characters (string) is contained in another. It can be used in VBA macros (in Word, Excel, Project etc.) and also in Microsoft Project and Microsoft Project Server Custom Field Formulas. In Project this can be used to find out if a task name contains some special coding. For example perhaps you have used a special naming convention to separate time tracking tasks from other tasks and you need to roll them up separately. By using Instr you don't need to have the coding in a specific position. You can put it at the beginning, middle or end of the name and Instr will still find it.
Instr is also useful in string manipulation because it returns the position of the first occurrence of a string in another string. Using it in conjuntion with the left function you can strip out leading characters.
The InStr syntax is pretty simple:
InStr( [start], string_to_search_in, string_to_search_for, [compare] )
start
is optional. The default is to start at the first character, but if you want to skip the first character then you can set it to another value. This could be useful for looking for the second occurance of the string you are looking for. You could feed in the result + 1 of another instr
function to see if the string occurs again. Using this recursively you could count specific characters.
string_to_search_in
is the string that will be searched.
string_to_search_for
is the string to search for.
compare
is optional. By default it does a text compare so most likely you can leave it out. The valid choices are: vbBinaryCompare, vbTextCompare
and vbDatabaseCompare
. I'm not even sure what vbDatabaseCompare is so don't worry about setting compare unless you are doing something a bit more advanced.
Here are a couple of examples:
- InStr(1, "This is a time tracking task", "tracking") would return 16.
- InStr("This is a time tracking task", "is") would return 3.
- InStr(10, "This is a time tracking task", "t") would return 11.
- InStr("This is a time tracking task", "abalone") would return 0.
From the examples, you can see when it CAN'T find what it is looking for it will return 0. This is an important point to remember. The typical test I'd use in a custom field formula would be to test the return from instr in an iif formula. Maybe something like this:
iif(instr("My haystack","needle")>0,"ouch", "zzzzzzzzz")