在VBA生成的Excel图上关闭标记阴影 [英] Turn off marker shadow on vba-generated Excel plots

问题描述

我正在将一些用于生成Excel中散点图的代码从Win 7/Excel 2010移植到OS X/Excel2011.在Mac上，数据点带有阴影.我不想要阴影，我也想不出如何摆脱阴影.

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.

使用此工作表(它在A1单元格中只有随机数: B6如果您不想下载启用宏的工作表，则以下代码可以正常运行，但会产生带有阴影的数据点:

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. 如何修改此代码以消除阴影，并且

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

这段代码如何运行，但是将SeriesCollection(1).Format.Shadow.Visible设置为msoFalse可以运行而没有任何明显的效果?

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.

推荐答案

Excel中产生轻微3D效果的几种默认图表样式也具有轻微的阴影，如前所述.

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

在我的Excel中(在Windows 7中)，默认图表样式为2，因此不会出现阴影或3D效果.我怀疑在Mac上，默认图表样式会有所不同.

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
  .....

在Excel中，ChartStyle设置可以修改图表外观的所有方面，包括标记的外观. MarkStyle唯一设置的是Marker的形状.更改ChartStyle时，将覆盖Marker的所有其他外观方面.

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.

编辑

以上评论基本上还是正确的，但是我找到了一种关闭阴影的方法.像使用Excel进行许多操作一样，它并不像您想的那么容易.如果通过代码完成设置阴影的可见性属性(无论出于何种原因)无效，因此您需要将阴影类型设置为无阴影".

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

再次编辑

实际上，msoShadow30是一种内部阴影"样式，根据您的标记样式，它可能看起来很奇怪. msoShadow41是我能够找到的最接近无阴影"的东西.它实际上是下面阴影的代码，但是默认情况下，它太微弱以至于看不到.如果确实出现，则可以随时更改颜色以使其消失.

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.

或更妙的是，将透明度设置为1(完全透明):

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

.Format.Shadow.Transparency = 1.0 'Fully transparent

这篇关于在VBA生成的Excel图上关闭标记阴影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！