locked
VB.net Excel - Chart - Color of the bars in a chart RRS feed

  • Question

  • Hi,

    I have created a simple barchart in Excel using VS2013 Pro.  Everything works and appears fine.

    A fragment is given below.  In the "With chart_Format" section each line such as ".ChartTitle.Characters.Font.Name" comes up with Intellisense prompts.  However after ".SeriesCollection(1)." there is no Intellisense and I have added "Interior.Color" in a similar way that I used in VB6.

    I have found that this works BUT I am concerned that this is not the correct way to set a barchart color in Visual Studio.

    Is this an acceptable way or is it a VBA method that happens to work?  Is there a better VS way to set the color of a barchart??

    Thanks for your time

    Margarita

    Dim xlCharts As Excel.ChartObjects
    Dim chart_Investigate As Excel.ChartObject
    Dim chart_Format As Excel.Chart
    Dim chart_Range As Excel.Range
    xlCharts = oSheet.ChartObjects
    chart_Investigate = xlCharts.Add(320, 160, 400, 400)
    chart_Range = oSheet.Range("C2" , "D20")
    chart_Format = chart_Investigate.Chart
    With chart_Format
         .SetSourceData(Source:=chart_Range)
         .ChartType = Excel.XlChartType.xlColumnClustered
         .HasTitle = True
         .ChartTitle.Characters.Text = "xxxxx"
         .ChartTitle.Characters.Font.Name = "Arial"
         .ChartTitle.Characters.Font.Bold = True
         .ChartTitle.Characters.Font.Size = 14
         .HasLegend = False
         .SeriesCollection(1).Interior.Color = bar_color
    End With


    Winchestermili

    • Moved by Carl Cai Monday, April 20, 2015 9:58 AM more related to suggested forum
    Friday, April 3, 2015 1:15 PM

Answers

  • Hi,

    I have created a simple barchart in Excel using VS2013 Pro.  Everything works and appears fine.

    A fragment is given below.  In the "With chart_Format" section each line such as ".ChartTitle.Characters.Font.Name" comes up with Intellisense prompts.  However after ".SeriesCollection(1)." there is no Intellisense and I have added "Interior.Color" in a similar way that I used in VB6.

    I have found that this works BUT I am concerned that this is not the correct way to set a barchart color in Visual Studio.

    Is this an acceptable way or is it a VBA method that happens to work?  Is there a better VS way to set the color of a barchart??

    Thanks for your time

    Margarita

    Dim xlCharts As Excel.ChartObjects
    Dim chart_Investigate As Excel.ChartObject
    Dim chart_Format As Excel.Chart
    Dim chart_Range As Excel.Range
    xlCharts = oSheet.ChartObjects
    chart_Investigate = xlCharts.Add(320, 160, 400, 400)
    chart_Range = oSheet.Range("C2" , "D20")
    chart_Format = chart_Investigate.Chart
    With chart_Format
         .SetSourceData(Source:=chart_Range)
         .ChartType = Excel.XlChartType.xlColumnClustered
         .HasTitle = True
         .ChartTitle.Characters.Text = "xxxxx"
         .ChartTitle.Characters.Font.Name = "Arial"
         .ChartTitle.Characters.Font.Bold = True
         .ChartTitle.Characters.Font.Size = 14
         .HasLegend = False
         .SeriesCollection(1).Interior.Color = bar_color
    End With


    Winchestermili

    Hello,

    Based on the code above, it seems that the Excel for developer forum will be the proper forum to get help for this issue.

    You could consider posting this isshe in that form to get help.

    Regards,

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 7, 2015 6:19 AM

All replies

  • I don't know if it's the correct way to set the color - I guess, yes, as it works - however, regarding intellisense, if you know the type of the object returned by the SeriesCollection function, you can cast to that type in order to make intellisense available. According to the Excel documentation, it returns either a Series object or a SeriesCollection object. If you are sure it's always one of these, you can directly cast to that type:

             Dim Series As Excel.Series
    
             Series = DirectCast(.SeriesCollection(1), Excel.Series)
             Series.Interior.Color = bar_color


    Otherwise, you could make it depend on the type:

             Dim oSeries As Object
    
             oSeries = .SeriesCollection(1)
    
             If TypeOf oSeries Is Excel.Series Then
                Dim Series As Excel.Series
                Series = DirectCast(oSeries, Excel.Series)
                '...
             ElseIf TypeOf oSeries Is Excel.SeriesCollection Then
                Dim Col As Excel.SeriesCollection
                Col = DirectCast(oSeries, Excel.SeriesCollection)
                '...
             End If


    Armin

    Friday, April 3, 2015 1:59 PM
  • Hello,

    Perhaps the following page may be of assistance

    http://www.siddharthrout.com/2012/09/06/creatingformatting-a-chart/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, April 3, 2015 2:31 PM
  • Hi,

    I have created a simple barchart in Excel using VS2013 Pro.  Everything works and appears fine.

    A fragment is given below.  In the "With chart_Format" section each line such as ".ChartTitle.Characters.Font.Name" comes up with Intellisense prompts.  However after ".SeriesCollection(1)." there is no Intellisense and I have added "Interior.Color" in a similar way that I used in VB6.

    I have found that this works BUT I am concerned that this is not the correct way to set a barchart color in Visual Studio.

    Is this an acceptable way or is it a VBA method that happens to work?  Is there a better VS way to set the color of a barchart??

    Thanks for your time

    Margarita

    Dim xlCharts As Excel.ChartObjects
    Dim chart_Investigate As Excel.ChartObject
    Dim chart_Format As Excel.Chart
    Dim chart_Range As Excel.Range
    xlCharts = oSheet.ChartObjects
    chart_Investigate = xlCharts.Add(320, 160, 400, 400)
    chart_Range = oSheet.Range("C2" , "D20")
    chart_Format = chart_Investigate.Chart
    With chart_Format
         .SetSourceData(Source:=chart_Range)
         .ChartType = Excel.XlChartType.xlColumnClustered
         .HasTitle = True
         .ChartTitle.Characters.Text = "xxxxx"
         .ChartTitle.Characters.Font.Name = "Arial"
         .ChartTitle.Characters.Font.Bold = True
         .ChartTitle.Characters.Font.Size = 14
         .HasLegend = False
         .SeriesCollection(1).Interior.Color = bar_color
    End With


    Winchestermili

    Hello,

    Based on the code above, it seems that the Excel for developer forum will be the proper forum to get help for this issue.

    You could consider posting this isshe in that form to get help.

    Regards,

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 7, 2015 6:19 AM