VBA从Collection获取Workbook引用偶发错误91问题问询

• 函数apriData的错误处理分支未给返回值赋值：当打开工作簿失败触发Error1、或后续操作触发Error2时，仅做了Excel实例、工作簿的释放操作，没有给函数返回值apriData赋值，此时函数默认返回Nothing。调用端拿到Nothing后直接访问collectionData(1)就会触发错误91。
• 错误处理逻辑存在次生风险：Error2分支直接调用wb.Close，如果触发Error2时wb还未被成功赋值为有效工作簿对象，这一步会再次抛出错误91。
• 调用端未做有效性校验：拿到apriData的返回值后没有判断是否为有效集合，直接访问元素，只要函数执行异常就会报错。
• 冗余代码：formatta过程开头的Set collectionData = New Collection无意义，后续会被apriData的返回值直接覆盖。

第一步：修改apriData函数 #

优化错误处理逻辑，确保出错时返回可识别的空值，避免次生错误：

Function apriData() As Collection
Dim appExcel As Application
Dim coll As Collection
Set coll = New Collection
Dim wb As Workbook
Dim i As Integer

'create new excel application object
Set appExcel = New Application
'set the applications visible property to false
appExcel.Visible = False
'open the workbook with data
On Error GoTo Error1
Set wb = appExcel.Workbooks.Open("PATH OF THE EXCEL FILE TO SAVE THE WB - CENSORED")
On Error GoTo Error2

MsgBox ("DATA aperto")
coll.Add wb
coll.Add appExcel
Set apriData = coll
Exit Function

Error1:
        'close the application
        appExcel.Quit
        Set appExcel = Nothing
        ' 出错时返回Nothing，通知调用方执行失败
        Set apriData = Nothing
        Exit Function
Error2:
       'close the workbooks if it's valid
        If Not wb Is Nothing Then
            wb.Close SaveChanges:=False
            Set wb = Nothing
        End If
        'close the application
        appExcel.Quit
        Set appExcel = Nothing
        Set apriData = Nothing
        Exit Function
End Function

第二步：修改formatta调用逻辑 #

增加返回值校验，确认拿到有效集合后再执行后续操作：

Sub formatta() 
Dim collectionData As Collection
Dim wb As Workbook
Dim wbData As Workbook
Dim app As Application
Dim ws As Worksheet
Dim sheetCount As Integer
Dim i As Integer
Dim target As Range

Set wb = ActiveWorkbook
sheetCount = wb.Worksheets.Count
For i = 1 To sheetCount
    If Sheets(i).Name = "CE" Then
        Sheets(i).Cells.Clear
    End If
Next i
Set ws = wb.Worksheets(2)
ws.Name = "CE"
Set target = ws.Range("A1")

' 先获取返回值再校验
Set collectionData = apriData
If collectionData Is Nothing Then
    MsgBox "打开数据文件失败，请检查文件路径是否正确", vbCritical
    Exit Sub
End If
If collectionData.Count < 2 Then
    MsgBox "返回数据不完整", vbCritical
    Exit Sub
End If

Set wbData = collectionData(1)
Set app = collectionData(2)
creaGriglia wbData
popolaGriglia wbData
'close the workbooks
wbData.Close SaveChanges:=False
'close the application
app.Quit
' 释放对象避免内存泄漏
Set wbData = Nothing
Set app = Nothing
Set collectionData = Nothing
End Sub

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