Excel VBA - a macro to 'Format Data Series - Gap Width' in a chart?

2
votes

I have to change the design of about 100 charts in Excel 2011, and I'm trying to speed things up a little with macros.

The problem is that Excel doesn't want to simply record some actions into a macro, it seems they need to be manually written.

I've managed to make a macro for changing the formatting of Data Labels using tips from this thread: Formatting data labels in Excel charts using VBA

But now I'd like to also edit Label Series- Gap Width percentage, through a macro. I don't know the exact VBA syntax for this action. Maybe someone here can help.

I've tried 

ActiveChart.SeriesCollection(1).DataSeries.GapWidth = "110%"

But it didn't work. Run-time error 438, Object doesn't support property or method.

Does anyone know the correct syntax?

1
vbaexcelcharts
Why "110%"??? Or is a Typo?? Can you share a screen shot of the chart?? - Elbert Villarreal
It's not a typo, default value is 150%. The value refers to the width (thickness) of the bar. If you want the spacing between bars to be thicker than the bars themselves, you set it to be over 100%. Image: i.imgur.com/Y22eBjw.jpg - Tina Malina

1 Answers

3
votes

You can try the following code:

ActiveChart.ChartGroups(1).GapWidth = 110