locked
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

    Wednesday, April 04, 2012 4:09 PM

Answers

  • 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.

    Thursday, April 05, 2012 7:59 AM
  • 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.

    Friday, April 06, 2012 4:41 AM
  • 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

    Monday, April 09, 2012 3:36 PM
  • 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


    Tuesday, April 24, 2012 9:09 AM

All replies

  • 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.

    Thursday, April 05, 2012 7:59 AM
  • 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

    Thursday, April 05, 2012 10:27 AM
  • 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.

    Friday, April 06, 2012 4:41 AM
  • 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

    Monday, April 09, 2012 3:36 PM
  • 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


    Tuesday, April 24, 2012 9:09 AM