Avoiding the use of Activate and Select when working with charts (Excel)

Viewed 5386

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
2 Answers
Related