Handling Excel Chart Objects and Option Strict On

Question

I am struggling to handle a Graph Object in Excel. The Graph has been created in sheet 2, however I need to format the axes, legends and add other series. The problem is I get Option Strict On disallows late binding.

						' Format Chart sheet, format chart
						objSheet2.Activate()
						With objSheet2

							objChart = CType(objExcel.Charts.Add, Excel.Chart)

							With objChart

								.SetSourceData(Source:=objRange1, PlotBy:=Excel.XlRowCol.xlColumns)
								'.ChartType = Excel.XlChartType.xlCylinderColStacked
								.ChartType = Excel.XlChartType.xlColumnClustered


								.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue)
								.HasLegend = True
								'.Legend.Delete()
								.PlotArea.Interior.ColorIndex = 2
								.ChartArea.Interior.ColorIndex = 2
								.ChartArea.Interior.PatternColorIndex = 1

							End With

							objChart = objChart.Location(Where:=Excel.XlChartLocation.xlLocationAsObject, Name:="Chart")
							'objChart.Name = "Turnover Chart"
							'objChart.Activate()

							With objChart

								objAxis = CType(objChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary), Excel.Axis)
								With objAxis
									.HasTitle = True
									.AxisTitle.Text = "Quantity Sold"
									.HasMajorGridlines = False
									.HasMinorGridlines = False
								End With

								objAxis = CType(objChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary), Excel.Axis)
								With objAxis
									.HasTitle = True
									.AxisTitle.Text = "Product Groups"
									.HasMajorGridlines = False
									.HasMinorGridlines = False
								End With

		' Following lines give Late Binding message						
                   ObjChartObjs = CType(objChart.ChartObjects("Chart 1"), Excel.ChartObjects).Activate

								xlsAxisCategory = CType(.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
								xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
								xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text() = "Date"
								xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMajorGridlines = False
								xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasMinorGridlines = False

								xlsAxisValue = CType(.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
								xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
								xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text() = "Weight"
								xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMajorGridlines = False
								xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMinorGridlines = False

							End With	' objChart

Answer 1

Hi Terry,

Please take a look at this KB: http://support.microsoft.com/kb/311329 

Option Strict restricts implicit data type conversions to only widening conversions. Widening conversions explicitly do not permit any data type conversions in which data loss may occur and any conversion between numeric types and strings. For more information about widening conversions, see the Widening Conversions section.

And you can find this is by design in this documentation: http://msdn.microsoft.com/en-us/library/efwbatax(v=vs.90).aspx 

Visual Basic allows implicit conversions of any data type to any other data type. However, data loss can occur if the value of one data type is converted to a data type with less precision or a smaller capacity. Option Strict On ensures compile-time notification of these types of conversions so they may be avoided. You cannot use Option Strict On with late binding.

I hope this is clear.

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Mike,

Thanks for the KB references. I was aware of these, however I am using Early Binding. It seems that CType can produce the correct casting, but it is a 'Dark Art' to know how to cast the Excel objects correctly. I have dealt with the Workbook, Worksheet, Ranges and Chart objects using CType, but just cannot get to the objects within the chart.

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.ComponentModel
Imports System.Diagnostics
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop

' Can also use below instead of Imports Microsoft.Office.Interop
Imports Excel = Microsoft.Office.Interop.Excel

Answer 3

Hi Terry,

what do you mean "but just cannot get to the objects within the chart." 

To retrieve and manipulate a excel chart object:http://www.codeproject.com/Articles/14082/Creating-Excel-Charts-Dynamically-from-Database-us

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

You are creating the chart as a standalone chart sheet

objChart = CType(objExcel.Charts.Add, Excel.Chart)

then converting it to an embedded chart on a worksheet

objChart = objChart.Location(Where:=Excel.XlChartLocation.xlLocationAsObject, Name:="Chart")

This action destroys the object in the variable objChart, You need to redefine it (this is VBA, translate it into your syntax):

Set objChart = Worksheets("Chart").ChartObjects(Worksheets("Chart").ChartObjects.Count).Chart

Now the rest of your code should be okay, except that I don't know what you're doing here:

ObjChartObjs = CType(objChart.ChartObjects("Chart 1"), Excel.ChartObjects).Activate

No need to activate objects if you can reference them through the OM.

---

Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
Peltier Tech Blog

Answer 5

Thanks Jon, you were correct in how I was creating the chart. I altered the code to create the chart within the worksheet and can now alter some properties.

objSheet2.Activate()
With objSheet2

	ObjChartObjs = CType(objSheet2.ChartObjects, Excel.ChartObjects)
	objChartObj = ObjChartObjs.Add(150, 30, 400, 400)
	objChart = objChartObj.Chart

	With objChart

	.SetSourceData(Source:=objRange1, PlotBy:=Excel.XlRowCol.xlColumns)

	' Chart type
	.ChartType = Excel.XlChartType.xlCylinderColStacked						
	.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue)

	' Chart title
	.HasTitle = True
	.ChartTitle.Text = "Turnover Chart"

	' Legend
	.HasLegend = True
	
	With .Legend
		.Position = Excel.XlLegendPosition.xlLegendPositionLeft
		.Border.Color = ColorTranslator.ToOle(Color.Green)
	End With

	' Plot and Chart Area
	.PlotArea.Interior.ColorIndex = 2
	.ChartArea.Interior.ColorIndex = 2
	.ChartArea.Interior.PatternColorIndex = 1

	' Chart Axes
	objAxis = CType(objChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary), Excel.Axis)
	With objAxis
		.HasTitle = True
		.AxisTitle.Text = "Quantity Sold"
		.HasMajorGridlines = False
		.HasMinorGridlines = False
	End With

	objAxis = CType(objChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary), Excel.Axis)
	With objAxis
		.HasTitle = True
		.AxisTitle.Text = "Product Groups"
		.HasMajorGridlines = False
		.HasMinorGridlines = False
	End With

	objSeries = CType(.SeriesCollection(1), Excel.Series)
	With objSeries
		.Name = "1050"
	End With

	End With	' objChart