Works in 2003, not in 2007

[Beebo]

I have a spreadsheet that worked great in Excel 2003, but now testing Excel 2007, I am 'blowing up' on the MajorUnit property of an Axes.

Is there anyone out there who can tell me why the following code worked in 2003, and now blows up in 2007 with an error message that says:

"Error -2147467259: Method MajorUnit of Object Axis failed"

Here is the code...

Set wb = ThisWorkbook
Set w = wb.Worksheets("Graphs")
Set c = w.ChartObjects(1).Chart
Set srs = c.SeriesCollection(1)
s = srs.FormulaR1C1
i = InStr(6, s, ":R")
j = InStr(i, s, "C")
sC = Mid(s, i, j - i)

j = w.ChartObjects.Count
For i = 1 To j
Set c = w.ChartObjects(i).Chart
Set a = c.Axes(xlValue)

' THIS IS WHERE THE THING FAILS

With a
.MajorUnit = MaxStaff / 5
.MaximumScale = MaxStaff
.MinimumScale = 0
End With

For Each srs In c.SeriesCollection
s = Replace(srs.FormulaR1C1, sC, sN)
srs.FormulaR1C1 = s
Next

Any help figuring this one out is GREATLY appreciated!

- Beebo

 

[Andrew Poulsom]

I just ran this in Excel 2007 with no problems:

Sub Test()
    Const MaxStaff As Integer = 3000
    Dim wb As Workbook
    Dim w As Worksheet
    Dim c As Chart
    Dim a As Axis
    Set wb = ThisWorkbook
    Set w = wb.Worksheets("Graphs")
    Set c = w.ChartObjects(1).Chart
    Set a = c.Axes(xlValue)
    With a
        .MajorUnit = MaxStaff / 5
        .MaximumScale = MaxStaff
        .MinimumScale = 0
    End With
End Sub

My sample data plotted as a column chart was:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Name</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Value</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>a</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2500</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>b</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2600</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>c</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2700</TD></TR></TBODY></TABLE>

My version of Excel is 12.0.6331.5000 SP1 MSO (12.0.6320.5000)

 

[Beebo]

Thanks very much for the offer to look at the spreadsheet - believe me, I really do appreciate that. However, the "powers that be" will not allow me to post the spreadsheet - sensitive info therein.

We will just keep knocking at it and one of us is going to try to get through to MS themselves and see what we can get out of them.

If I do get some solution, I will post it on Mr. Excel for all to see.

Again, thanks!

Beebo

 

[Beebo]

We seem to have found a very good potential source to this problem - we are not fully through our analysis, but this is looking like the culprit.

We wrote a brand new, from the ground up spreadsheet and tried the same code as in the spreadsheet that is not working. To our surprise, the new spreadsheet works just fine. We then started searching for anything in the failing spreadsheet that might be different.

Something in the 'bad' spreadsheet is protected. We cannot determine what is protected, the sheet simply says its protected, while that has not prohibited us from working with cells, values or any part of the spreadsheet.

So our determination to this point is that the protection is somehow causing the method to fail. We are trying to locate the fellow who wrote the original spreadsheet in our organization to see if we can get his password and unlock whatever is protected, to confirm what we suspect.

This IS different behavior between 2003 and 2007 - and as yet we have not definitively nailed it down, but protection is looking like the culprit.

 

[Beebo]

We have confirmed the following:

In Office Excel 2007, if a spreadsheet (or any part of it) is password protected, addressing the properties of a chart on that spreadsheet will fail.

We confirmed this by creating a brand new spreadsheet, creating a VBA method to alter the MajorUnit values on a Chart Axes collection, and then testing that method in both password protected, and non-protected states.

When protected, the method fails. When not protected, the method works.