如何最优地将整个Excel工作表读取到VBA中？

嘿，针对你的需求，我来分享几个实用的方案，帮你做选择：

你的核心需求是批量读取Excel数据到内存→做分析/修改→写入新表，这里主要对比三种常见实现方式，各有优劣：

1. 二维数组（最快最省内存） #

这是VBA里处理批量数据的首选方案，因为一次性把整个数据区域读取到内存数组，彻底避免了逐行和Excel对象交互的性能开销（Excel对象模型的交互速度是出了名的慢）。内存占用也极小，适合几万甚至几十万行的大数据量。

唯一的小缺点是数组元素都是Variant类型，处理时要注意类型转换，代码可读性稍弱，但胜在速度。

代码示例：

Sub ProcessWithArray()
    Dim srcSheet As Worksheet
    Set srcSheet = ThisWorkbook.Worksheets("Source") ' 替换成你的源表名
    
    ' 确定数据区域范围（假设表头在第1行，数据从第2行开始）
    Dim lastRow As Long, lastCol As Long
    lastRow = srcSheet.Cells(srcSheet.Rows.Count, "A").End(xlUp).Row
    lastCol = srcSheet.Cells(1, srcSheet.Columns.Count).End(xlToLeft).Column
    
    ' 一次性读取整个数据区域到数组
    Dim dataArr As Variant
    dataArr = srcSheet.Range(srcSheet.Cells(2, 1), srcSheet.Cells(lastRow, lastCol)).Value
    
    ' 遍历数组处理数据
    Dim i As Long
    For i = LBound(dataArr, 1) To UBound(dataArr, 1)
        ' 分析发票参考号长度（假设在第5列，根据你的实际列调整）
        Dim refLen As Integer
        refLen = Len(CStr(dataArr(i, 5)))
        
        ' 示例：修改数值列（比如第3列是金额，加价5%）
        If IsNumeric(dataArr(i, 3)) Then
            dataArr(i, 3) = dataArr(i, 3) * 1.05
        End If
    Next i
    
    ' 写入目标工作表
    Dim destSheet As Worksheet
    Set destSheet = ThisWorkbook.Worksheets("Destination") ' 替换成你的目标表名
    
    ' 先清空目标区域旧数据（可选）
    destSheet.Range(destSheet.Cells(2, 1), destSheet.Cells(destSheet.Rows.Count, lastCol)).ClearContents
    
    ' 把处理后的数组一次性写入
    destSheet.Cells(2, 1).Resize(UBound(dataArr, 1), UBound(dataArr, 2)).Value = dataArr
End Sub

2. 自定义类+集合（可读性最强，维护方便） #

这就是你提到的思路，非常适合数据结构复杂、需要频繁访问不同字段的场景。用类来封装每一行的所有属性，代码可读性拉满，后期扩展或修改字段也非常方便，完全不用担心搞混列的位置。

速度方面，只要不是几十万行的超大数据量，完全够用，是兼顾可读性和性能的优质方案。

第一步：创建类模块 #

插入一个类模块，命名为clsInvoiceRecord（可以自定义名字），然后添加对应属性：

' clsInvoiceRecord 类模块代码
Public DocumentType As String
Public VendorNumber As String
Public VendorName As String
Public InvoiceNumber As String
Public InvReference As String
' 可以根据你的实际需求添加更多属性，比如金额、日期等

第二步：主程序代码 #

Sub ProcessWithClass()
    Dim srcSheet As Worksheet
    Set srcSheet = ThisWorkbook.Worksheets("Source")
    
    Dim lastRow As Long
    lastRow = srcSheet.Cells(srcSheet.Rows.Count, "A").End(xlUp).Row
    
    ' 用Collection存储每个发票记录对象
    Dim invoiceRecords As New Collection
    Dim i As Long
    
    ' 逐行读取并实例化对象
    For i = 2 To lastRow
        Dim newRecord As New clsInvoiceRecord
        ' 按实际列对应赋值（这里假设A列是Document type，B是Vendor #，以此类推）
        newRecord.DocumentType = srcSheet.Cells(i, 1).Value
        newRecord.VendorNumber = srcSheet.Cells(i, 2).Value
        newRecord.VendorName = srcSheet.Cells(i, 3).Value
        newRecord.InvoiceNumber = srcSheet.Cells(i, 4).Value
        newRecord.InvReference = srcSheet.Cells(i, 5).Value
        
        invoiceRecords.Add newRecord
        Set newRecord = Nothing ' 释放对象
    Next i
    
    ' 处理数据：遍历集合
    Dim record As clsInvoiceRecord
    For Each record In invoiceRecords
        ' 分析发票参考号长度
        Dim refLen As Integer
        refLen = Len(record.InvReference)
        
        ' 示例：如果参考号长度小于8，给发票编号加前缀
        If refLen < 8 Then
            record.InvoiceNumber = "FIX-" & record.InvoiceNumber
        End If
    Next record
    
    ' 写入目标工作表
    Dim destSheet As Worksheet
    Set destSheet = ThisWorkbook.Worksheets("Destination")
    destSheet.Range("A2:Z" & destSheet.Rows.Count).ClearContents ' 清空旧数据
    
    Dim rowIdx As Long
    rowIdx = 2
    For Each record In invoiceRecords
        destSheet.Cells(rowIdx, 1).Value = record.DocumentType
        destSheet.Cells(rowIdx, 2).Value = record.VendorNumber
        destSheet.Cells(rowIdx, 3).Value = record.VendorName
        destSheet.Cells(rowIdx, 4).Value = record.InvoiceNumber
        destSheet.Cells(rowIdx, 5).Value = record.InvReference
        rowIdx = rowIdx + 1
    Next record
End Sub

3. 直接用Range对象（不推荐） #

如果直接逐行操作Range对象，每次读写都要和Excel交互，数据量大的时候会非常卡顿，除非你的数据只有几十行，否则不建议用这种方式。

总结选择建议 #

• 若数据量极大（几万行以上），追求极致速度：选二维数组。
• 若数据结构复杂，需要清晰的代码逻辑，或者后期要扩展功能：选自定义类+集合——你的初步思路完全正确，这是非常好的实践！
• 小数据量的话，两种方案都可以，看你更看重速度还是代码可读性。

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