Confusion in creating excel application object using VB.net: CreateObject vs New-CodePudding

I am facing difficulty in understanding the following concepts. I had posted a question some time back - read through the answers but some things are still not clear. I state my confusion below:

My first question refers to the following code piece

Option Strict On
Imports Microsoft.Office.Interop
Dim oxl As Excel.Application
oxl = CreateObject("Excel.Application")

In the above code piece, the statement oxl = CreateObject("Excel.Application") throws an error stating, Option Strict On disallows implicit conversions from Object to Application. My question is I read from many sources that it is always better to keep Option Strict ON but in this case when we need to create a new excel application, the Option Strict ON is preventing us from doing so. So what is the best practice that should be followed for such a conflict?

Next I tried replacing the statement oxl = CreateObject("Excel.Application") with oxl = New Excel.Application. It was observed that even with Option Strict ON, we can create a new excel application object with the NEW keyword. It was also checked with GetType that in both cases that is, using CreateObject and NEW, the type of object being created was: System._ComObject.So my question is if the type of object being created remains remains the same, why is that Option Strict disallows CreateObject but allows the creation of the excel application object using NEW?

To study it further, I extended the above code to the following:

Option Strict On
Imports System
Imports Microsoft.Office.Interop
Module Program

    Dim oxl As Excel.Application
    Dim owb As Excel.Workbook
    Dim osheet As Excel.Worksheet

    Sub Main()
        oxl = New Excel.Application
        'oxl = CreateObject("Excel.Application")
        Console.WriteLine(oxl.GetType)
        oxl.Visible = True
        owb = oxl.Workbooks.Add()
        osheet = owb.Worksheets("Sheet1") ‘Error: Option Strict ON disallows implicit conversions from ‘Object’ to ‘Worksheet’
        osheet.Range("A1").Value = 53
        Console.WriteLine("Hello World!")
        Console.ReadLine()
    End Sub
End Module

When we run the code we see that the error Option Strict ON disallows implicit conversions from ‘Object’ to ‘Worksheet’ comes at the line: osheet = owb.Worksheets("Sheet1")

Question: Why is the error coming? I mean if, owb = oxl.Workbooks.Add()can work (that it returns a workbook which is referred to by owb) then why is osheet = owb.Worksheets("Sheet1") not working because the right hand side returns the “Sheet1” of the workbook which osheet should be able to point to (given that it is of the type Excel.Worksheet)?

CodePudding user response：

This is what VB statements about COM objects actually do.

Information for Visual Basic Programmers

Visual Basic provides full support for Automation. The following table lists how Visual Basic statements translate into OLE APIs.

Visual Basic statement OLE APIs

CreateObject (“ProgID”)
CLSIDFromProgID 
CoCreateInstance
QueryInterface to get IDispatch interface.   
GetObject (“filename”, “ProgID”)
CLSIDFromProgID 
CoCreateInstance
QueryInterface for IPersistFile interface.
Load on IPersistFile interface.
QueryInterface to get IDispatch interface.    
GetObject (“filename”)
CreateBindCtx creates the bind context for the subsequent functions. 
MkParseDisplayName returns a moniker handle for BindMoniker.
BindMoniker returns a pointer to the IDispatch interface.
Release on moniker handle.
Release on context.   
GetObject (“ProgID”)
CLSIDFromProgID 
GetActiveObject on class ID.
QueryInterface to get IDispatch interface.    
Dim x As New interface
Find CLSID for interface. 
CoCreateInstance
QueryInterface

A standard COM VTable the first three entries are IUnknown

AddRef, Release (decreases the ref count), and QueryInterface to find what interfaces this object support.

The next four entries are IDispatch

GetIDsOfNames, Invoke , GetTypeInfoCount, GetTypeInfo.

The entries after that are your methods and properties, and all are a indirect function call.

To get the code in memory you use the COM API calls, such as CoCreateInstance

You need to decide to early or late bind. Early binding requires the program to be installed so its type library can be read so it types are compiled into the program. Late binding doesn't care about compile time. There is a conversation Hello object, do you have a function called x. Object replies Yes, it is function 7, Can you please do function 7 object. Early binding function 7 is hard coded. You can only late bind to generic objects. –

So a COM object is 4 x 32 bit. One is the reference count, one is the address if the Virtual Function Table (VTable), 2 are unused. In early binding to call a function 7 the compiler does Address_Of_Vtable (4 x 7) (being 4 bytes for an address). See IDispatch. N.B. Microsoft.Office.Interop is not used at all in late binding. –

Only the generic object can be used in late binding and cannot be used in early binding. Early binding requires you to tell it the specific object. You are mixing and matching. The compiler is confused, just like you. –

Option Strict Restricts implicit data type conversions to only widening conversions, disallows late binding, and disallows implicit typing that results in an Object type. learn.microsoft.com/en-us/dotnet/visual-basic/… –

CodePudding user response：

Sometimes it just doesn't have the information that something is more specific than an Object. If you don't use the default property and use Item instead, as in owb.Worksheets.Item("Sheet1"), you can hover over the Worksheets part to see that represents the .Sheets, but hovering over the Item part reveals it has no details of the items therein - it says it returns an Object.

You know what it should be, so if you had

Imports XL = Microsoft.Office.Interop.Excel

then you could do

osheet = DirectCast(owb.Worksheets("Sheet1"), XL.Worksheet)

and the types would all work out.