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

[Sönke Schreiber]

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.

 

[Sönke Schreiber]

Hello,

The Problem has been solved. If creating Excel object from Word it is not
allowed to use Excel constants. Instead it is necessary to replace e.g.
xlValue by the actual value 2. After eliminating the Excel constants the axes
were displayed correctly.

Kind regards,
Sönke Schreiber

 

[Dave Peterson]

If you set a reference to excel in your project (Tools|References), then you'll
be able to use all the excel vba's constants.

But I think you're doing better by not using this reference. You're using late
binding and lots of times, this will avoid problems when recipients have
different versions of office.