The code to be referred to is:
Dim oXL As Microsoft.Office.Interop.Excel.Application
Dim oWBS As Workbooks
Dim oWB As Workbook
Dim oSheet As Worksheet
oXL = CreateObject("Excel.Application")
oWBS = oXL.Workbooks
oWB = oWBS.Add
Summary of the detailed explanation presented below: In the above code oXL, oWBS, oWB and oSheet are all storing references. By definition, a reference must point to a memory location. What I want to understand is whether they point to the same or different memory locations?
My thoughts: I have highlighted my questions in bold
My guess is that these memory locations representing the various references are all different from one another. I imagine the representations as follows. Say oXL (representing the Excel Application) points to a location ‘A’ inside the memory. The Workbooks property returns an instance of the Workbooks collection. oWBS stores the reference returned by oXL.Workbooks and points to a new location, say B which is tagged to Location A. I mention “tagging” because oWBS is created against oXL. The reference oWBS can access the ADD method. When the ADD method is executed it adds a workbook or in more technical terms returns a WORKBOOK object (which is also a reference). oWB stores this reference and by definition must be pointing at some memory location, say C which gets tagged to B.
My Question: Are A, B, C are all different memory locations? Although I may be wrong, I can still imagine A and B to be separate memory locations. But what about B and C? The memory location representing the Workbooks collection (B) and the location which actually holds the workbook (with one sheet in it) that is C – should they be really different locations? If not, then are the two reference oWBS and oWB pointing to the same location – how to explain that??
Generalizing the above line of thought, does it mean that whenever we execute oXL.Workbooks.ADD to add a new workbook we are returned three references?
- a reference denoting the excel application
- a reference denoting the instance of
oXL.Workbookscollection - a reference denoting the single workbook that gets added
Now let’s add a worksheet to a workbook, using _Workbook.Worksheets.ADD. Extending the above reasoning would we be again getting unique references (pointing to new memory locations) for _Workbook.Worksheets and Worksheets.ADD??
Also, I want to state that this is more of an academic curiosity.
CodePudding user response：
By definition, a reference must point to a memory location. What I want to understand is whether they point to the same or different memory locations?
In the COM world object reference doesn't point to a memory location, especially in a managed applications written in VB.NET. In .Net you deal with an RCW object which routes your calls to the COM object. Read more about that in the Runtime Callable Wrapper article in MSDN. It states the following:
The runtime creates exactly one RCW for each COM object, regardless of the number of references that exist on that object. The runtime maintains a single RCW per process for each object. If you create an RCW in one application domain or apartment, and then pass a reference to another application domain or apartment, a proxy to the first object will be used. Note that this proxy is a new managed object and not the same as the initial RCW; this means the two managed objects are not equal but do represent the same COM object.
In the COM world when you call a property or method, for example, the oXL.Workbooks property returns an instance of the Workbooks class and increases the reference counter of the corresponding COM object. Say, if you call the property twice in a raw you will get a new object instance in .NET and the refence counter will be increased twice. In that case you need to call the Marshal.ReleaseComObject for each object returned to decrease the refence counter.
Finally, you may find the Why doesn’t Excel quit? article helpful.