How to use excel object to another procedure in vba

Question

I need to use an excel object(which is set in a procedure), to other procedure in the same module . But not able to do it. Please help me doing the same. Below is my code.

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

Below is my second procedure,

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

But I am getting the RunTime Error 424 Object Required. Any help would be appreciated.

brettdj · Accepted Answer · 2015-03-13 08:00:59Z

3

While I would just add that line to the first module your could try something like this (tested in Outlook).

DisplayAlertsturned off to avoid a check message when the sheet is deleted.

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

answered Mar 13, 2015 at 8:00

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use excel object to another procedure in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related