with selection speed

  • Thread starter Thread starter Ian Mangelsdorf
  • Start date Start date
I

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
 
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.
 
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"
 
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)
 
Good catch, Bob! I stared at that for 5 minutes and couldn't see the error.

Regards,

Vasant.

Bob Phillips said:
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)
Click to expand...
 
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)

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

Regards,

Vasant.
Click to expand...
 
Back
Top