C# - why Histogram does not work in Excel 2016?

Question

I have excel 2016 vsto application build in c#. I have a chart control and want to set chart type to the histogram. I can select this chart from excel but I am not able set this chart type programmatically.

In other words, I am not able to find histogram chart type in the XlChartType enum.

Have you tried inserting one through the interface and then checking its `Type` via code? — Chris, Oct 30 '17 at 18:18

Tarun Lalwani · Accepted Answer · 2017-11-06T12:49:08.337

3

In such cases you should always open Object Browser in excel macros and and search for what you are looking for

As you can see from object browser, the value for histogram is 118 or 76 in hex. You can use the same in your code directly by defining a constant

Edit-1: The code

Debugging your code below I found a issue

Worksheet sheet1 = Globals.Factory.GetVstoObject(Globals.Sheet1.Application.Worksheets[1]);
//chartTest
Excel.ChartObject myChart = (Excel.ChartObject)sheet1.ChartObjects("chartTest");
myChart.Chart.SetSourceData(sheet1.Range["A1", "A51"]);
myChart.Chart.Type = 118;

What you need to do is assign 118 to ChartType and not Type. Below code worked fine for me

Worksheet sheet1 = Globals.Factory.GetVstoObject(Globals.Sheet1.Application.Worksheets[1]);
//chartTest
Excel.ChartObject myChart = (Excel.ChartObject)sheet1.ChartObjects("chartTest");
myChart.Chart.SetSourceData(sheet1.Range["A1", "A51"]);
Excel.XlChartType myType = (Excel.XlChartType)118;

myChart.Chart.ChartType = myType;

edited Nov 06 '17 at 12:49

answered Oct 31 '17 at 14:37

Tarun Lalwani

142,312
9
204
265

I tried to set myChart.Chart.Type = 118 and I am getting "Error HRESULT E_FAIL has been returned from a call to a COM component". – user781700 Nov 02 '17 at 19:34
@user781700, possible to get a minimal repo to debug? – Tarun Lalwani Nov 02 '17 at 19:45
@user781700, please provide a link which doesn't require a signup and a paid account – Tarun Lalwani Nov 03 '17 at 04:47
Here is the free download link: http://www.filedropper.com/https://ufile.io/es8bc – user781700 Nov 03 '17 at 14:27
@user781700, please check the edit. The code now works fine for me – Tarun Lalwani Nov 06 '17 at 12:49

score 0 · Answer 2 · answered Nov 06 '17 at 00:19

0

Simply record a Macro while you add a Histogram to your sheet.

Stop recording and press Alt+F11 to open the Visual Basic Editor. Open the recoded macro and convert/compare the VBA to C# code.

answered Nov 06 '17 at 00:19

Jeremy Thompson

61,933
36
195
321

C# - why Histogram does not work in Excel 2016?

2 Answers2

Linked