报表场景下，高效检查字符串中文件扩展名的最优方法？

Hey Gregory,

Handling multiple filenames packed into a single cell can get messy with just nested Like operators—here's a cleaner, more scalable approach to validate extensions and flag accounts that need an email:

Let's structure this to avoid repetitive code and make it easy to update your valid extension list later.

1. Define Your Valid Extension List #

First, create an array of allowed extensions. This way, you can add/remove types in one place instead of editing multiple lines of code:

Dim validExtensions As Variant
validExtensions = Array(".xls", ".xlsx", ".pdf", ".docx") ' Add your specific valid types here

2. Split Filenames from the Cell #

Assuming your filenames are separated by a delimiter (like space, comma, or semicolon—adjust this to match your actual data), split the cell value into an array of individual filenames:

Dim filenames As Variant
filenames = Split(Cells(i, 11).Value2, " ") ' Replace " " with your actual delimiter (e.g., ", ")

3. Reusable Helper Function for Extension Checks #

Create a helper function to check if a given extension is in your valid list. This keeps your main code clean and avoids repetition:

Function IsExtensionValid(ext As String, validList As Variant) As Boolean
    Dim item As Variant
    ' Case-insensitive check so ".XLS" and ".xls" are both recognized as valid
    For Each item In validList
        If LCase(ext) = LCase(item) Then
            IsExtensionValid = True
            Exit Function ' No need to check further once a match is found
        End If
    Next item
    IsExtensionValid = False
End Function

4. Check Each Filename in the Cell #

Loop through each filename, extract its extension, and verify validity. If any invalid extension (or missing extension) is found, flag the account for an email:

Dim filename As Variant
Dim hasInvalidFiles As Boolean
hasInvalidFiles = False

For Each filename In filenames
    If Trim(filename) <> "" Then ' Skip empty entries created by the split
        ' Check if the filename has an extension at all
        If InStr(filename, ".") = 0 Then
            hasInvalidFiles = True
            Exit For
        End If
        
        ' Extract the full extension (including the dot)
        Dim fileExt As String
        fileExt = "." & LCase(Right(filename, Len(filename) - InStrRev(filename, ".")))
        
        ' Validate the extension
        If Not IsExtensionValid(fileExt, validExtensions) Then
            hasInvalidFiles = True
            Exit For ' Stop checking once an invalid file is found
        End If
    End If
Next filename

' Trigger email if invalid files exist
If hasInvalidFiles Then
    ' Insert your email sending code here
    ' Example: SendEmailToAccountHolder Cells(i, 1).Value2 ' Assuming account holder is in column 1
End If

5. Bonus: Count Valid Extensions (If You Need That Metric) #

If you still need to track how many valid files are present (like your original function), adjust the loop to count valid entries instead of just flagging invalid ones:

Dim validCount As Integer
validCount = 0
Dim totalFiles As Integer
totalFiles = 0

For Each filename In filenames
    If Trim(filename) <> "" Then
        totalFiles = totalFiles + 1
        If InStr(filename, ".") > 0 Then
            Dim fileExt As String
            fileExt = "." & LCase(Right(filename, Len(filename) - InStrRev(filename, ".")))
            If IsExtensionValid(fileExt, validExtensions) Then
                validCount = validCount + 1
            End If
        End If
    End If
Next filename

' Send email if not all files are valid
If validCount < totalFiles Then
    ' Insert email logic here
End If

Key Improvements Over Your Current Method #

• Scalability: Update valid extensions in one place instead of modifying multiple Like statements.
• Accuracy: Properly splits and checks each filename individually, avoiding edge cases (e.g., filenames with spaces before extensions).
• Maintainability: Reusable helper function makes the code easier to debug and extend later.

Just remember to adjust the delimiter in the Split function to match how your filenames are separated in the cell (e.g., use ", " if they're comma-and-space separated).

内容的提问来源于stack exchange，提问作者Gregory