在使用图表时避免使用激活和选择（Excel） [英] Avoiding the use of Activate and Select when working with charts (Excel)

问题描述

我知道在Excel VBA中使用激活和选择不是最佳做法。我已经看到在处理范围时如何避免它们的参考（例如：链接）。在处理 ChartObjects （或一般范围内的任何其他内容）时，如何避免使用？

I know that using Activate and Select in Excel VBA is not best practice. I've seen references on how to avoid them when dealing with Ranges (example: LINK). How can I avoid them when dealing with ChartObjects (or anything other than Ranges, in general)?

例如，使用激活和选择将修改y轴上的最大值的方法看起来像这样（其中工作）：

For instance, a way to modify the maximum value on the y-axis using Activate and Select would look something like this (which works):

        ActiveSheet.ChartObjects("MyChart").Activate
        ActiveChart.Axes(xlValue).Select
        ActiveChart.Axes(xlValue).MaximumScale = Range("MaxVal").Value

为避免使用激活和选择，我厌倦了声明变量，并与那些工作，但这不工作：

In order to avoid using Activate and Select, I tired to declare variables, and work with those, but that does not work:

     Dim ws As Worksheet
     Set ws = Worksheets("Chart")
     With ws.ChartObjects("MyChart").Axes(xlValue)
        .MaximumScale = Range("MaxVal").Value
     End With

上面的代码运行（即不抛出错误），但轴上的刻度d没有改变我缺少什么？

The code above runs (i.e. does not throw an error), but the scale on the axis does not change. What am I missing?

编辑：使用这个longwinded版本：

Got it to work with this "longwinded" version:

    With Worksheets("Chart").ChartObjects("MyChart").Chart.Axes(xlValue)
        .MaximumScale = Range("MaxVal").Value
    End With

推荐答案

对于您的问题＃1：在处理ChartObjects（或者一般范围以外的其他任何内容）时，如何避免这些问题？，您使用的方法是正确的。您的结论不起作用是由另一个错误引起的。

As for your question #1: How can I avoid them when dealing with ChartObjects (or anything other than Ranges, in general)?, the method you use is correct. Your conclusion that does not work is brought about by the other error.

至于你的问题＃2：我错过了什么？， ChartObject 没有方法 Axes 。
你所谓的longwinded版本实际上是 的方式。

As for your question #2: What am I missing?, a ChartObject does not have a method Axes. What you called the "longwinded" version is actually the way to do it.

PS：唯一可以想到的原因关于非工作代码运行没有错误是错误处理程序，忽略错误。
我得到预期的运行时错误438'：对象不支持此属性或方法。

PS: The only reason I can think about for the non-working code to run with no error is an error handler that ignores the error. I get the expected "Run-time error '438': Object doesn't support this property or method".

这篇关于在使用图表时避免使用激活和选择（Excel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！