viewing big charts

[inquirer]

Is there any way to create large charts in excel, ie those that exceed the
screen size, and view them by creating scroll bars and scrolling right/left,
up/down ?
Chris

 

[Guest]

Why not just embed the chart in a worksheet instead of creating a chart sheet
and size it manually to suit? If you don't want someone to be able to scroll
beyond the limits of the chart then add the following code to the
ThisWorkbook code module:

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:T50"
End Sub

Change the referenced range (here "A1:T50") to fit the range occupied by the
chart. You will likely have to adjust the row height, column widths and chart
dimensions slightly so that the user can't see any leftover worksheet when
scrolling to the limits of the range.

Regards,
Greg

 

[inquirer]

I want to graph some time series data- daily data going back 10-15 years.
I would like to be able to set the length of the x axis programatically so
that I can produce charts that can be directly compared.
I have just tried your suggestion and it is fine manually but when I put the
manual change into a macro I got
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.98, msoFalse, msoScaleFromTopLeft
which looks as tho it is what I want but I am not sure how to control the
ScaleWidth - what does the 1.98 refer to?

Thanks
Chris

 

[Guest]

Inquirer,

I thought you just wanted a very large chart - bigger than the screen size.
I get the impression you want to dynamically change the width of the chart
proportional to the amount of x-data (time) instead of just changing the
amount of plotted data (in which case the chart's scaling will adjust
automatically to accomodate).

My impression is that you want to be able to compare separate charts with
different amounts of x-data and with their x-axies scaled the same. This
seems strange. Can you not just plot multiple series in the same chart so
that they all share the same x-axis?

Dynamic adjustment of chart size as a function of the amount of data can be
done but would be a bit complicated. Off the top of my head, this would
involve programmatically adjusting the chart width, the plot area width and
the min and max scaling.

The plot area width would change as a function of the amount of x-data while
the chart width would be maintained equal to the plot area width plus a
constant. You would have to specify the min and max scaling. You might then
want to adjust the allowed scroll area to suit the changing dimensions.

To answer your question, "ScaleWidth 1.98" means to change the width of a
shape by 1.98 times the original (i.e. effectively doubling it).

Regards,
Greg

 

[inquirer]

Greg
Thanks for your comments, I have taken them on board and will probably
change my approach as a result.

I have many groups of time series and would like to compare them in dfferent
combinations - plotting them all together on one x axis is too confusing.

I was planning to be able to select combinations for plotting via a vba prog
and display them with the same x scale - say a 3 month period always appears
4 inches wide on the screen. Then the x scale would always seem the same on
screen irrespective of the total time period.

I think I have everything under control except the x scaling bit. I
understand "ScaleWidth 1.98" changes the shape of the original by 1.98 but
a) what is the width of the original shape? b) can I define the width of the
original shape?
Thanks
Chris

 

[Guest]

Inquirer,

I took a shot at it this evening and was NOT successful at getting the plot
area width to change in proportion to the amount of x-data (although I got
close). The problem appears to result from conflict between the parent chart
object and the plot area, as well as to Excel's automatic adjustment of the
plot area size in order to accomodate the x-axis labeling. In other words,
even if you tell Excel to make the plot area a certain size and the chart
object equal to this size plus some constant, it decides to change it
according to some weird formula. Not to say that it is hopeless, just that I
exhausted the time I was willing to devote to it. Although unsuccesful, you
may find the information of value.

For the sake of experimentation, I made the chart object very large so that
adjustment of the plot area would never conflict. I then created a Dynamic
Named Range called "XVals" and another called "YVals" which respectively
contained the x- and y-data. The chart source data referenced these named
ranges (i.e. Sheet1!XVals and Sheet1!YVals). This allowed me to easily
manipulate the amount of data by either expanding the ranges or vice versa.

For the record, the x-data started in cell B5 and ranged down to an
arbitrary degree. The y-data was immediately to the right starting in cell
C5. Named ranges were defined as follows:
XVals: "=OFFSET(Sheet1!$B$5, 0, 0, COUNT(Sheet1!$B:$B), 1)"
YVals: "=Offset(XVals, 0, 1)

If you are not familiar with Dynamic Named Ranges and are interested then I
can explain on request.

The macro code I used was as follows:

Const PtsPerDay As Single = 20
Sub AdjustChart()
Dim ChtObj As ChartObject
Dim Cht As Chart
Dim n As Long
Dim Day_1 As Date, Day_n As Date
Dim Msg As String

Set ChtObj = ActiveSheet.ChartObjects(1)
Set Cht = ChtObj.Chart
n = Range("XVals").Count
Day_1 = Range("XVals")(1)
Day_n = Range("XVals")(n)
With Cht.Axes(xlCategory)
.MinimumScale = Day_1
.MaximumScale = Day_n
End With
Cht.PlotArea.Width = (Day_n - Day_1) * PtsPerDay
Msg = "Should be: " & (Day_n - Day_1) * PtsPerDay & vbCr & _
"Actual plot area width = " & Cht.PlotArea.Width
MsgBox Msg
End Sub

Note that, if done according to my directions, the plot area width should
size in proportion to the amount of x-data except for an offset. The offset
tends to vary, unfortunately. You may wish to pursue this. I have not
developed a stategy for resolving the chart object vs. plot area conflict.
Obviously it is not an option to do this.

Hope this was of some value.

Regards,
Greg

 

[inquirer]

Thanks for all your time and help Greg, I will start with the code you
listed and see if I can get the result I want. I have come across Excel's
changing things according to weird formula before and sometimes it is
possible to work with it even tho I don't completely understand what it is
doing.
Thanks again
Chris

 

[Jon Peltier]

Chris -

I think you should look at this article I wrote last year:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

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

 

[inquirer]

Thanks Jon that's a great article.

I guess the problem I have is that if I have daily data for 10 years and
plot this on a standard excel chart, the x axis is compressed and some of
the detail in the pattern is difficult to see. I want to stretch the x axis
out to see individual points clearly. This means having an x axis that may
be 5-6 screenwidths in length and I would like to be able to scroll back and
forth along this length. I was looking for an easy or simple way to be able
to set the actual length of the x axis.
Chris