with selection speed

[Ian Mangelsdorf]

I have a series of embed charts in a worksheet, all of which need to
have various changes made. I am currently using code as follows
(extract)

'Sets the series point markers
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlPlus
End With

'sets the depth axis properties
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = MinDepth
.MaximumScale = MaxDepth
.ReversePlotOrder = True
.CrossesAt = MinDepth
End With


In varoious articles on this news group I have seen it mentioned that
selecting an object slows the code down and isnt nesacarally the best
option.

I am curious as to what would be the best practice for this situation

 

[Vasant Nanavati]

Without getting into the particulars of your code, I'll just say that you
rarely need to select any object in Excel. For example:

Workbooks("Book1.xls").Activate
Worksheets("Sheet3").Select
Range("B5").Select
Selection.Copy
Worksheets("Sheet5").Select
Range("C6").Select
ActiveSheet.Paste

is equivalent to:

Workbooks("Book1.xls").Worksheets("Sheet3").Range("B5").Copy Destination:= _
Workbooks("Book1.xls").Worksheets("Sheet5").Range("C6")

If Book1.xls is the active workbook you could omit the reference to it. Then
the code would simply be:

Worksheets("Sheet3").Range("B5").Copy Destination:= _
Worksheets("Sheet5").Range("C6")

No sheets or ranges have to be activated or selected for this to work.

 

[crossplatform]

May I know what's wrong with this code:
Worksheets("sheet1").ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Value = "=Sheet1!R1C1:R20C1"

and this works perfectly:
Worksheets("sheet1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C1:R20C1"

 

[Bob Phillips]

User error.

Worksheets("sheet1").ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Value = "=Sheet1!R1C1:R20C1"

should be

Worksheets("sheet1").ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Values = "=Sheet1!R1C1:R20C1"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Vasant Nanavati]

Good catch, Bob! I stared at that for 5 minutes and couldn't see the error.

Regards,

Vasant.

User error.

Worksheets("sheet1").ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Value = "=Sheet1!R1C1:R20C1"

should be

Worksheets("sheet1").ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Values = "=Sheet1!R1C1:R20C1"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

I know the feeling, thought it was the .Chart at first, until I tried it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Good catch, Bob! I stared at that for 5 minutes and couldn't see the error.

Regards,

Vasant.