Excel for Mac 365 VBA运行时错误7（内存不足）求助

关键问题排查与修复 #

1. 修复行号变量的类型溢出问题 #

代码中用Integer类型定义lRow、lRowP、lCol、lColP，但Excel最大行数为1048576，远超Integer的上限（32767）。当数据行数超过32767时，变量会溢出，导致后续计算的范围错误，进而触发“内存不足”的假象。

修复代码：
将所有行/列数的变量类型从Integer改为Long：

Dim lRow As Long
Dim lCol As Long
Dim lRowP As Long
Dim lColP As Long

2. 明确限定Range/Cells的父对象 #

未限定父对象的Range和Cells可能引用错误的工作表（比如当前激活的工作表并非预期目标），导致读取的范围远大于实际数据，占用过多内存。

修复示例：
打开外部工作簿后，用SRC.Worksheets("Sheet1")明确限定操作范围，避免依赖Activate：

Set SRC = Workbooks.Open(Vecnames(1, 5) & Vecnames(2, 1), 0, True, True)
With SRC.Worksheets("Sheet1")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    lCol = .Cells(lRow, .Columns.Count).End(xlToLeft).Column
    Set rng = .Range(.Cells(2, 1), .Cells(lRow, lCol))
End With
MyDB = rng
SRC.Close False

同理处理第二个外部文件的读取逻辑。

3. 移除低效的Select/Selection操作 #

Select和Selection会额外占用内存，且容易引发上下文错误。直接操作目标范围即可：
替换原有代码：

Range(Cells(2, 11), Cells(lRow, 11)).Select
Selection.TextToColumns Destination:=Range(Cells(2, 11), Cells(lRow, 11)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yyyy;@"

改为：

With ThisWorkbook.Worksheets("Sheet1").Range(Cells(2, 11), Cells(lRow, 11))
    .TextToColumns Destination:=.Cells, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 4), TrailingMinusNumbers:=True
    .NumberFormat = "dd/mm/yyyy;@"
End With

4. 及时释放对象内存 #

关闭外部工作簿后，将对象设置为Nothing，彻底释放占用的内存：

SRC.Close False
Set SRC = Nothing

完整修复后的代码示例 #

Sub ARMABASES()
'
' ARMABASES Macro
' Arma la base de inventario y producción de fianzas
'
' Acceso directo: Ctrl+Mayús+A
'

Dim MyDB As Variant
Dim MyDBP As Variant
Dim Vecnames(1 To 10, 1 To 5) As String
Dim SRC As Workbook
Dim rng As Range
Dim lRow As Long
Dim lCol As Long
Dim lRowP As Long
Dim lColP As Long

'读取配置参数
With ThisWorkbook.Worksheets("DATOS")
    Vecnames(1, 1) = .Range("B5").Value
    Vecnames(1, 2) = .Range("B6").Value
    Vecnames(1, 3) = .Range("B7").Value
    Vecnames(1, 4) = .Range("B8").Value
    Vecnames(1, 5) = .Range("B9").Value
    Vecnames(2, 1) = .Range("B10").Value
    Vecnames(2, 2) = .Range("B11").Value
    Vecnames(3, 2) = .Range("B12").Value
End With

'处理第一个外部文件
Set SRC = Workbooks.Open(Vecnames(1, 5) & Vecnames(2, 1), 0, True, True)
With SRC.Worksheets("Sheet1")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    lCol = .Cells(lRow, .Columns.Count).End(xlToLeft).Column
    Set rng = .Range(.Cells(2, 1), .Cells(lRow, lCol))
End With
MyDB = rng
SRC.Close False
Set SRC = Nothing

'写入数据并处理日期列
With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Cells(2, 1), .Cells(lRow, lCol)).Value = MyDB
    With .Range(.Cells(2, 11), .Cells(lRow, 11))
        .TextToColumns Destination:=.Cells, DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 4), TrailingMinusNumbers:=True
        .NumberFormat = "dd/mm/yyyy;@"
    End With
End With

Erase MyDB

'处理第二个外部文件
Set SRC = Workbooks.Open(Vecnames(1, 5) & Vecnames(2, 2), 0, True, True)
With SRC.Worksheets("Sheet1")
    lRowP = .Range("A" & .Rows.Count).End(xlUp).Row
    lColP = .Cells(lRowP, .Columns.Count).End(xlToLeft).Column
    MyDBP = .Range(.Cells(2, 1), .Cells(lRowP, lColP)).Value
End With
SRC.Close False
Set SRC = Nothing

'写入第二个文件的数据
With ThisWorkbook.Worksheets("Hoja1")
    .Range(.Cells(2, 1), .Cells(lRowP, lColP)).Value = MyDBP
End With

Erase MyDBP

End Sub

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