Excel VBA函数，用于检查文件名是否包含该值 [英] Excel VBA function that checks if filename CONTAINS the value

问题描述

我需要一个可以输出如下内容的公式:

I need a formula that would output something like this:

如果包含特定文件夹中的文件名(因为该文件名在@字符后会有一个附加的字符串)，那么如果文件名包含单元格值AB1，则在单元格AC1中添加完整的文件名.

If a filename in a specific folder contains (because the filename will have an additional string after a @ character), so if a filename contains the cell value AB1 then add full filename in cell AC1.

这对VBA有用吗?

非常感谢

推荐答案

在VBA中可行吗?

Is that doable in VBA?

好的.这是我一直使用的VBA辅助函数:

Sure. Here's a VBA helper function I use all the time:

Public Function Contains(ByVal string_source As String, ByVal find_text As String, Optional ByVal caseSensitive As Boolean = False) As Boolean

    Dim compareMethod As VbCompareMethod

    If caseSensitive Then
        compareMethod = vbBinaryCompare
    Else
        compareMethod = vbTextCompare
    End If

    Contains = (InStr(1, string_source, find_text, compareMethod) <> 0)

End Function

每当我要检查一个以上的值时，我也会使用此值-它的表现比If {check1} Or {check2} Or {check3}...更好，因为它会在找到匹配项后立即返回，因此如果{check1}，则不会对它进行评估>返回True:

I also use this one whenever I have more than one value to check for - it performs better than doing If {check1} Or {check2} Or {check3}... because it returns as soon as it finds a match, so {check42} doesn't get evaluated if {check1} returned True:

Public Function ContainsAny(ByVal string_source As String, ByVal caseSensitive As Boolean, ParamArray find_strings() As Variant) As Boolean

    Dim find As String, i As Integer, found As Boolean

    For i = LBound(find_strings) To UBound(find_strings)

        find = CStr(find_strings(i))
        found = Contains(string_source, find, caseSensitive)

        If found Then Exit For
    Next

    ContainsAny = found

End Function

这篇关于Excel VBA函数，用于检查文件名是否包含该值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！