Excel VBA 基于自定义结构体数组按文件名匹配查找fsize并构建合同关联数组的方法

前置声明注意事项 #

• 自定义类型的声明位置必须放在标准模块的通用声明段，不能嵌入到任何过程内部，否则会触发编译错误
• 原代码中的Global关键字在新版VBA中兼容性较差，统一替换为Public可以避免在窗体模块、类模块下引用时报错
• 定长数组file_array(1000)没有记录有效元素边界，遍历到未赋值的空元素时会产生误匹配，建议额外定义一个全局变量记录数组实际填充的数据量

核心实现逻辑 #

为了避免逐行匹配时双重循环的性能损耗，优先把file_array中的文件名和大小映射为字典索引，匹配时直接按文件名做O(1)查找，数据量较大时也不会出现卡顿。
完整可运行的代码如下：

' === 以下代码放置在标准模块的通用声明段 ===
Public Type file_sizes
    fName As String
    fsize As Integer
End Type
    
Public Type ctr_file_sizes
    ctr_id As String
    fName As String
    fsize As Integer
End Type

' 全局数组 + 有效元素计数
Public file_array(1000) As file_sizes
Public file_array_ValidCount As Long

' === 以下是填充关联数组的过程 ===
Sub BuildCtrFileArray()
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim ctrFileArr() As ctr_file_sizes
    Dim fileNameIndex As Object
    Dim i As Long
    
    ' 初始化文件名-文件大小的索引字典，设置匹配忽略大小写
    Set fileNameIndex = CreateObject("Scripting.Dictionary")
    fileNameIndex.CompareMode = 1
    ' 遍历已填充的有效数组元素，写入索引
    For i = 0 To file_array_ValidCount - 1
        If Len(Trim(file_array(i).fName)) > 0 Then
            ' 统一处理文件名前后空格，避免匹配偏差
            fileNameIndex(Trim(file_array(i).fName)) = file_array(i).fsize
        End If
    Next i
    
    ' 绑定存储合同信息的工作表，可根据实际表名修改
    Set wsTarget = ThisWorkbook.Worksheets("合同信息")
    ' 获取A列（合同编号）的最后有效行，默认第一行为表头
    lastRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        Debug.Print "工作表无有效合同数据"
        Exit Sub
    End If
    
    ' 按实际数据行数重定义关联数组大小
    ReDim ctrFileArr(0 To lastRow - 2) As ctr_file_sizes
    
    ' 逐行填充关联数组字段
    For i = 2 To lastRow
        With ctrFileArr(i - 2)
            .ctr_id = Trim(wsTarget.Cells(i, "A").Value)
            .fName = Trim(wsTarget.Cells(i, "B").Value)
            ' 匹配不到文件大小时默认赋值0
            If fileNameIndex.Exists(.fName) Then
                .fsize = fileNameIndex(.fName)
            Else
                .fsize = 0
            End If
        End With
    Next i
    
    ' 此处可对填充完成的ctrFileArr做后续业务处理
End Sub

常见开发避坑点 #

• 文本一致性检查：如果file_array中存储的是带完整路径的文件名，而工作表中仅存文件名，会出现完全匹配失败的情况；可在写入字典索引时提取纯文件名部分，统一匹配规则
• 重名文件处理：如果同一文件名在file_array中对应多个不同的fsize值，直接用字典会覆盖之前的记录，需要根据业务逻辑调整为字典存数组值，保留所有匹配项
• 数组越界防护：原定长file_array最多支持1001个元素，如果实际存储的文件数超过这个范围会直接报下标越界，建议替换为动态数组，在填充文件数据时自动扩展长度

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