0

I am trying to set source data for a MS Office 2016 chart in VBA, but recieve error "Object doesn't support this property or method".

The same code works fine with older chart types.

To be more specific, the chart type I am trying to generate is the xlWaterfall.

Usually I would have recorded a macro, but it does not seem work with MS Office 2016 chart types yet.

Functional code:

Works fine :

Crashes at .setSourceData :

Appreciate your help. Thanks in advance.

L. Kleiven
  • 23
  • 5
  • Can you show us the code you have attempted? (even the code that worked for older types) – RGA Jun 30 '16 at 12:52
  • Now I have edited the question – L. Kleiven Jun 30 '16 at 13:09
  • If you would, please copy the actual code into the body of your question (rather than including a photo). This will help you, as some potential answerers may not be able to open the images due to network restrictions, and helps them to debug the code by allowing them to copy-paste it into their own compiler – RGA Jun 30 '16 at 13:11
  • In terms of a solution, have you verified that the data in the source range is valid for the chart type? – RGA Jun 30 '16 at 13:13
  • Yes, i have comfirmed that the range contains the preffered cells and tried to manually set them as source data. When I executed the same operation manually in Excel, with the same source data, it worked fine – L. Kleiven Jun 30 '16 at 13:20
  • Is it possible that the source data range is too large for the created chart? (honestly just spitballing ideas here, hence why I'm not writing any as answers) – RGA Jun 30 '16 at 13:25
  • I considered something like that when i first started debugging, since I am using the .end(xlToRight) method. But when debugging it, the range was exactly like the range i used when manually making the waterfall chart. I have read at stackoverflow that the MS Office 2016 charts are saved as shapes and not chartObjects. But again, the created waterfall chart reacts when I am formatting it through the "with activeChart" code, It is mainly the setSourceData method that fails. – L. Kleiven Jun 30 '16 at 13:39
  • I tried to select the source data range before adding the chart. That worked, and the range was set as dataSource by default – L. Kleiven Jun 30 '16 at 13:39
  • Glad you found a solution! In fact, why don't you post it as an answer to this question so that anyone searching for a similar issue can find it too – RGA Jun 30 '16 at 13:40

1 Answers1

0

Strange that the setDataSource method crashed, but I found a work around: When first selecting the preffered range and then creating the chart with the range selected, it was set as data source by default.

Solution

L. Kleiven
  • 23
  • 5