excelvb.netoffice-automationexcel-automationcom-automation

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


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)?


Solution

  • 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.