2003 to 2007

[chandrashekar]

Hi,

I have a code in Office 2003 which is working fine, but if I run same in Office 2007 code is not working.

Is there any way to convert code to 2007.

Thanks in advance.

Thanks,

Chandra Shekar B

 

[RoryA]

We'd need more detail than that. Most code is compatible but, for example, Application.Filesearch is not.

 

[Andrew Poulsom]

What's the code and in what way isn't it working?

 

[chandrashekar]

Hi,

This below code is not working in 2007, but its working in 2003 fine. May I know can we do any betterment to the code. Which can run in any Office.

Sub chrt_col()
Dim ws As Worksheet
Dim chrtrng As Range
Dim myChart As Chart
Dim rng As Range
ThisWorkbook.Worksheets("Analysis").Select
ThisWorkbook.Worksheets("Analysis").Range("B4").CurrentRegion.Select
Set rng = Selection
cl = rng.Columns.Count - 1
rw = rng.Rows.Count + 1
chtrng1 = "B4" & ":" & "F" & rw
Set chrtrng = ThisWorkbook.Worksheets("Analysis").Range(chtrng1)
Set myChart = Charts.Add
Set myChart = myChart.Location(xlLocationAsObject, "Analysis")
With myChart
.SetSourceData chrtrng, xlColumns
.HasTitle = True
.ChartTitle.Caption = "Change"
.ChartType = xlColumnClustered
End With


With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "Sts"
.HasMajorGridlines = False
.HasMinorGridlines = False
'.MajorTickMark = xlTickMarkNone
End With

With ActiveChart.Axes(xlCategory).AxisTitle
.Text = "Sts"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
End With
With ActiveChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Vol"
.HasMajorGridlines = False
.HasMinorGridlines = False
.MinimumScale = 0
'.MaximumScale = 10
.MaximumScaleIsAuto = True
.MajorUnit = 2
.MinorUnit = 0.4
End With

With ActiveChart.Axes(xlValue).AxisTitle
.Text = "Vol"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlUpward
End With
With ActiveChart.ChartTitle
.Text = "Change"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
End With

With ActiveChart.PlotArea
.Interior.ColorIndex = xlNone
.Border.LineStyle = xlNone
.Top = 20
.Height = 161
.Width = 325
End With

With ActiveChart
.HasDataTable = True
.HasLegend = False
End With

With ActiveChart.DataTable
.ShowLegendKey = True
End With

With ActiveChart.SeriesCollection(1)
.Border.LineStyle = xlAutomatic
.Interior.ColorIndex = 39
End With

With ActiveChart.SeriesCollection(2)
.Border.LineStyle = xlAutomatic
.Interior.ColorIndex = 4
End With

With ActiveChart.SeriesCollection(3)
.Border.LineStyle = xlAutomatic
.Interior.ColorIndex = 26
End With

With ActiveChart.SeriesCollection(4)
.Border.LineStyle = xlAutomatic
.Interior.ColorIndex = 27
End With

With ActiveChart.ChartArea
.Font.Name = "Arial"
.Font.Size = 8
.Font.FontStyle = "Bold"
End With

With ActiveChart.Parent
.Left = 525
.Width = 350
.Top = 25
.Height = 200
End With

Set myChart = Nothing
Set chrtrng = Nothing
End Sub

 

[RoryA]

How does it not work? I can't see anything offhand that has been deprecated or removed in 2007.

 

[chandrashekar]

I am getting following error... Run-time error '-2147467259 (800004005'):
The specified dimension is not valid for the current chart type.

Thanks,

Chandra Shekar B

 

[RoryA]

On which line?
I just ran your code in 2007 and it ran perfectly, with no errors. Do you have Service Pack 2 installed?

 

[chandrashekar]

Yes I have Service Pack is installed in my system.

Regards,

Chandra Shekar B

 

[RoryA]

Well unless you can tell me which line the error happens on, I can't help further since the code works for me exactly as it is.

 

[chandrashekar]

Hi,
Please check this code in 2007, its working in 2003 but not in 2007. I have highlighted error line in Red. Thanks!

Sub chrt_line()
Dim ws As Worksheet
Dim chrtrng As Range
Dim myChart As Chart
Dim rng As Range
ThisWorkbook.Worksheets("Analysis").Select
ThisWorkbook.Worksheets("Analysis").Range("B25").CurrentRegion.Select
Set rng = Selection
cl = rng.Columns.Count - 1
rw = rng.Rows.Count + 1
rg = 25 + rw - 4
chtrng1 = "B25" & ":" & "C" & rg
Set chrtrng = ThisWorkbook.Worksheets("Analysis").Range(chtrng1)
Set myChart = Charts.Add
Set myChart = myChart.Location(xlLocationAsObject, "Analysis")

With myChart
.SetSourceData chrtrng, xlLine
.HasTitle = True
.ChartTitle.Caption = "Change"
.ChartType = xlLine
End With


With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "Sts"
.HasMajorGridlines = False
.HasMinorGridlines = False
'.MajorTickMark = xlTickMarkNone
End With

With ActiveChart.Axes(xlCategory).AxisTitle
.Text = "Sts"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
End With
With ActiveChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Volume"
.HasMajorGridlines = False
.HasMinorGridlines = False
.MinimumScale = 0
.MaximumScaleIsAuto = True
'.MaximumScale = 18
'.MajorUnit = 2
.MajorUnitIsAuto = True
.MinorUnit = 0.4
End With

With ActiveChart.Axes(xlValue).AxisTitle
.Text = "Volume"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlUpward
End With
With ActiveChart.ChartTitle
.Text = "Change"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
End With

With ActiveChart.PlotArea
.Interior.ColorIndex = xlNone
.Border.LineStyle = xlNone
.Top = 20
.Height = 161
.Width = 325
End With

With ActiveChart
.HasDataTable = False
.HasLegend = False
End With

With ActiveChart.SeriesCollection(1)
.Border.LineStyle = xlAutomatic
.Border.Weight = xlMedium
.MarkerBackgroundColorIndex = 43
.MarkerStyle = xlDiamond
.MarkerSize = 6
.ApplyDataLabels ShowValue:=True
End With

With ActiveChart.SeriesCollection(1).DataLabels
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
End With

With ActiveChart.ChartArea
.Font.Name = "Arial"
.Font.Size = 8
.Font.FontStyle = "Bold"
End With

With ActiveChart.Parent
.Left = 525
.Width = 350
.Top = 280
.Height = 200
End With
ThisWorkbook.Worksheets("Analysis").Select
ThisWorkbook.Worksheets("Analysis").Range("A1").Select
Set myChart = Nothing
Set chrtrng = Nothing
End Sub