Size of chart elements screws up in Excel 97
I have seen this problem in all earlier versions of Excel, even on Macs! The problem concerns sizing of the plot area, legend and titles.
- When I create a chart with a macro using certain dimensions for plot area, legend etc., the chart first looks fine. Then after actions like print preview, page break view, rezooming, the whole chart looks totally busted. Most of the times, the plot area collapses to a tiny bar within the chart area. This problem occurs unpredictably. When I run the macro again now, the chart is still disfigured. To get the original result I have to use much smaller dimensions: e.g. what was plotarea.width=200 must now be =80.
(I suspect some problem with switching between inches and cm here: my default settings are cm, and after these incidents the sizes in the page setup menu, e.g., are suddenly in inches.)
- When I save a chart where I have changed dimensions of the plot area, etc. manually, suddenly a couple of changes I've just made are reversed and saved wrong. Very bothersome. It seems just impossible to get 10 charts on a sheet to look exactly the same with Excel.
- When I run a small macro that changes plotarea size, the result depends on whether the marked chart is fully displayed or if parts of it are scrolled out of the current view. If parts of the chart are outside the view, the resulting plot area is too small.
- When I create a chart with a macro using certain dimensions for plot area, legend etc., the chart first looks fine. Then after actions like print preview, page break view, rezooming, the whole chart looks totally busted. Most of the times, the plot area collapses to a tiny bar within the chart area. This problem occurs unpredictably. When I run the macro again now, the chart is still disfigured. To get the original result I have to use much smaller dimensions: e.g. what was plotarea.width=200 must now be =80.
(I suspect some problem with switching between inches and cm here: my default settings are cm, and after these incidents the sizes in the page setup menu, e.g., are suddenly in inches.)
- When I save a chart where I have changed dimensions of the plot area, etc. manually, suddenly a couple of changes I've just made are reversed and saved wrong. Very bothersome. It seems just impossible to get 10 charts on a sheet to look exactly the same with Excel.
- When I run a small macro that changes plotarea size, the result depends on whether the marked chart is fully displayed or if parts of it are scrolled out of the current view. If parts of the chart are outside the view, the resulting plot area is too small.
If none helps:
a) Try this little trick:
StartButton|Run excel /regserver (there is a blank before the switch)
This will rewrite all registry keys for Excel
b) Perhaps you have a virus. Make a check using a recent signature list.
If still nothing:
c) Which Excel version and service release do you have ? If unsure: Help|About
d) Which operating system and patch level do you have ? If unsure: StartButton|Settings|Contr
a) Try this little trick:
StartButton|Run excel /regserver (there is a blank before the switch)
This will rewrite all registry keys for Excel
b) Perhaps you have a virus. Make a check using a recent signature list.
If still nothing:
c) Which Excel version and service release do you have ? If unsure: Help|About
d) Which operating system and patch level do you have ? If unsure: StartButton|Settings|Contr
If you have Excel 95 (aka 7.0)
http://support.microsoft.com/support/kb/articles/Q142/5/92.asp
Q111249 - XL: Embedded Chart Shrinks After Print or Print Preview http://support.microsoft.com/support/kb/articles/Q111/2/49.asp
If you have Excel 97
Q186931 - XL97: Chart Is Reduced to a Small Size After Formatting Legend
http://support.microsoft.com/support/kb/articles/q186/9/31.asp
This search is endless. Perhaps you are luckier as you can dismiss at once what is not applicable. For persons using the english version bookmark this URL:
http://search.microsoft.com/us/SearchMS.asp
For other languages, access the a.m. URL then switch to the appropriate country using the listbox in the upper left corner before bookmarking.
http://support.microsoft.com/support/kb/articles/Q142/5/92.asp
Q111249 - XL: Embedded Chart Shrinks After Print or Print Preview http://support.microsoft.com/support/kb/articles/Q111/2/49.asp
If you have Excel 97
Q186931 - XL97: Chart Is Reduced to a Small Size After Formatting Legend
http://support.microsoft.com/support/kb/articles/q186/9/31.asp
This search is endless. Perhaps you are luckier as you can dismiss at once what is not applicable. For persons using the english version bookmark this URL:
http://search.microsoft.com/us/SearchMS.asp
For other languages, access the a.m. URL then switch to the appropriate country using the listbox in the upper left corner before bookmarking.
ASKER
Cri, thanks for your help so far.
I have checked the regional settings: they are US. However, the problem persists on German systems equally. I don't know what you meant by "unit in your code" anyway. As far as I remember you don't have to change the numbers in your code to get the same result whether you are programming on a US or metric system.
Those Microsoft bug "helps" keep disappointing everybody. They are everything but helpful.
Running "Excel /regserver" didn't make change. I don't have any viruses. I run Excel 97 SR-2 on Windows 98 4.10.1998, both are German versions. I'd like to remind y'all that I have seen this bug in earlier versions of Excel and on the Macintosh. It must be a low-level bug in the Excel kernel. I tried to rule out certain parts of the macro code to be responsible. But leaving out legend or title formatting didn't help.
I have checked the regional settings: they are US. However, the problem persists on German systems equally. I don't know what you meant by "unit in your code" anyway. As far as I remember you don't have to change the numbers in your code to get the same result whether you are programming on a US or metric system.
Those Microsoft bug "helps" keep disappointing everybody. They are everything but helpful.
Running "Excel /regserver" didn't make change. I don't have any viruses. I run Excel 97 SR-2 on Windows 98 4.10.1998, both are German versions. I'd like to remind y'all that I have seen this bug in earlier versions of Excel and on the Macintosh. It must be a low-level bug in the Excel kernel. I tried to rule out certain parts of the macro code to be responsible. But leaving out legend or title formatting didn't help.
The articles are not so bad, and nobody ever accused me of being a Microsoft fan. Retrieving them is tedious, although the search engine is quite good, there are too many similar or related ones.
As you have to read a lot diagonaly, perhaps you are better off searching in the german MSKB (this is why I left the number before the title and the short instruction how to switch).
As for your problem: Must be either a problem in the german version or in a chart type I never use. Which ones do you use ?
As for the units: Not so sure here. I use metric system and nonetheless Excel translates all to inches, see the recorded snippet:
..LeftMargin = Application.InchesToPoints
As you have to read a lot diagonaly, perhaps you are better off searching in the german MSKB (this is why I left the number before the title and the short instruction how to switch).
As for your problem: Must be either a problem in the german version or in a chart type I never use. Which ones do you use ?
As for the units: Not so sure here. I use metric system and nonetheless Excel translates all to inches, see the recorded snippet:
..LeftMargin = Application.InchesToPoints
ASKER
Cri, I really looked into all the articles with "chart" as a keyword. None of them really made my day.
My problem is not confined to the German version. First time I ever saw it was on the American version of Excel 6 on the Mac (the version where macros were still stored in a sheet of the workbook). As I told you, I think this bugs has been inherited from ancient times.
Concerning units: you are right about page setup commands (leftmargin for example). They are measured in inches. But the units for chart elements are independent of regional setting for measures (e.g. I use about 250 for the width of the chart objects, they have half the width of one page then. This is the same whether you run a metric or US system).
Any more ideas anyone, please???
My problem is not confined to the German version. First time I ever saw it was on the American version of Excel 6 on the Mac (the version where macros were still stored in a sheet of the workbook). As I told you, I think this bugs has been inherited from ancient times.
Concerning units: you are right about page setup commands (leftmargin for example). They are measured in inches. But the units for chart elements are independent of regional setting for measures (e.g. I use about 250 for the width of the chart objects, they have half the width of one page then. This is the same whether you run a metric or US system).
Any more ideas anyone, please???
As a.m. please indicate which charts are affected, I never saw this and I (have to) work with Excel since v. 3.0 but some chart types I do not use at all.
Additionally, please paste the VBA/Excel code which concerns the creation/sizing of the charts here, perhaps you are using something which is not supported for embedded charts.
PS: Hope I did not offend you by refering you to the german MSKB.
Additionally, please paste the VBA/Excel code which concerns the creation/sizing of the charts here, perhaps you are using something which is not supported for embedded charts.
PS: Hope I did not offend you by refering you to the german MSKB.
ASKER
Here is some code, cri (I omitted irrelevant lines). If you'd prefer, I can e-mail you the whole xls with some instruction on how to reproduce the malfunction. The chart type is xyScatterLines.
[...]
ActiveSheet.ChartObjects.A
ActiveChart.ChartWizard Source:=Range(xrange), Gallery:=xlColumn, _
Format:=1, PlotBy:=xlColumns, CategoryLabels:=0, SeriesLabels _
:=0, HasLegend:=0, Title:="sda", CategoryTitle:="Time (min)", _
ValueTitle:="f", ExtraTitle:=""
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.ChartTitle.Aut
ActiveChart.ChartTitle.Fon
ActiveChart.ChartTitle.Fon
ActiveChart.ChartTitle.lef
ActiveChart.ChartTitle.Top
[...]
ActiveChart.HasLegend = True
ActiveChart.Legend.Top = 2
ActiveChart.Legend.left = 180
ActiveChart.Legend.Height = 25
ActiveChart.Legend.Font.Si
ActiveChart.Legend.Width = 60
[...]
ActiveChart.PlotArea.Top = 25
ActiveChart.PlotArea.Heigh
ActiveChart.PlotArea.left = 5
ActiveChart.PlotArea.Width
[...]
[...]
ActiveSheet.ChartObjects.A
ActiveChart.ChartWizard Source:=Range(xrange), Gallery:=xlColumn, _
Format:=1, PlotBy:=xlColumns, CategoryLabels:=0, SeriesLabels _
:=0, HasLegend:=0, Title:="sda", CategoryTitle:="Time (min)", _
ValueTitle:="f", ExtraTitle:=""
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.ChartTitle.Aut
ActiveChart.ChartTitle.Fon
ActiveChart.ChartTitle.Fon
ActiveChart.ChartTitle.lef
ActiveChart.ChartTitle.Top
[...]
ActiveChart.HasLegend = True
ActiveChart.Legend.Top = 2
ActiveChart.Legend.left = 180
ActiveChart.Legend.Height = 25
ActiveChart.Legend.Font.Si
ActiveChart.Legend.Width = 60
[...]
ActiveChart.PlotArea.Top = 25
ActiveChart.PlotArea.Heigh
ActiveChart.PlotArea.left = 5
ActiveChart.PlotArea.Width
[...]
ASKER
Adjusted points to 400
Off the cuff, have to do this at home:
a) I would define the ChartType directly with " Gallery:=xlXYScatterLines " and delete the redundant ChartType definition.
b) Why AutoScaleFont = false ? I _think_ this will cause problems when zooming.
c) I managed to make errors which did not trigger the error dialog, they are simply ignored, resulting in mixture of macro and default values.
d) If you do _not_ distribute the workbook: Consider defining as much as possible of your XY Scatter as an user defined chart type. This will be saved in xlusrgal.xls, unless otherwise specified you will find this file in \Office directory.
a) I would define the ChartType directly with " Gallery:=xlXYScatterLines " and delete the redundant ChartType definition.
b) Why AutoScaleFont = false ? I _think_ this will cause problems when zooming.
c) I managed to make errors which did not trigger the error dialog, they are simply ignored, resulting in mixture of macro and default values.
d) If you do _not_ distribute the workbook: Consider defining as much as possible of your XY Scatter as an user defined chart type. This will be saved in xlusrgal.xls, unless otherwise specified you will find this file in \Office directory.
ASKER
Cri, I tried putting all the formatting into a user defined chart. It helped insofar as the charts looked more uniform the first time I ran the macro (I have about 16 charts on a worksheet). However, the same problems occur as before when I do some manipulations like zooming, print preview. In some charts the plot area collapses when I zoom to 25%, e.g. Sometimes only those charts are effected that are on the left or on the right side (I have them arranged in two columns), or charts that are at a page break line. Maybe I should learn to use another spreadsheet program soon??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Maybe there is a sence to write simple VBA macros, that will resize your charts - run it every time, when charts became busted. Sample below - only for plotarea, but you can make the same with legend etc.
Sub rechart()
Dim c As Chart
For i = 1 To ActiveSheet.ChartObjects.C
Set c = ActiveSheet.ChartObjects(i
c.PlotArea.Top = 25
c.PlotArea.Height = 50
c.PlotArea.Left = 5
c.PlotArea.Width = 170
Next i
End Sub
Hope it helps.
Sub rechart()
Dim c As Chart
For i = 1 To ActiveSheet.ChartObjects.C
Set c = ActiveSheet.ChartObjects(i
c.PlotArea.Top = 25
c.PlotArea.Height = 50
c.PlotArea.Left = 5
c.PlotArea.Width = 170
Next i
End Sub
Hope it helps.
ASKER
I think I can live with the improvements achieved by your suggestions and vboukhar's help. Thanks for your careful analysis.
has4, it's a wise solution!
Cri, my best congratulation with your third position in MSOffice extert's list!
I'm so sorry, but I was too busy for E-E :-( I tried to sell myself :-) and I'm waiting now for the results.
Good luck!
Cri, my best congratulation with your third position in MSOffice extert's list!
I'm so sorry, but I was too busy for E-E :-( I tried to sell myself :-) and I'm waiting now for the results.
Good luck!
has4, although unexpected, thanks.
vboukhar, thanks too, but do you think this is fair ? And, in which timezone are you living now ?
vboukhar, thanks too, but do you think this is fair ? And, in which timezone are you living now ?
cri, I think it's fair - time spent here and efforts must be appreciated, and I'm sure you really help to E-E visitors. IMHO, points are measure of help, not skills and knowedge only. I wish you get T-shirt in a nearest future!
I'm living in GMT+3 timezone yet (Moscow), but I try to change this situation :-)
Has4, sorry for off-topic, but I miss E-E discussions last time.
I'm living in GMT+3 timezone yet (Moscow), but I try to change this situation :-)
Has4, sorry for off-topic, but I miss E-E discussions last time.
vboukhar, thanks. Yes, it has been quiet lately and the backlog and ask&abandon is growing steadly. If only E-E would implement some of the suggested measures instead of merely repainting the site.
Check whether your Settings|ControlPanel|Regi
http://support.microsoft.com/support/serviceware/office/mso40/e9j9ym2zo.asp
Question B: This article might relate altough it is about a picture:
XL97: Size of Scaled Picture Is Incorrect When Cropped
http://support.microsoft.com/support/kb/articles/q174/1/95.asp