Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / June 2007

Tip: Looking for answers? Try searching our database.

how to shorten InStr statement

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bryan - 08 Jun 2007 22:02 GMT
Any suggestions on how to shorten this If...InStr statement?

If _
               InStr(LCase(Selection), "park city") Or
InStr(LCase(Selection), "deer valley") Or InStr(LCase(Selection),
"snyderville") Or InStr(LCase(Selection), "kimball junction") _
           Then strExclude = strExclude & " | Park City area"

Signature

Bryan

Jay Freedman - 09 Jun 2007 01:38 GMT
>Any suggestions on how to shorten this If...InStr statement?
>
[quoted text clipped - 3 lines]
>"snyderville") Or InStr(LCase(Selection), "kimball junction") _
>            Then strExclude = strExclude & " | Park City area"

In one sense, that statement is already just about as short as it can
get. It would be easier to read if you use more continuation
underscores and align the parallel parts, like this:

   If InStr(LCase(Selection), "park city") Or _
      InStr(LCase(Selection), "deer valley") Or _
      InStr(LCase(Selection), "snyderville") Or _
      InStr(LCase(Selection), "kimball junction") _
   Then strExclude = strExclude & " | Park City area"

Although it's longer and more complicated, the following is easier to
maintain if the list of test phrases might grow very long, or if you
decide to import the list from a spreadsheet or a database; the only
thing that needs to change is the assignment of the array.

   Dim strExclude As String
   Dim Arry As Variant
   Dim idx As Long
   Dim bExclude As Boolean
   
   Arry = Array("Park City", "Deer Valley", _
                "Snyderville", "Kimball Junction")

   bExclude = False
   For idx = 0 To UBound(Arry)
       If InStr(LCase(Selection), LCase(Arry(idx))) Then
          bExclude = True
          Exit For
       End If
   Next
   
   If bExclude Then
       strExclude = strExclude & " | Park City area"
   End If

I'll guess, though, that you have lots of phrases like  " | Park City
area", each one with a handful of inputs to match. In that case, you
should set up a data structure that lets you cycle through each one in
turn. Probably an array of Variants, with the replacement phrase as
the first entry in each variant.

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
Julian - 22 Jun 2007 01:53 GMT
And not smaller but more efficient to do LCase only once and store the result
in a variable... arrays tend to grow <g>

> >Any suggestions on how to shorten this If...InStr statement?
> >
[quoted text clipped - 51 lines]
> Email cannot be acknowledged; please post all follow-ups to the
> newsgroup so all may benefit.
Harold Druss - 10 Jun 2007 12:47 GMT
> Any suggestions on how to shorten this If...InStr statement?
>
[quoted text clipped - 3 lines]
> "snyderville") Or InStr(LCase(Selection), "kimball junction") _
>            Then strExclude = strExclude & " | Park City area"

Another way to consider:

Select Case LCase(Selection)
    Case "park city", "deer valley", "kimball junction"
         strExclude = strExclude & " | Park City area"
End Select

Good luck
Harold

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.