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]

Welcome to the Board.

I don't see MaxStaff being assigned a value anywhere in your code. If you comment out the offending line do you get any other errors? Can you set the major unit manually?

 

[Beebo]

Hi Andrew,

Sorry MaxStaff (in this case) = 3000, just an integer show the total staff that will be used in my chart calculations.

If I comment out the offending lines, I DO NOT get any errors - it is definately the MajorUnit problem. Yes, I can set those manually - but that wont work for what we use this spreadsheet for.

What is particularly irritating to me (no offense at you) is that when I look up Axis MajorUnit help on Microsoft's site, and copy THEIR code - it blows up (!!! ) - dont they test their own code??? As well, I cannot find any other web threads that talk about this 2003 to 2007 problem.

The only other 'clue' I can offer you is that when I write the code in long hand, following the MS help - that too blows up in Office 2007!!!

Do you have any ideas at all that might help?

Thanks,

Beebo

 

[Andrew Poulsom]

I don't have access to Excel 2007 to check at the moment, but I will try to look later today. One thought is that you may need to set the maximum before you set the majorunit.

 

[RoryA]

Charts in 2007 can be a bit temperamental. Odd as it sounds, you might try:

X = .MajorUnit
.MajorUnit = MaxStaff / 5

 

[Beebo]

Rory,

Thanks, but did you look at the code I posted? Not sure how putting "X=.MajorUnit" within a With block would make any difference?

Either way, tried that and nada...

Beebo

 

[Beebo]

If its any further help, in getting help with this, we've noticed that NONE of the properties/methods set within the WITH block are working.

It was thus that we presumed we were 'addressing' the Axes collection incorrectly so we consulted Microsoft's MSDN site for proper syntax and there we found their own code, we copied it, we pasted it, IT DOESNT WORK.

So where our problem seemed to be with MajorUnit, it is actually with ANY property or method we try to set within that WITH block.

Worse still, if we do them as individual lines (no WITH block), and follow the exact syntax on MS MSDN site - still doesnt work!!!

Extremely frustrating!!!

 

[RoryA]

Rory,

Thanks, but did you look at the code I posted?

No, of course not, I was just guessing wildly... I did say it might sound odd (if you read what I posted), but I have seen a situation with a chart in 2007 where reading the Top property of its PlotArea was necessary before you could change it.

 

[Beebo]

Yes, I did read what you posted, and tried it, but alas, it did not work.

No disrespect intended, but to add a line like "X = .MajorUnit" with X simply being an arbitrary value stored out there... Whoa, that certainly killed any illusion of Office 2007 being an "improvement" on earlier versions. I do appreciate your expertise that 2007 can be "tempermental", but I thought the idea of new versions was to address that kind of thing and deliver a good working product, well-documented!!! (again, comment not intended 'at' you - MS is obviously the lost cause here)

Our testing and debugging continues and we have discovered that anything (prop/meth) in our WITH block doesnt work. So we thought it was the way we were 'addressing' the object - but as I say, then we copied Microsoft's OWN code right off their help site - verbatum.

IT DOESNT WORK. Where does one go from there? OpenOffice is looking real good about now... And our Office 2007 CD's will sure make nice shiny frisbees because the apps on them are appearing more and more useless - indeed like some giant step backward in software, instead of forward.

We will keep working on this - if you have any other ideas I surely would appreciate them. Pardon my frustration - it is not at you - I just made the stupid mistake of thinking that "upgrading" meant better software, but it seems Microsoft's idea of "upgrade" is roughly what Osama Bin Laden did in "upgrading" the World Trade Center.

 

[RoryA]

Can you post a sample workbook somewhere? 2007 seems to be much more sensitive to the order in which certain operation sare carried out on charts. Mostly, I assume, because the entire charting model has been changed.
Oh, and I take no offence at your rant about 2007 - I don't work for MS, and Office 2007 has frustrated me too on many occasions!