The Document Object Model (DOM) is a term which describes how things are structured. By applying the principles and methods in this post, you will be able to do almost anything you want with charts in Excel using VBA.

It is not feasible to provide code for every scenario you might come across there are just too many options. As a result, some of the code presented in this post may not work with versions before Excel 2013. For example, the AddChart2 method replaced the AddChart method. In Excel 2013, many changes were introduced to the charting engine and DOM. Understanding Excel’s Document Object Model (DOM) is essential to understand how VBA can be used with charts. While it might be tempting to skip straight to the section you need, I recommend you read the first section in full. But once you’ve mastered it, you’ll know the situations when VBA is the best option. The short code snippets below will help you apply some of the most common chart options with VBA (and hopefully turn those hours into minutes).īut don’t let me fool you into thinking that using VBA is quick and easy, it’s not. When we want to apply those hundreds of settings to lots of charts, it can take hours and hours of frustrating clicking.

This is great for creating precisely the visualization we want but can be time-consuming to apply. So it should be able to take the given range as values for XValues, but I can't understand why this error appears.Charts and graphs in Excel have hundreds of different options. The XValues property can be set to a range on a worksheet or to anĪrray of values, but it cannot be a combination of both SeriesCollection(1).XValues = XLabels' in the function above. When I click debug it marks the row 'Set. ListColumns(6).DataBodyRangeĬall DrawChart2(.Worksheets(ChartsWorksheet), chart_labels, chart_values, chartTitle, 10, 10) Worksheets('LastDayData').ListObjects(Queryname) I call the function this way: ChartsWorksheet = 'Summary' SeriesCollection(1).XValues = XLabels ' Here is the error With obj_(a, b, 900, 300) ' Left, Top, Width, Height

'b - top border position of chart in pixels

'a - left border position of chart in pixels 'obj_worksheetTgt - Object worksheet on which to be placed the chart I have this VBA function for drawing charts in Excel 2013: Sub DrawChart2(obj_worksheetTgt As Worksheet, ByVal XLabels As Range, ByVal DataValues As Range, ByVal chartTitle As String, a As Integer, b As Integer)

Excel vba set chart title hight