Question about a download sample:

Add-in Express™ Support Service
That's what is more important than anything else

Question about a download sample:
How to show an advanced Excel task pane dynamically 
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi,

I am learning this download sample:
How to show an advanced Excel task pane dynamically
https://www.add-in-express.com/support/vb-net-excel.php

What this add-in does?
It makes a task pane appear whenever we type anything in cell A1.


In AddinModule.vb, there is this property:
Public ReadOnly Property A1Cell() As Excel.Range
        Get
            Dim activeWorksheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
            Dim range As Excel.Range = Nothing
            Try
                If activeWorksheet IsNot Nothing Then
                    range = TryCast(activeWorksheet.Cells(1, 1), Excel.Range)
                End If
                Return range
            Finally
                If activeWorksheet IsNot Nothing Then
                    Marshal.ReleaseComObject(activeWorksheet)
                    activeWorksheet = Nothing
                End If
            End Try
        End Get
    End Property





I don't understand this TryCast code:
range = TryCast(activeWorksheet.Cells(1, 1), Excel.Range)


activeWorksheet is a Microsoft.Office.Interop.Excel.Worksheet

Cells(1,1) is a Microsoft.Office.Interop.Excel.Range

Excel.Range is also Microsoft.Office.Interop.Excel.Range

Why would there a need to cast one type into an exactly similar type?

Is it because the 1st attribute is an Expression, and the 2nd one is a Type?
TryCast(Object As Expression, Object As Type) As Object

Thanks

Leon
Posted 01 Feb, 2019 04:04:38 Top
Andrei Smolin


Add-in Express team


Posts: 16970
Joined: 2006-05-11
Hello Leon,

In a test method I write:

        Dim w As Excel.Worksheet = Nothing
        Dim r As Excel.Range = w.Cells(1, 1)


This compiles only if Option Strict is Off. Set it to On and it produces the following compile-time message:

Option Strict On disallows implicit conversions from 'Object' to 'Range'.


The confusion is caused by the implicit way in which VB.NET lets you to access the Range.Item property. You're right: Worksheet.Cells is a Range. But you write Range(1,1) and this a VB-style shortcut for Range.Item(1,1); the Item property returning Object, not Range.

Personally, I prefer to use Option Strict On. It generates compile-time errors and in this way saves you a lot of time spent on debugging.

See section "To set Option Strict in the IDE" at https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 01 Feb, 2019 05:45:54 Top
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi, Andrei

How complicated everything is! Fortunately you are here to prevent me from drowning!

So, the reason why we must use TryCast is to cast an OBJECT into an EXCEL RANGE?

Is there no way (using the Object Browser) to discover that one is an Object, and the other one is an Excel Range?

So, my theory that one is an Expression, and the other one is a Type is false?

But why is TryCast defined as:
TryCast(Object As Expression, Object As Type) As Object ?

What does "Expression" mean anyway?

Is activeWorksheet.Cells(1, 1) an expression because it's made up of 2 parts (separated by a dot?)
And is Excel.Range a Type?

Leon
Posted 01 Feb, 2019 06:11:17 Top
Andrei Smolin


Add-in Express team


Posts: 16970
Joined: 2006-05-11
Leon Lai Kan writes:
Is there no way (using the Object Browser) to discover that one is an Object, and the other one is an Excel Range?


Set Option Strict On.

Leon Lai Kan writes:
So, my theory that one is an Expression, and the other one is a Type is false?


Check https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/trycast-operator. See also https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/operators-and-expressions/index.

Leon Lai Kan writes:
And is Excel.Range a Type?


Most definitely it is.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 01 Feb, 2019 06:41:59 Top
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Great insight!

Thanks a lot.

Leon
Posted 01 Feb, 2019 07:03:45 Top
Andrei Smolin


Add-in Express team


Posts: 16970
Joined: 2006-05-11
Welcome!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 01 Feb, 2019 10:30:59 Top
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi, Andrei

In your sample, there is this Property:
Private ReadOnly Property TaskPane() As ADXExcelTaskPane1

        Get
            Dim taskPaneInstance As AddinExpress.XL.ADXExcelTaskPane = Nothing

            taskPaneInstance = AdxExcelTaskPanesCollectionItem1.TaskPaneInstance

            If taskPaneInstance Is Nothing Then
                taskPaneInstance = AdxExcelTaskPanesCollectionItem1.CreateTaskPaneInstance()
            End If

            Return TryCast(taskPaneInstance, ADXExcelTaskPane1)

       End Get

    End Property



Consider this line:
Return TryCast(taskPaneInstance, ADXExcelTaskPane1)


Is it possible to rewrite this line without using TryCast?
Something like:
Return adx_xl_vb_dynTaskPane.AddinModule.CurrentInstance...

The only reason why I want to do this is to get a better understanding of what the whole code is trying to do.
Not using TryCast can help me get a better insight.


Thanks
Leon
Posted 04 Feb, 2019 06:12:33 Top
Andrei Smolin


Add-in Express team


Posts: 16970
Joined: 2006-05-11
Hello Leon,

Leon Lai Kan writes:
Is it possible to rewrite this line without using TryCast?


The function above returns an object returned by the ADXExcelTaskPanesCollectionItem.TaskPaneInstance property or by the ADXExcelTaskPanesCollectionItem1.CreateTaskPaneInstance() method.

In both case, you get an object of the ADXExcelTaskPane type. Yes, it represents your task pane, but this is the generic type for all Advanced Excel task panes. To get your actual type (rather than the generic one), you cast the returned value to your type.

Note that using TryCast will return Nothing if you cast to a wrong type; cast to a wrong type using CType will throw an exception.

I've noticed that you don't like the idea of casting. Really often you can't bypass it; I suggest that you pay attention to this.

Consider a memory area hold by an object representing your ADXExcelTaskPane1. You can refer to this area by using variables of these types:
- ADXExcelTaskPane1; Dim myPane as MyAddin1.ADXExcelTaskPane1
- ADXExcelTaskPane; Dim myADXPane as AddinExpress.XL.ADXExcelTaskPane
- Object; Dim myObject as Object

Correspondingly, you can use TryCast as follows:
myPane = TryCast(myADXPane, MyAddin1.ADXExcelTaskPane1)
myPane = TryCast(myObject, MyAddin1.ADXExcelTaskPane1)
myADXPane = TryCast(myPane, AddinExpress.XL.ADXExcelTaskPane)
...

In other words, the TryCast operator allows you to use a certain memory area disguised as required by your code. As if you refer to a cat as "a quadruped", "a carnivore", "a feline", or by its name (e.g. Tom or Tiger); all of these refer to the same object (=memory area) via different interfaces.

As to your question, there's no way to directly return an instance of ADXExcelTaskPane1).

Leon Lai Kan writes:
adx_xl_vb_dynTaskPane.AddinModule.CurrentInstance


This returns an instance of the add-in module; the instance is connected to Office. Although you can create an instance of the module; only the instance returned via CurrentInstance will be connected to Office.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 04 Feb, 2019 09:38:56 Top
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi, Andrei

Thanks for your reply.

I've noticed that you don't like the idea of casting. Really often you can't bypass it.


Well, really, it's not that I don't like casting...
The problem is that I don't understand at all what TryCast is doing! I don't know where I can get more explanations on that topic.
If we can't bypass it, well, we've got to live with it.

And your explanations give me a really great insight about what TryCast does!
I wish your Developer's Manual gave more insights like this!

It's very important for me to understand what I am doing, rather than blindly copying a line of code that works without knowing an iota what it does, and how it works!

Now I understand a bit better...

Some questions to clear the remaining mist:

When you say:
Private ReadOnly Property TaskPane() As ADXExcelTaskPane1
     'This is in AddinModule.vb
       
             Return TryCast(taskPaneInstance, ADXExcelTaskPane1)


. . . you are casting what into what?

Are you casting AddinExpress.XL.ADXExcelTaskPane into AddinModule.ADXExcelTaskPane1?

What is the difference between these 2 types of Task Panes? How does each Type arise?

Let me guess:
AddinExpress.XL.ADXExcelTaskPane is the task pane which is inbuilt in Add-in Express??? (Generic Type)
I guess this from the Object Browser.

AddinModule.ADXExcelTaskPane1 is the task pane created by the user using the designer in Add-in Express (ADXExcelTaskPane1.vb) (Actual Type)?

And you are casting the Generic type to the Actual type?

Let me know if I am missing something...

Apart from Task Panes, are there other objects that exist in >=2 versions (Generic / Actual) that we must convert using TryCast?
Some examples?



Best Regards,
Leon
Posted 06 Feb, 2019 00:53:11 Top
Andrei Smolin


Add-in Express team


Posts: 16970
Joined: 2006-05-11
Hello Leon,

Leon Lai Kan writes:
I wish your Developer's Manual gave more insights like this!


No, no! That would be a totally different kind of manual.

In ADXExcelTaskPane.Designer.vb I see the following declaration of the pane class:

Partial Class ADXExcelTaskPane
    Inherits AddinExpress.XL.ADXExcelTaskPane


The relation (inheritance) between these types is what allows me to cast! Now TryCast will return Nothing (and won't produce an exception) if you cast an object of some type to an unrelated type. CType will fire an exception in this scenario.

All descendant classes are related in this sense. Moreover, it is useful sometime to create a custom class (and type) that descends from a more generic class in order to simplify some aspects of using the base class.

See https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/objects-and-classes/inheritance-basics.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 06 Feb, 2019 06:59:51 Top