VBA help on one of Jon Peltier's Charting

S

Shi

Jon's technique helps me a lot to plot a stock's HLC chart
manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub
 
S

Shi

Jon,

Thanks a lot for your time. All what I want to do is to
plot exactly the same HLC chart as you sampled on your web
site, but with a macro.

The only difference is I use the "Open" price line as my
Index line on HLC chart.

I can do this easily by following your suggested steps
manually, but I can't do it with a macro.

Can you help, please?
Thank you,


-----Original Message-----
Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What purpose does
this line serve? The effect is to remove reference to the X Values range
in the series formula, which means the series will just use 1, 2, 3,
etc. Interestingly, if you do this to the Y values, it does not clear
that piece of the formula. Instead, it converts from a range containing
values, to an array of these values {1,2,3,4}. Still, I imagine it's a
way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro just
doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,


-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very much
for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.




-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will

work if you put

Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon's technique helps me a lot to plot a stock's HLC

chart

manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close" data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =

xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,

SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.
Click to expand...
Click to expand...

.
Click to expand...
 
J

Jon Peltier

Shi -

This example assumes
- Data in columns, L-R, of Date, Open, High, Low, Close
- Dates are real numerical dates

The only error checking is whether a range of cells is selected.

'' START CODE ------------------------------------------------
Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart

If TypeName(Selection) <> "Range" Then
MsgBox "Select a range of cells containing data," & vbCrLf _
& "in order: Date, Open, High. Low, Close", vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData = rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(, 3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100, 375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub
'' END CODE --------------------------------------------------------

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Jon,

Thanks a lot for your time. All what I want to do is to
plot exactly the same HLC chart as you sampled on your web
site, but with a macro.

The only difference is I use the "Open" price line as my
Index line on HLC chart.

I can do this easily by following your suggested steps
manually, but I can't do it with a macro.

Can you help, please?
Thank you,



-----Original Message-----
Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What
Click to expand...

purpose does
this line serve? The effect is to remove reference to the
Click to expand...

X Values range
in the series formula, which means the series will just
Click to expand...

use 1, 2, 3,
etc. Interestingly, if you do this to the Y values, it
Click to expand...

does not clear
that piece of the formula. Instead, it converts from a
Click to expand...

range containing
values, to an array of these values {1,2,3,4}. Still, I
Click to expand...

imagine it's a
way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:

Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro
Click to expand...
just
doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,




-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very
Click to expand...
much
for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.





-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will

work if you put


Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:



Jon's technique helps me a lot to plot a stock's HLC

chart


manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close"
Click to expand...
data
located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the
Click to expand...
problem
with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =


xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,


SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.
Click to expand...

.
Click to expand...
Click to expand...
 
S

Shi

Jon,

Thanks a lot for your great solution. I almost got the
final result by running your macro. (Maybe I missed one or
two steps somewhere???)

So far, I got HLC chart, but without OpenLine. I think
this was due to my chart was not in a CategoryType. How do
you add these lines inside your macro?

With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale


Thanks for your advice,

-----Original Message-----
Shi -

This example assumes
- Data in columns, L-R, of Date, Open, High, Low, Close
- Dates are real numerical dates

The only error checking is whether a range of cells is selected.

'' START CODE -------------------------------------------- ----
Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart

If TypeName(Selection) <> "Range" Then
MsgBox "Select a range of cells containing data," & vbCrLf _
& "in order: Date, Open, High. Low, Close", vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData = rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(, 3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100, 375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub
'' END CODE ---------------------------------------------- ----------

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Jon,

Thanks a lot for your time. All what I want to do is to
plot exactly the same HLC chart as you sampled on your web
site, but with a macro.

The only difference is I use the "Open" price line as my
Index line on HLC chart.

I can do this easily by following your suggested steps
manually, but I can't do it with a macro.

Can you help, please?
Thank you,



-----Original Message-----
Shi -

1. Do you need to select the plot area? Probably not.

2. It's dangerous to be setting the XValues to "". What
Click to expand...

purpose does
this line serve? The effect is to remove reference to
Click to expand...
the

X Values range
in the series formula, which means the series will just
Click to expand...

use 1, 2, 3,
etc. Interestingly, if you do this to the Y values, it
Click to expand...

does not clear
that piece of the formula. Instead, it converts from a
Click to expand...

range containing
values, to an array of these values {1,2,3,4}. Still, I
Click to expand...

imagine it's a
way to create problems.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:


Jon,

The true problem is with these two lines:
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""

If I run both macros without these above two lines,
everthing seems fine.

If I add these two lines inside my macro, this macro
just

doesn't know how to select "SeriesCollection(4)".

Would you please give me an advice?

Thanks for your help,




-----Original Message-----
Jon,

You are the Master of Excel Charting. Thank you very
much

for your help here.

I just tried your solution, I still couldn't get
my "OpenLine" or your "Index line" working with a macro.

Can you offer me more help? Thanks.





-----Original Message-----
Shi -

Sometimes the macro recorder gets confused. This will

work if you put


Chart Type after Set Source Data:

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.ChartType = xlStockHLC
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="Chart1"
[etc.]

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Shi wrote:



Jon's technique helps me a lot to plot a stock's HLC

chart


manually. But if I record it as a macro based on his
recommended steps, it doesn't work anymore. I have a
standard Data sheet, the
stock's "Date", "Open", "High", "Low" and "Close"
data

located at "A, B, C, D, E" columns respectively.

The following "HLC20 macro" works fine, but "OpenLine
macro" doesn't. Can anyone find out what's the
problem

with "OpenLine macro"? Thanks.

Sub HLC20()
Range("A1:A20,C1:E20").Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlStockHLC
ActiveChart.SetSourceData Source:=Sheets("Data
Sheet").Range("A1:A20,C1:E20") _
, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory,

xlPrimary).CategoryType =


xlCategoryScale
End Sub

Sub OpenLine()
Sheets("Data Sheet").Select
Range("A1:B20").Select
Selection.Copy
Sheets("Chart1").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.Paste

Rowcol:=xlColumns,


SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False,
NewSeries:=True
ActiveChart.SeriesCollection(4).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(4).ChartType =
xlXYScatterSmooth
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = ""
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).AxisGroup = 1
End Sub

.


.


.
Click to expand...
Click to expand...

.
Click to expand...
 
J

Jon Peltier

Shi -

If your dates are true numerical dates, and you make a stock chart,
Excel will make a time scale axis. This works fine for both the XY
series (Open Line) and the stock chart series.

If you make the X axis into a category type, the Open Line XY series
still uses the dates as input. Your categories for the stock data will
be numbers like 1, 2, 3, etc., while the X data for the Open Line series
will be numbers in the 39 thousands, which is what dates look like. The
two series will not want to line up in this case.

Follow the instructions about the data arrangement
(Date-Open-High-Low-Close), and the date configuration (real numerical
dates), then try again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
S

Shi

Jon,

Thanks for your time. How do you configure the Date as the
Real Numerical Date?
 
S

Shi

Jon,

Here were the real data I run your macro, I was still
unable to get HLC chart with an Open line.

Date,Open,High,Low,Close
38174,28.32,28.33,27.94,28.02
38170,28.62,28.68,28.40,28.57
38169,28.70,28.84,28.26,28.63
38168,28.57,28.80,28.39,28.56
38167,28.18,28.58,28.18,28.50
38166,28.60,28.75,28.17,28.28
38163,28.48,28.63,28.25,28.57
38162,28.48,28.65,28.36,28.39
38161,28.20,28.38,28.00,28.30
38160,28.15,28.35,27.81,28.29
38159,28.22,28.66,28.12,28.35
38156,27.77,28.50,27.70,28.35
38155,27.31,27.92,27.29,27.77
38154,27.34,27.50,27.15,27.32
38153,26.99,27.60,26.97,27.41
38152,26.55,26.90,26.53,26.90
38148,26.38,26.79,26.38,26.77
38147,26.40,26.65,26.40,26.47
38146,26.28,26.65,26.24,26.60

I had Date-Open-High-Low-Close at L-M-N-O-P columns.
What I did wrong? I have no idea at all.

Please help,
 
S

Shi

By the way, I forgot to mention, after I changed the Date
format to a "General" or "Numeric" format, the original
macro was stopped at line: .ChartType = xlStockHLC

What should I do next?
 
J

Jon Peltier

In my sample table, I had no header labels. The code below adjusts for
this. You just need to format your dates as any date format.

Sub StockHLC_OpenLine()
Dim rData As Range
Dim rHLC As Range
Dim rOpen As Range
Dim rXVals As Range
Dim cHLCO As Chart
Dim iPts As Integer
Dim iRows As Integer

If TypeName(Selection) <> "Range" Then
MsgBox "Select a range of cells containing data," & vbCrLf _
& "in order: Date, Open, High. Low, Close", vbExclamation, _
"Invalid selection"
Exit Sub
End If

Set rData = Selection
If rData.Columns.Count < 5 Then Set rData = rData.CurrentRegion

Set rXVals = rData.Columns(1)
Set rHLC = Union(rXVals, rData.Columns(3).Resize(, 3))
Set rOpen = rData.Columns(2)

Set cHLCO = ActiveSheet.ChartObjects.Add(100, 100, 375, 225).Chart
With cHLCO
.SetSourceData Source:=rHLC, PlotBy:=xlColumns
.ChartType = xlStockHLC
iRows = rOpen.Rows.Count
iPts = .SeriesCollection(1).Points.Count
If iRows > iPts Then
Set rOpen = rOpen.Offset(1).Resize(iPts)
Set rXVals = rXVals.Offset(1).Resize(iPts)
End If
Set rOpen = rOpen.Resize(.SeriesCollection(1).Points.Count)
With .SeriesCollection.NewSeries
.Values = rOpen
.ChartType = xlXYScatterLinesNoMarkers
.XValues = rXVals
.Name = "Open"
End With
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
S

Shi

Jon,

I just saw your new post, it works like wonder.
Thank you for being here to help me.

You are really a good man.
 
J

Jon Peltier

Shi -

Glad to help.

- Jon
Jon,

I just saw your new post, it works like wonder.
Thank you for being here to help me.

You are really a good man.





below adjusts for


data," & vbCrLf _


vbExclamation, _


375, 225).Chart
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to change the row number in macro code? 1
"Lines on 2 Axes" fails on Excel 2007 1
Floating Chart? 1
add a chart in a Add-In 1
Trying to Learn Code to Copy Charts 2
Help on adding the second trendline in a "lines on 2 axes" Chart by using VBA 1
Macro to generate new graph with variable range 1
Run-time error `91': Object variable . . . not set 2

Top