Adding Host Controls at Run Time

Most host controls can be added to the document at run time. The exception to this rule is related to XML mapping. As you learned earlier, you can add XML-mapped elements only by using the XML Structure task pane. To programmatically add other host controls, such as a ListObject or NamedRange, you must use helper methods provided by VSTO. If you programmatically add a list object, chart, or range object in the same way you add it in VBA, it is created as an Interop object and not a VSTO object. For example, the code in Listing 7.11 adds a list to Sheet1 using the Add method of the ListObjects collection. This is an Interop list, not a VSTO list.

Listing 7.11. Programmatically adding an Interop ListObject to Excel

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

' Create an Interop ListObject.

Dim InteropList As Microsoft.Office.Interop.Excel. _ ListObject = Me.ListObjects.Add( _ Excel.XlListObjectSourceType.xlSrcRange, _ Me.Range("A1", "C3"))

End Sub

If you write additional code to access the methods and properties of the InteropList you created, you will notice that none of the data binding methods or properties is available in IntelliSense. This is because an Interop list object is added to the worksheet and it does not have the data binding capabilities (or events) that are found on a ListObject control. Figure 7.14 shows the IntelliSense list, where you don't see data binding properties such as the BindingContent property on the list object.

VSTO provides helper methods that enable you to programmatically add host controls to a worksheet. You cannot instantiate a host control using the New keyword; instead, you must use the VSTO helper methods.

Figure 7.14. Viewing IntelliSense of an InteropList object

For Excel, there are three helper methods: AddNamedRange, AddListObject, and AddChart. When you add a ListObject control programmatically, the helper methods have two parameters. The first parameter is the range where you want to add the control, and the second parameter is the name you want to give the control. Listing 7.12 shows you how to add a ListObject control to Sheet1 using the AddListObject helper method.

Listing 7.12. Programmatically adding a ListObject control to Excel

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

Dim VSTOList As Microsoft.Office.Tools.Excel.ListObject = _ Me.Controls.AddListObject(Me.Range("A1", "C3"), _ "VSTOList")

This time, when you access IntelliSense, you will see properties specific to data that is bound to the control. These properties exist only for a VSTO ListObject control. See Figure 7.15 for the IntelliSense list.

Figure 7.15. Viewing IntelliSense of a VSTO ListObject control

Programmatically adding a NamedRange control to the worksheet works in the same way as the ListObject; you use a helper method and pass the desired range and name. The code in Listing 7.13 shows you how to add a NamedRange control to a cell on Sheet1 of a workbook created with VSTO and then add data to the NamedRange.

Listing 7.13. Programmatically adding a NamedRange to a cell

Dim myNamedRange As Microsoft.Office.Tools.Excel.NamedRange _ = Me.Controls.AddNamedRange(Me.Range("A1:B4"), _ "myNamedRange") myNamedRange.Formula = "=RAND()"

When you programmatically add a Chart control to a worksheet, you will see that the AddChart method is overloaded. The first overload requires a range parameter and a name parameter, just as the AddNamedRange and AddListObject methods required. The second overload enables you to add the chart to a specific location and specify its size. You pass in the left and top positions, width and height of the chart, and the name you want to give the control. This helps you to have more control over the exact positioning of the chart.

If you wanted to use the data in the NamedRange to programmatically create a chart, you could do so as the code in Listing 7.14 illustrates.

Listing 7.14. Programmatically adding a Chart to the worksheet

Dim myNamedRange As Microsoft.Office.Tools.Excel.NamedRange = _ Me.Controls.AddNamedRange(Me.Range("A1:B4"), "myNamedRange")

myNamedRange.Formula = "=RAND()"

Dim myChart As Microsoft.Office.Tools.Excel.Chart = _ Me.Controls.AddChart(Me.Range("A6:E15"), "myChart")

myChart.SetSourceData(myNamedRange.InnerObject, _ Excel.XlRowCol.xlColumns)

myChart.ChartType = Excel.XlChartType.xl3DPie

In this code example, you use the RAND function to add data to the range of cells specified by the NamedRange control myNamedRange. The RAND function is a random number generator that adds random data to each cell. You then create a Chart control on the worksheet and use the data in the NamedRange to populate the chart. Although your data will differ, the result of pressing F5 to run this code is a worksheet that contains a NamedRange control and a Chart control similar to the example in Figure 7.16.

Figure 7.16. Creating a chart on a worksheet
+1 0

Post a comment