« Las Vegas as an oyster | Main | Microsoft Project Server 2007 Service Pack 1 Release Date »

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")
RELATED POSTS
  • MS Project VBA - Trim Function
  • VBA returns in Excel 2011 for Mac
  • Office VBA for Mac After 2008
  • Analyze Microsoft Project Resource Usage Data In Excel
  • VBA Writing to a text file (MS Project, Excel)
  • Telling Time - ProjDateDiff, VB DateDiff and Application.DateDifference
  • Setting Microsoft Project Level Custom fields using VBA
  • Iterating through Microsoft Project Subprojects
  • Date Math - DateAdd, ProjDateAdd in Microsoft Project
  • Microsoft Project Undo Levels and Macros

  • Comments (2)

    Bruno Augusto:

    Hi all

    First, don't worry about my poor english. I don't have fluence at language and I don't trust in translators, but...I am trying....

    Buddies,

    I have a trouble. I developed a class in VBA to use in Project Professional. I have a MS Project Server and a lot of projects inside it. But I don't know how to overwrite/replace/deploy the code that I developed to all project that already were created.

    Anybody can help me?

    Regards.
    Bruno Augusto
    ---------------------
    Bruno, Copy the macro into the Enterprise Global.mpt file using the organizer. -Jack

    Peter Brooks:

    An equally useful companion function is InstrRev(), which allows you to begin the search from the end of the string.

    This is helpful when what you're searching for is the last occurrence of it in the main string.

    For example, if you're looking to isolate the name of a file from a string that includes the full path, the last occurrence of "\" marks the end of the path, and the very next character is the beginning of the file name.

    Using Instr() you could count through all the "\" until you reached the last one, but much quicker is to use InstrRev() to look for the first occurrence counting from the end.

    It should be noted that InstrRev() uses a slightly different arrangement of parameters compared with Instr():

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

    vs

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

    Best,

    Peter

    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

    About

    The previous article is Las Vegas as an oyster.

    The next article is Microsoft Project Server 2007 Service Pack 1 Release Date.

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34