Sunday, June 23, 2019
Avoiding the use of Activate and Select when working with charts (Excel)
Answer
Answer
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)?
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
The code above runs (i.e. does not throw an error), but the scale on the axis does not change. What am I missing?
EDIT: Got it to work with this "longwinded" version:
With Worksheets("Chart").ChartObjects("MyChart").Chart.Axes(xlValue)
.MaximumScale = Range("MaxVal").Value
End With
Answer
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.
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: 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".
plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV
In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...
-
This question attempts to collect the few pearls among the dozens of bad C++ books that are published every year. Unlike many other programm...
-
I need to do the following: My current address looks like: https://www.domain.com I want to redirect with htaccess: www.domain.com TO https:...
-
using namespace std; So far in my computer science courses, this is all we have been told to do. Not only that, but it's all tha...
No comments:
Post a Comment