Forums Neueste Beiträge
 

Problem when trying to set axis title of a chart by VBA

12/08/2009 - 09:31 von Sönke Schreiber | Report spam
Hello,

I am trying to automate creating a chart in Excel by using VBA. The approach
is to create a chart on its own sheet not embedded into another worksheet.
(Actually the entire process is initiated from Word VBA but this should not
be a factor, I think.)

The shematic code is as follows:

Dim oExcel As Object
Dim oWorkbook As Object
Dim oWorksheetDaten As Object
Dim oGrafik As Object
Dim oRange As Object

...

' An das Diagramm herangehen, das danach ein eigenes Datenblatt ist
Set oGrafik = oWorkbook.Charts.Add

' oGrafik.Select

oGrafik.name = "DIAGRAMM"
oGrafik.ChartType = 65

' Datenquelle
spalte = 2
While (spalte <= anz_spalten)

oGrafik.SeriesCollection.NewSeries
oGrafik.SeriesCollection(spalte - 1).XValues = ...
oGrafik.SeriesCollection(spalte - 1).Values = ...
oGrafik.SeriesCollection(spalte - 1).name = ...

spalte = spalte + 1
Wend

...

With oGrafik
.HasTitle = False
.HasLegend = True
.HasDataTable = False
.HasAxis = True
.Legend.Position = -4160 'xlTop
.Legend.Font.name = "Arial"
.Legend.Font.Size = 10
.Legend.Border.LineStyle = -4142 'xlNone
.PlotArea.Border.LineStyle = -4142 'xlNone
.PlotArea.Interior.ColorIndex = 2
End With

' Note: Variables are set before.
With oGrafik.Axes(xlValue)
.MinimumScaleIsAuto = False
.MinimumScale = ug
.MaximumScaleIsAuto = False
.MaximumScale = og
.MinorUnitIsAuto = False
.MinorUnit = schrittweite
.MajorUnitIsAuto = False
.MajorUnit = schrittweite
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

With oGrafik
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Sollanzeige"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Abweichung"
End With

...

The diagram is created as an own sheet and the data source is correct. Also,
the legend is shown correctly. However, the axis scaling is not set and the
same is true for the axis title!

Can anybody help me and explain what is the reason for this behaviour? What
can I do to make it run correctly?

Thank you!

Greetings from Germany,
Soenke Schreiber

P.S.: We are using Excel 2003.
 

Lesen sie die antworten

#1 Andreas Killer
12/08/2009 - 18:00 | Warnen spam
Sönke Schreiber schrieb:

I am trying to automate creating a chart in Excel by using VBA. The approach
is to create a chart on its own sheet not embedded into another worksheet.
(Actually the entire process is initiated from Word VBA but this should not
be a factor, I think.)


Normally not, but you have something crucial overlooked.

With oGrafik.Axes(xlValue)
.MinimumScaleIsAuto = False
.MinimumScale = ug
.MaximumScaleIsAuto = False
.MaximumScale = og
.MinorUnitIsAuto = False
.MinorUnit = schrittweite
.MajorUnitIsAuto = False
.MajorUnit = schrittweite
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With


Here you are using different constants, but they are only in Excel
defined, but not in Word.

I recommend you to place the line "Option Explicit" as the first line
in the code, it makes it more easier to find all the missing constants
like "xlValue", "xlAutomatic", "xlLinear" and many more.

Take a look in the object catalog in Excel by pressing F2 in the VBA
editor, enter the name of the constant in the search field, i.e.
"xlValue" and you can find this:

Const xlValue = 2

Copy this line in front of your code. And go on for any missing constants.

Andreas.

Ähnliche fragen