Pass object to Sub.

[dhartford]

I need pass an object (Excel Application/work book) to a Sub. Can anyone help me on the syntax?


FunctionA (byVal E as Workbook) 'don't know if it is right



Thank you in advance.

[Oorang]

That's a function not a sub:) But other than that yes. That is correct:)
Public Sub DoSomething(ByVal myWorkbook As Excel.Workbook)
MsgBox myWorkbook.Name
End Sub

[dhartford]

That's a function not a sub:) But other than that yes. That is correct:)
Public Sub DoSomething(ByVal myWorkbook As Excel.Workbook)
MsgBox myWorkbook.Name
End Sub

Oorang,

Thanks for your response. I just can't get it work. Here is my code:


'in calling sub
Sub .....
..........
'lunch Excel
Set objXL = New Excel.Application

'create a new worksheet and add field name and data to build chart
objXL.Workbooks.Add
'Debug.Print objXL.ActiveWorkbook.Name
Set objWS = objXL.ActiveSheet
objWS.Name = "Chart_Data"
objWS.Cells(2, 1) = "Name1"
............ 'add

Call BuildChart(objXL, CTitle, Len(CTitle), .....)

End Sub

Sub BuildChart(ByVal Cobj As Excel.Application, ChTitle As String, LenTitle As Integer, ..................)

Cobj.Charts.Add
Set objChart = Cobj.ActiveChart 'error objChart, variable is not defined

.............



If I create chart within the calling sub, it works fine. I'm confused with following:
1. Should I pass application or workbook? What is the defferience?
2. If I pass application then create a workbook in BuildChart, how do I make sure I work on the same book as I send chart data to the book created in calling sub?

Appreciate your help.

[Oorang]

1.) Generally you should pass the smallest object needed to accomplish the goal. In this case I would pass the workbook (as that is all is really needed by the sub)
2.) You can either pass around the workbook object. Or you can save and close, then pass around the path, opening as needed. The first approach is generally faster.

See if this helps get you going:
Option Explicit
'in calling sub
Sub Example()
Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim CTitle As String
'launch Excel
Set objXL = New Excel.Application
'create a new worksheet and add field name and data to build chart
objXL.Visible = True 'Not needed, just makes it easier to debug.
Set objWB = objXL.Workbooks.Add
Set objWS = objWB.Worksheets(1)
objWS.Name = "Chart_Data"
objWS.Cells(2, 1) = "Name1"
CTitle = "W00t!"
BuildChart objWB, CTitle, Len(CTitle)
objWB.Save "C:\Test\MyChartTest.xls"
objXL.Quit
End Sub
Sub BuildChart(ByVal objWB As Excel.Workbook, ChTitle As String, LenTitle As Integer)
Dim objChart As Excel.Chart
Set objChart = objWB.Charts.Add
End Sub

[dhartford]

1.) Generally you should pass the smallest object needed to accomplish the goal. In this case I would pass the workbook (as that is all is really needed by the sub)
2.) You can either pass around the workbook object. Or you can save and close, then pass around the path, opening as needed. The first approach is generally faster.

See if this helps get you going:
Option Explicit
'in calling sub
Sub Example()
Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
debug.print objXL.ActiveWorkbook.Name 'got name book25
Dim CTitle As String
'launch Excel
Set objXL = New Excel.Application
'create a new worksheet and add field name and data to build chart
objXL.Visible = True 'Not needed, just makes it easier to debug.
Set objWB = objXL.Workbooks.Add
Set objWS = objWB.Worksheets(1)
objWS.Name = "Chart_Data"
objWS.Cells(2, 1) = "Name1"
CTitle = "W00t!"
BuildChart objWB, CTitle, Len(CTitle)
objWB.Save "C:\Test\MyChartTest.xls"
objXL.Quit
End Sub
Sub BuildChart(ByVal objWB As Excel.Workbook, ChTitle As String, LenTitle As Integer)
Debug.Print ActiveWorkbook.Name 'got name book10
Dim objChart As Excel.Chart
Set objChart = objWB.Charts.Add
End Sub

Oorang,

I added above code in red and got different book names. I can't create graph with 2 different books. Why do I get 2 different books?

Thank you very much for your time.

[Oorang]

Because those are two difference instances of an Excel Application. If you open Excel up. Then go back to the start menu and open it up again. If you go to the task manager then to processes you will see two Excel process open. Each Application object represents a process. There can then be many workbooks that belong to the application, and many worksheets that belong to the workbook.

When you do objXL.Activeworkbook you are specifying the specific application (process) you want (with objXL) and then the activeworkbook for that process. When you just do Activeworkbook you are implicitly doing Excel.ActiveWorkbook.Name. The Excel prefix is implicitly the process(or application) that the code resides in. If the code resides in anything other than Excel then there will be no object for it to roll into and it will throw an error 91 (object not set).