We help IT Professionals succeed at work.
Get Started

InStr with Wildcards Function

JenH2 asked
Last Modified: 2012-05-09
I'm trying to use a function I found here: http://www.visualbasic.happycodings.com/Other/code26.html

However, when I run my script, I get the following error:

Line:    1
Char:   29
Error:   Expected ')'
Code:  800A03EE

For the life of me I can't figure out why it would look for a ) to be at that character spot.  Any ideas?

Function InStr2(sSearchText As String, sForText As String, Optional ByVal lStart As Long = 1, Optional bCompareText As Boolean = False) As Long
    Dim lWildCardStartPos As Long, lWildCardPos As Long, lWildCardEndPos As Long
    Dim sSubtext As String, lCompare As Long
    'Set variables
    lWildCardStartPos = lStart
    If bCompareText Then
        lCompare = vbTextCompare
        lCompare = vbBinaryCompare
    End If
        'Search for wildcard
        lWildCardPos = InStr(lWildCardPos + 1, sForText, "*")
        If lWildCardPos Then
            lWildCardEndPos = lWildCardPos - 1
            lWildCardEndPos = Len(sForText)
        End If
        'Search input text
        sSubtext = Mid$(sForText, lWildCardStartPos, lWildCardEndPos - lWildCardStartPos + 1)
        lStart = InStr(lStart, sSearchText, sSubtext, lCompare)
        'Check exit conditions
        If lStart = 0 Then
            'Clear value and exit do
            Instr2 = 0
            Exit Do
        ElseIf Instr2 = 0 Then
            'Store value
            Instr2 = lStart
        End If
        If lWildCardPos = 0 Then
            Exit Do
        End If
        lWildCardStartPos = lWildCardPos + 1
End Function

Open in new window

Watch Question
Test your restores, not your backups...
Expert of the Year 2019
Distinguished Expert 2020
This problem has been solved!
Unlock 3 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE