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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Question A:
Check whether your Settings|ControlPanel|RegionalSettings|Number: Measurement Units match with the units in your code. If not, _try_ with changing one. Should this be the cause, and you will distribute your workbook, you will have to make the check in VBA.

Question B: This article might relate altough it is about a picture:
XL97: Size of Scaled Picture Is Incorrect When Cropped
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|ControlPanel|System: General

If you have Excel 95 (aka 7.0)

Q111249 - XL: Embedded Chart Shrinks After Print or Print Preview

If you have Excel 97
Q186931 - XL97: Chart Is Reduced to a Small Size After Formatting Legend 

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: 
For other languages, access the a.m. URL then switch to the appropriate country using the listbox in the upper left corner before bookmarking.  
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

has4Author Commented:
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.
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(0.748031496062992)

has4Author Commented:
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???
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.
has4Author Commented:
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.Add((xpos - 1) * (xsize + 20) + 5, (ypos - 1) * (ysize + 20) + 10, xsize, ysize).Select
    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.AutoScaleFont = false ActiveChart.ChartTitle.Characters.Text = s
ActiveChart.ChartTitle.Font.Size = 13
ActiveChart.ChartTitle.Font.Name = Font
ActiveChart.ChartTitle.left = 12
ActiveChart.ChartTitle.Top = 3
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Top = 2
    ActiveChart.Legend.left = 180
    ActiveChart.Legend.Height = 25
    ActiveChart.Legend.Font.Size = 7
    ActiveChart.Legend.Width = 60
    ActiveChart.PlotArea.Top = 25
    ActiveChart.PlotArea.Height = 50
    ActiveChart.PlotArea.left = 5
    ActiveChart.PlotArea.Width = 170
has4Author Commented:
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.
has4Author Commented:
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??
I think the best solution is you saving the whole thread as html, then delete the question, which will refund your points.
Then re-ask the question again, it will get more attention this way:
- Edit the text to reflect the latest knowledge/changes.
- Include a reference the result of the MSKB search (=zilch) and to this question Q.10236157 (only way to access a deleted question.
- Paste the new macro code

I think 150-200 points are enough, otherwise you will attract too many point oriented persons.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.Count
    Set c = ActiveSheet.ChartObjects(i).Chart
    c.PlotArea.Top = 25
    c.PlotArea.Height = 50
    c.PlotArea.Left = 5
    c.PlotArea.Width = 170
  Next i
End Sub

Hope it helps.
has4Author Commented:
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!
has4, although unexpected, thanks.

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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.