Creating Second Chart via Sub Procedure Error

[robinsonie]

Hi,

I have a set of data (date, price) for a number (the actual number
depends on the user input from a previous form) of bonds, and I plot
this, via VBA on a single chart, with multiple series.

I then normalise this data, and then plot the normalised data on
another chart. All in the same workbook. I therefore created a
sub-procedure to create the graphs. Part code is below:

'Previous code reads in data from Db and creates 2 worksheets
'One with standard data, the other with Normalised.
'Then the Code calls the sub to create the charts.
Call MakeGraphs("MultiPriceActual", "BondData", lngRowCheck)
Call MakeGraphs("MultiPriceNormalised", "Normalised", lngRowCheck)

'Sub that creates the chart, and adds the series of data for
'plotting.
Sub MakeGraphs(strChtTab As String, strWks As String, intRef As Long)

Dim cht As Chart
Dim i As Integer, intj As Integer

Set cht = ActiveWorkbook.Charts.Add
cht.ChartType = xlLine
cht.Name = strChtTab

cht.HasLegend = True
cht.Legend.Position = xlLegendPositionTop
cht.HasTitle = False
'more code that does the series adding and formatting.

Basically what happens is the first call works well, the chart is
created, and the series plotted. On the second call, the chartsheet is
created, but not filled with a 'skeleton' chart (it is blank), so when
the .haslengend property is set, the code falls down, as there is no
chart to set the property for.

I am sure i am missing something basic here, and can post more code if
required.

Many Thanks
Ian R

 

[Tushar Mehta]

The only 'basic' thing you are missing is that XL always tries to help
you build the chart. It does this based on the selected cells (or the
current region if a single cell is selected). It then tries to analyze
the contents of the region of interest and decides if X values are
specified, how many series to create, whether to add a legend, etc.

The bottom line is that the result of a default chart is completely
unpredictable. Well, actually, it is completely predictable; just that
one would have to do the same analysis XL did in creating the chart to
figure out what elements exist in the chart.

It is a lot simpler to do one of the following:

Option 1: Select a cell outside the usedrange. Now, XL will definitely
create an empty chart.

Option 2: Delete every series in the default chart created by XL. Then,
add the series of interest one at a time.

Each has its advantages and disadvantages. If I am working in a totally
automated environment, I prefer 1; if my code is invoked through the UI,
I prefer 2.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[robinsonie]

Wow! Thank you Tushar!

Added in a line between the 2 calls that selected a cell, and it
works....

Many Many Thanks.
Ian

 

[Tushar Mehta]

Wow! Thank you Tushar!

Added in a line between the 2 calls that selected a cell, and it
works....

Many Many Thanks.
Ian

You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions