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