How to use non-capturing group in VBA regex?

With VBA, I'm trying to use regex to capture the filename from a UNC path without the extension--looking at .TIF files only.

So far this is what I have:

Function findTIFname(filestr As String) As String

Dim re As RegExp
Dim output As String
Dim matches As MatchCollection

Set re = New RegExp
re.pattern = "[^\\]+(?:[.]tif)$"
Set matches = re.Execute(filestr)
If matches.Count > 0 Then
    output = matches(0).Value
Else
    output = ""
End If
findTIFname = output

End Function

But when I run the function as follows:

msgbox findTIFname("\\abc\def\ghi\jkl\41e07.tif")

I get the following output:

41e07.tif

I thought that "(?:xxx)" was the regex syntax for a non-capturing group; what am I doing 开发者_JS百科wrong?

The syntax (?:...) is a non-capturing group. What you need here is a positive lookahead assertion which has a (?=...) syntax like so:

re.pattern = "[^\\]+(?=[.]tif$)"

Note that lookaround assertions have zero width and consume no characters.

Do you really need to do this with RegEx?
Access (or better, MS Office) has built-in ways to do this quite easily without RegEx.

You just need to reference the Microsoft Scripting Runtime (which should be included in every MS Office installation, as far as I know).
Then you can use the FileSystemObject:

Public Function findTIFname(filestr As String) As String

    Dim fso As FileSystemObject
    Set fso = New FileSystemObject

    If fso.GetExtensionName(filestr) = "tif" Then
        findTIFname = fso.GetBaseName(filestr)
    End If

    Set fso = Nothing

End Function

Given your example UNC path \\abc\def\ghi\jkl\41e07.tif, this will return 41e07.