MaratYa

In VSTO Excel document level application I need to add NamedRange controls on various worksheets. All examples I found show the creation of NamedRange from Worksheet_startup event like this:

Dim textInCell As Microsoft.Office.Tools.Excel.NamedRange
textInCell = Me.Controls.AddNamedRange(Me.Range("A1"), "cellText")

But I cannot seem to be able to create a named range control on a sheet from any other event but that Sheet_startup.

I want to create a named control on a sheet from my code on a custom control on a taskpane. If this is possible, could someone please point me to a code example


Thank you,
Marat.



Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

Phil Hoff - MSFT

You can create an instance of your custom control, add it to the Actions Pane, then subscribe to some event in your custom control (i.e. Button.Click). The event handler can then directly add the control to the worksheet (if the worksheet is the owner of the event handler) or add the control to the worksheet by first retrieving the worksheet from the Globals class (i.e. Globals.Sheet1).

Code Snippet

Public Class Sheet1

Private WithEvents _button As New Button

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

_button.Text = "Add Named Range"

Globals.ThisWorkbook.ActionsPane.Controls.Add(_button)

End Sub

Private Sub Sheet1_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

End Sub

Private Sub OnClicked() Handles _button.Click

Controls.AddNamedRange(Range("A1"), "NamedRange")

End Sub

End Class

-Phil





Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

MaratYa

Phil,

Thank you, but that does not answer my question.

I want to initiate <sheet object>.AddNamedRange method from the code behind the control, not from the sheet's own method or sheet_startup event.

From the workbook startup event, going through the collection of worksheets, I add to the taskpane one control for each sheet, passing the sheet name as parameter to the control's constructor.

Each control gets a hold of it's assigned worksheet and creates Host Controls on this worksheet based on the analysis done by private method inside the custom control itself.

From the control's constructor I am trying to get to the Globals.ThisWorkbook.sheets("sheet1") object by name, but for some reason I cannot get that object.

How can I get to that Globals.ThisWorkbook.sheets("sheet1") object from inside my control's scope, so I could call the .AddNamedRange from it

Thank you for your reply,

Marat.





Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

Cindy Meister

<<From the workbook startup event, going through the collection of worksheets, I add to the taskpane one control for each sheet, passing the sheet name as parameter to the control's constructor.>>

Why not pass the sheet object, rather than the name

The other approach might be to loop through all the objects in Globals.ThisWorkbook.Sheets and compar the name until you hit on the right one.






Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

MaratYa

>> Why not pass the sheet object, rather than the name

Exactly so. That is what I ended up doing as a work-around instead of what I wanted to do at first. It works. I pass the sheet object itself to the constructor instead of only the name, as I wanted. Therefore, I researched the surroundings a little while trying to make it work.

When I started to trace through the code in the debugger, trying from the code behind different objects (workbook, sheet, control) to point my Dim'ed objects to Globals members (like a worksheet), it seems that I cannot see into the "outside world" from within the control.

Almost seems like a domain crossing protection is in place. I could pass the sheet object into custom control from a Sheet event and then use the Sheet object's abilities from inside the custom control, but I cannot snoop for the sheet objects from inside that same custom control and use the capabilities of Sheet object without the "outside world" (in this case the code in a sheet or workbook scope) offering me the trust explicitly by providing the object itself, and not less! Knowing the Sheet object's name was not enough. I could not do "For each" on the sheets collection and grab those objects from the "Globals" either, while inside the custom control.

Although I could understand such logic, but why is there no documentation to this fact that I could find

Perhaps this is some fundamental rule that I missed somehow There is a difference between a button_event accessing the form's properties and a custom control's methods modifying the parent object's environment and behaviour. A kind of "don't you touch my objects unless I explicitly give one to you by myself".

From the answers of those who know so far I concluded the following:

In Excel VSTO project, a custom control on a task pane cannot instantiate a sheet HostControl object without being passed the sheet object itself into the custom control constuctor as a parameter object.

Is this true

Thank you,

Marat.





Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

Cindy Meister

Well, I'm no expert on the internal structures of VSTO, but...

All of the objects you want to work with are actually classes. Globals is giving you a way to access the classes as objects. But if Globals wasn't designed to give you an enumerable collection, then it's not. I have not idea whether the idea behind doing that way is as "paranoic" as you describe, or whether it was simply a design decision (least amount of effort / least amount of complexity).

But there's no reason you can't populate a list, array or collection with each host object plus information you want to use in this manner when the project starts up. The members of "Globals" is static at design time, and any that are added at run time your code can add to the list dynamically.

Personally, though, I see no problem at all with passing the object itself to the constructor rather than a string. Especially since it's the object that you actually want to work with.






Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

MaratYa

Of course I can always find an alternative (already did), like in this case, passing the object into the constructor. I could pass the whole woorkbook to the constructor too, I suppose, just in case, and access anything I need from there. But that is a different tactic, though, not the one I wanted to use originaly.

It's not a problem for me iether, rather a healthy desire to know why something doesn't work the way I expected it to work. I wanted the control to independently arrange things in any place on the workbook the control decides to. But that tactic was not successful. The next time I will not consider it as a possible tactic.

About my specific question. Although I did not get a straight "yes" or "no" answer, I concluded that the statement below is a true statement:

>> In Excel VSTO (document level) project, a custom control on a task pane cannot instantiate a sheet HostControl object without being passed the sheet object itself into the custom control constuctor as a parameter object.

Thank you for your help.

Marat.





Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

Phil Hoff - MSFT

The answer to your specific question is not strictly true. There are any number of ways for a custom control to obtain a reference to a particular worksheet without passing it to the control's constructor. At some point, however, you need a reference to the worksheet in order to call the method that adds a control. The exact method employed is a matter of good design principles, convenience, and personal taste.

The Globals class is just a simple means of accessing the known Worksheet/Workbook instances from anywhere within the project. It would be nice, I admit, if the VSTO Workbook class exposed a collection of VSTO Worksheet instances rather than just the underlying Excel worksheets. You could (as Cindy suggested) expose your own worksheet collection from the Globals class that allowed indexing by name (as shown in the example below). However, keep in mind that relying on the Worksheet.Name property can be problematic as the user can change it on a whim. To me, it seems better to simply pass each control the worksheet(s) on which it is to operate or to associate a unique identifier with each worksheet that you can then use as a map to the correct worksheet.

-Phil

Code Snippet

internal sealed partial class Globals

{

internal class WorksheetCollection : KeyedCollection<string, Worksheet>

{

protected override string GetKeyForItem(Worksheet item)

{

return item.Name;

}

}

private static WorksheetCollection _sheets;

internal static WorksheetCollection Sheets

{

get

{

if (_sheets == null)

{

_sheets = new WorksheetCollection();

_sheets.Add(Globals.Sheet1);

_sheets.Add(Globals.Sheet2);

_sheets.Add(Globals.Sheet3);

}

return _sheets;

}

}

}





Re: Visual Studio Tools for Office How to create Excel HostControl from code behind Custom Control on TaskPane?

MaratYa

That answers it!

Thank you Phil and Cindy.

Marat.