excel - 如何在VBA中将Excel对象用于另一个过程

我需要对同一模块中的其他过程使用excel对象（在过程中设置）。但无法做到。请帮我做同样的事情。下面是我的代码。

Public Sub FirstProc() 'Here the excel object is defined

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open(ReportTemplateDirectory\Test.xlsx")
xlApp.Visible = True
...
... 'all work goes here FINE
...

SecondProc  'calling second proc
End Sub

下面是我的第二个步骤

Public Sub SecondProc()

' In this procedure i need to delete a sheet of the same excel file which is generated in above procedure.

xlWorkbook.Sheets(4).Delete

End Sub

但是我收到了所需的运行时错误424对象。任何帮助，将不胜感激。

最佳答案

尽管我只是将该行添加到第一个模块中，但是您可以尝试进行以下操作（在Outlook中测试）。

关闭DisplayAlerts以避免在删除工作表时出现检查消息。

Public Sub FirstProc() 'Here the excel object is defined

Dim xlApp As Object
Dim xlWorkbook As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.displayalerts = False
Set xlWorkbook = xlApp.Workbooks.Add
xlApp.Visible = True

Call SecondProc(xlWorkbook)    'calling second proc
xlApp.displayalerts = True
End Sub


Public Sub SecondProc(xlWorkbook As Object)
' In this procedure i need to delete a sheet of the same excel file which is generated in above procedure.
xlWorkbook.Sheets(2).Delete
End Sub