Excel add-ins VSTO: add a chart
Everything in Excel add-in development is tricky and difficult, but when it comes to charts — you need to get creative. The basic idea is to insert chart object, take its’ reference and then set data to via the reference. Once you see the code, it will all look quite straightforward.
This code is the one that creates your charts, any kind of chart you want — even if you probably cannot see where the type is right now. First, we start by retrieving a reference for an active worksheet, obviously it could be done with a generated sheet as well. After that, you can see the AddChart2 method, and yes — it is AddChart2, NOT AddChart. This method creates a chart and gives you a variable for its’ reference. The first parameter needs to be -1, the second is the type of chart and this is not just some magical value, there is a nice table for it in the official documentation (https://docs.microsoft.com/en-us/office/vba/api/excel.xlcharttype). Paramaters 3–6 are for margins and sizes, you the parameter commenting for the method includes the specifics there. The final parameter is set to true, and just like the first one — it should stay that way.
At this point, you would have a chart with no data, properties, just a completely empty one. The data is assigned from the cells in the sheet, so for that we establish a range (rng variable), the cells in the range are assigned either a name for a bar or a value for it. Do notice how the cell indexes start from 1, not from 0. Finally, to apply that data to the newly created chart, you simply use SetSourceData and provide the range of data in the parameters.
That is all there is to it, other than that you have the reference variable to that chart and from there you can assign title and other general chart properties.
Resources
If you want to learn more about Excel add-in development, you should take a look at the following
