Turn off marker shadow on vba-generated Excel plots

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

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

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.