Turn off marker shadow on vba-generated Excel plots

Question

I'm porting some code I use to generate scatter plots in Excel from Win 7 / Excel 2010, to OS X / Excel 2011. On the Mac, the data points show up with a shadow. I don't want the shadow, and I can't figure out how to get rid of it.

Using this worksheet (it just has random numbers in cells A1:B6 if you don't want to download my macro-enabled worksheet) the following code runs fine, but produces data points with shadows:

Sub plotNoShadow()

  Dim x As Range
  Dim y As Range

  Dim cht As ChartObject

  Set x = ActiveSheet.Range("A1:A6") 'haphazard numbers
  Set y = ActiveSheet.Range("B1:B6")

  Set cht = ActiveSheet.ChartObjects.Add(Left:=150, Top:=50, Width:=200, Height:=160)
  With cht.Chart
    .ChartType = xlXYScatter
    .SeriesCollection.NewSeries
    With .SeriesCollection(1)
        .XValues = x
        .Values = y
        .Format.Shadow.Visible = msoFalse 'This seems to parse, but have no effect
    End With
    .SetElement (msoElementLegendNone)
    .SetElement (msoElementPrimaryValueGridLinesNone)
  End With      
End Sub

Can anyone explain to me:

1. How to modify this code to remove the shadows, and

2. How it is that this code runs, but setting the SeriesCollection(1).Format.Shadow.Visible to msoFalse manages to run without having any apparent effect?

Per the comment thread below, here's a screencap of the macro output on the left, points with shadow turned on then off in the middle, and shadows turned on on the right. I edited the macro to remove legend and gridlines, for clarity. It looks like the macro output has less shadow than the 'shadow on' state, but more shadow than the 'shadow off' state.

Answer 1

Several of the default Chart Styles in Excel that produce a slight 3D effect also have a minor drop-shadow as pointed out earlier.

On my Excel (in Windows 7) the default chart style is 2, so no shadow or 3D effect appears. I suspect that on the Mac, the default chart style is different.

To fix this, you can set the Chart Style in your code:

With cht.Chart
  .ChartType = xlXYScatter
  .ChartStyle = 2
  .....

In Excel, the ChartStyle settings have the ability to modify all aspects of the appearance of the chart, including the look of the Marker. The only thing MarkStyle sets is the shape of the Marker. All of the other appearance aspects of the Marker are overridden when the ChartStyle is changed.

EDIT

The above comments are still basically true, but I have found a way to shut off the shadow. Like many things with Excel, it is not as easy as you would think. Setting the visibility property of the shadow has no effect if done in code (for whatever reason), so you need to set the shadow type to "No Shadow".

Sub plotNoShadow()

  Dim x As Range
  Dim y As Range

  Dim cht As ChartObject

  Set x = ActiveSheet.Range("A1:A6") 'haphazard numbers
  Set y = ActiveSheet.Range("B1:B6")

  Set cht = ActiveSheet.ChartObjects.Add(Left:=150, Top:=50, Width:=200, Height:=160)
  With cht.Chart
    .ChartType = xlXYScatter
    .ChartStyle = 26 'Something 3D with a default shadow. This line can be left out.
    .SeriesCollection.NewSeries
    With .SeriesCollection(1)
        .XValues = x
        .Values = y
        .Format.Shadow.Type = msoShadow30 'This is the code for an inner shadow
    End With
  .SetElement (msoElementLegendNone)
  .SetElement (msoElementPrimaryValueGridLinesNone)
  End With

End Sub

EDIT Again

Actually, msoShadow30 is an "internal shadow" style and may look strange depending on your marker style. msoShadow41 is the closest thing to "No Shadow" that I have been able to find. It's actually the code for shadow below, but by default, it is too faint to see. If it does show up, the color can always be changed to make it disappear.

Or even better, set the tranparency to 1 (fully transparent):

.Format.Shadow.Transparency = 1.0 'Fully transparent

Answer 2

The shadow that you are seeing is actually not a shadow. What I mean is that is the default way a marker looks without shadow.

Unfortunately you cannot do much about it. See the snapshots below and you will know what I mean.

Snapshot:

Alternative:

You can however play with the marker size to minimize that effect. Try this code.

Sub plotNoShadow()

  Dim x As Range
  Dim y As Range

  Dim cht As ChartObject

  Set x = ActiveSheet.Range("A1:A6") 'haphazard numbers
  Set y = ActiveSheet.Range("B1:B6")

  Set cht = ActiveSheet.ChartObjects.Add(Left:=150, Top:=50, Width:=200, Height:=160)
  With cht.Chart
    .ChartType = xlXYScatter
    .SeriesCollection.NewSeries
    With .SeriesCollection(1)
        .MarkerStyle = 2
        .MarkerSize = 7
        .XValues = x
        .Values = y
        .Format.Shadow.Visible = msoFalse 'This seems to parse, but have no effect
    End With
  End With

End Sub

Answer 3

This gratuitous excess formatting in Mac Excel charts has severely hampered conversion of my many programs to the Mac. Fortunately I've discovered:

ActiveChart.ChartStyle = 2

which applies the default formats without shadows, gradients, and glows. No need to go back and reformat every little detail of every series in the chart.