Help understanding object instantiation using automation

[beacon]

Hi everybody,

[Access/Excel 2010]

I have a database that I'm using to generate an Excel workbook with 13 worksheets for a list of 50 or so contractors. I have a subroutine that loops through a list of providers, creates a new workbook, adds the 13 worksheets, and formats cells. Soon, I will be calling multiple queries and placing the data into specific cells in each worksheet in each of the workbooks for the contractors.

So far, everything is working as I intend it to, but since I'm always trying to learn and become better at programming, I wanted to break up the subroutine into more manageable pieces before I get to the data step since the subroutine I've written is quite long. I was going to create a container subroutine that would loop through the contractor recordset, then call addditonal subroutines to carry out specific steps.

My question is whether or not I can instantiate the Excel objects in the container subroutine so I don't have to recreate the objects in each of the subroutines that are called from the container or pass the objects to the subroutine from the container.

For instance, will the following pseudo-code work?:

Public Sub ContractorContainer()
 
     Dim db As DAO.Database
     Dim rs As DAO.Recordset
     Dim objExcel As Object
     Dim objExcelWorkbook As Object
     Dim objExcelWorksheet As Object
 
     Set db = DBEngine(0)(0)
     Set rs = db.OpenRecordset(SELECT Contractor FROM tblContractor, dbOpenDynaset)
 
     With rs
          If Not (.EOF And .BOF) Then
               .MoveFirst
               Do Until .EOF = True
                    On Error Resume Next
 
                    Set objExcel = GetObject(,"Excel.Application")
 
                    If Err.Number <> 0 Then
                         Err.Clear
                         On Error GoTo Error_Handler
                         Set objExcel = CreateObject("Excel.Application")
                    End If
 
                    objExcel.ScreenUpdating = False
                    objExcel.Visible = False
 
                    Set objExcelWorkbook = objExcel.Workbooks.Add
 
                    With objExcelWorkbook     
                         CreateContractorWorkbook
                         FormatFirstTab
                         FormatSecondTab
                    End with
               Loop
               .MoveNext
          End If
     End With
 
     Set objExcelWorkbook = Nothing
     Set objExcel = Nothing
     Set rs = Nothing
     Set db = Nothing
 
End Sub
 

Public Sub CreateProviderWorkbook()
 
     Dim objExcelWorksheet As Object
 
     Set objExcelWorksheet = objExcelWorkbook.Sheets("First Tab")
 
End Sub
 

As you can see in the example, I refer to 'objExcelWorkbook' in the 'CreateProviderWorkbook' subroutine without re-instantiating the object or passing an argument to the subroutine. I don't mind re-instantiating the worksheet object for each subroutine, but I wanted to reduce the amount of code and increase the readability as much as possible (I also didn't want to have to pass a bunch of arguments to the subroutines because I'm not sure how ByVal/ByRef really works with objects, if at all, especially during automation). Currently, everything in my example, plus much, much more, is all in the same subroutine.

Thanks,
beacon

[NeoPa]

That's a good reason for looking into this Beacon.

The answer's relatively straightforward as I understand it (and I think I do pretty well TBF), and is that any object reference is maintained while the object variable is still in scope. If calling one procedure from another then the other has no access to the variables of the one. As such you have two main options (without introducing extra modules into the mix) :

1. Pass the object as a parameter. ByRef should work perfectly for you in this case.
2. Define and use module-level Public variables from the start such that all procedures in the module can share the variables fully.

Whatever you do, make sure you stay on top of which variables are still instantiated and which objects (like Workbooks and Applications) are still open and active.

[ADezii]

It appears to be a matter of Scope whereas the Object is Declared and Instantiated at a Procedural Level, as opposed to Modular. To the best of my knowledge, the Object should never be Declared/Instantiated for each Call of the Routine.

[beacon]

Thanks NeoPa. I thought about that, but really needed, and appreciate, the confirmation.

To the best of my knowledge, the Object should never be Declared/Instantiated for each Call of the Routine.

Thanks ADezii...I knew the object shouldn't be declared/instantiated for each call of the routine (where the same object is open and used throughout).

However, it does raise another question (again, I think I know the answer, but appreciate the confirmation to write it to my personal harddrive, i.e. my brain). Is an object considered to be declared/instantiated when it is dimensioned or when it is set?

If I were working with an object that I intended to set to a worksheet in the called subroutine/function (or set repeatedly through a loop), would I be able to send the object after dimensioning it? If I set it before I pass it, then I would just have to re-set it again in the called subroutine/function, which kind of defeats the purpose for carrying out loop routine in a separate subroutine/function.

[NeoPa]

It doesn't need to be set in the calling procedure. Unset object variables return Nothing. Not zero, but a special value which indicates that they are variables that refer to no object (yet). An object set to Nothing (or even one set to a valid object) can be passed by the calling procedure and set by the called procedure. This will then be visible to the calling procedure once the called procedure has returned, as long as ByRef is used.

[TheSmileyCoder]

That is a very good question, and a good basis for some advanced discussions.


When something is declared (dim) within a procedure, it is only visible to that procedure. However you can pass either its value or a reference to the variable/object to another procedure. An example is provided below:
(Aircode)

Public Sub ExportToExcel()
  Dim objExcel as New Excel.Application
  Dim objWB as Excel.WorkBook
  set objWB=objExcel.Workbooks.Add
  Call  CreateProviderWorkSheet(objWb,7) 'Create sheet for provider 7
  Call CreateProviderWorkSheet(objWb,9) 'Create sheet for provider 9
  Call CreateProviderWorkSheet(objWb,68) 'Create sheet for provider 68
  objExcel.Visible=True
  Set objExcel=Nothing 'Note taht I don't close or quit
  set objWB=nothing
End Sub
 
Private Sub CreateProviderWorkSheet(ByRef objWB as Excel.WorkBook,lngProviderID as long)
  Dim objWS as excel.Worksheet
  set objWS =objWB.WorkSheets.Add
  objWS .Name="Provider " & lngProviderID
  'more code blah blah
  set objWS =Nothing
End Sub

The above code should (barring typos) create a new excel workbook with 3 worksheets. Note that I pass the reference to the workbook to the function.
The above variables are declared at the PROCEDURE scope.

Another way of doing it, would be to declare some of the variables at the MODULE level scope, at the top of the module, usually as private and not public as previously stated. Private basically means that only procedures within the module will have access to the object.

Option Compare Database
Option Explicit
Private objExcel as Excel.Application 'Note the private instead of the dim
Private objWB as Excel.Workbook
 
Public Sub ExportToExcel()
  Set objExcel=New Excel.Application 'Change made here
  set objWB=objExcel.Workbooks.Add
  Call  CreateProviderWorkSheet(7) 'Create sheet for provider 7
  Call CreateProviderWorkSheet(9) 'Create sheet for provider 9
  Call CreateProviderWorkSheet(68) 'Create sheet for provider 68
  objExcel.Visible=True
  Set objExcel=Nothing 'Note taht I don't close or quit
  set objWB=nothing
End Sub
 
Private Sub CreateProviderWorkSheet(lngProviderID as long) 'This line becomes slightly simpler since I no longer need to pass the WB variable, its available at the module level.
  Dim objWS as excel.Worksheet
  set objWS =objWB.WorkSheets.Add
  objWS .Name="Provider " & lngProviderID
  'more code blah blah
  set objWS =Nothing
End Sub

If you have alot of stuff going on in your export procedure it might be simpler to declare the variables at the module level so you don't have to pass them back and forth all the time. Another thing you may have noticed is that the ExportToExcel is declared as public, since it will likely by called by the click of a button somewhere, while the CreateProviderWorkSheet is private, it will only be available within the module, which is just fine, since only the ExportToExcel has any reason to call the CreateProviderWorkSheet procedure.

The important thing to remember with using module level variables is to cleanup when you are done. Module level variables don't get cleaned up automatically (by intention). Depending on the object and intended use it may need to be closed/exited and the reference set to nothing.

Is an object considered to be declared/instantiated when it is dimensioned or when it is set?

I call it declaring when I use such keywords as Dim, Private or Public, and I would call it instantiating when I use the keyword set. In order not to make it too simple, there are cases where you can actually do both at the same time:

Dim objExcel as New Excel.Application

Furthermore there is some difference as to the behaviour of simple variables such as long, integers, strings, and the behaviour of objects, but that's for another discussion.

[NeoPa]

Smiley:
usually as private and not public as previously stated.

Indeed so. I was considering the third option of declaring it in a separate standard module and confused the two when I posted :-( Good catch :-)

Actually, Public scope wouldn't be an actual problem, but Private is all that would be required, and it's generally better to limit scope to what is required to avoid any possibilities of overlap.

[TheSmileyCoder]

As NeoPa said, there is little direct "bad" influence by creating it as a public function instead of a private function.

However it can make the code a bit easier to understand later on, and thus to maintain for "the next developer" even if that is just you 6 months from now. Just knowing its a private function makes it easier to spot the possible repercussions from making modifications to it. Still a proper understanding of scope and its implications can save alot of headaches down the line :)

[NeoPa]

I concur absolutely.

Another reason is to allow the reuse of the name in other modules without any clash or confusion, in the same way that procedures can use variables of the same name as variables in other procedures because the scope of the variables are local to the specific procedures they're defined in.