Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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 "Greg Wilson" wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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" wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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 "Greg Wilson" wrote in message ... 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" wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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" wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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 "Greg Wilson" wrote in message ... 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" wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
Chris -
I think you should look at this article I wrote last year: http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ inquirer wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
viewing big charts
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 "Jon Peltier" wrote in message ... Chris - I think you should look at this article I wrote last year: http://pubs.logicalexpressions.com/P...cle.asp?ID=246 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ inquirer wrote: 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 "Greg Wilson" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Viewing Charts in Excel 2007 | Charts and Charting in Excel | |||
Viewing Charts | Setting up and Configuration of Excel | |||
Unlock for Viewing | Excel Worksheet Functions | |||
Viewing in web | Charts and Charting in Excel | |||
Viewing Pictures | Excel Discussion (Misc queries) |