Excel 2003 / Excel 2010 Compatibility Issue ??

[monirg]

Hello;

My earlier Thread in Excel Questions Forum:
VBA: Opening XL 2003 w/b in XL 2010 Produces Error ‘1004’ ??
http://www.mrexcel.com/forum/showthread.php?t=583852

I’d like to regard the problem as a compatibility issue rather than an excel question; particularly for those who might be interested in the subject or have encountered a similar problem.

Here’s in a nutshell a description of the problem and my earlier solution.

1) I need to transfer many of my XL 2003 workbooks (developed and run perfectly on different computers, XL 2003, Win XP 32-bit) to my new laptop (Win 7 Pro 64-bit, Office 2010 full version, 32-bit).

2) Any of those XL 2003 workbooks (with w/s password protection, macros, w/s events) produces in XL 2010 the error:
“Run-time error ‘1004”

3) In XL 2010, and been annoyed with the run-time error, I tried a w/b Open() event to unprotect the w/s in the transferred XL 2003 w/b, and a w/b BeforeClose() event to protect the w/s before exiting. Also tried calling a Sub to unprotect the w/s directly before doing anything. Nothing worked.
The Run-time error ‘1004’ persisted and appeared at different locations / different routines depending on the code changes.

4) Even unprotecting the w/s in the XL 2003 w/b before transferring the file to the new m/c running XL 2010 and then protecting the w/s in the XL 2010 environment before save/open didn’t work!
My earlier “inconvenient” solution (post # 6 in the above link)

to manually remove the w/s password protection (either before or after the transfer), and keep the w/s unprotected

remains the only solution (so far!) that’s working for me in XL 2010.

5) Since MS claims that a working XL 2003 w/b would run successfully AS IS in XL 2010 (i.e.; no VBA code modifications, no added events, no w/s attribute changes), I wonder if the problem should be looked at by MS Excel Development Team to possibly identify the XL 2010 bug and fix the problem once and for all.
(Will post here the MS solution, if any!)

Any comments ?? Is communicating with MS Excel Team worth the time and effort ??

Regards.

 

[xenou]

Change:

Set chrt = ws.ChartObjects("Chart 1")

To:

Set chrt = ws.ChartObjects("Chart 1").Chart

My bad - I don't code for charts a lot but I should have remembered this. As an added plus, when you start writing your code using the chrt variable, intellisense will kick in and you'll start to see the properties and methods for charts. You could also dim an axis variable too:

Dim ws As Worksheet
Dim chrt As Chart
Dim x As Axis

Set ws = ActiveSheet
Set chrt = ws.ChartObjects("Chart 1").Chart
Set x = chrt.Axes(xlCategory)

This is a helps when learning about objects on the fly - just to get an idea of what their properties are and what you can work with.

 

[monirg]

Hi xenou;

Getting closer, but no cigar yet!

Now the run-time error is down couple more statements in the same trouble section of the code and within the IF block.
This is encouraging in a sense ... It is moving down!

Run-time error ‘-2147467259 (80004005)’:
Method ‘MinimumScale’ of object ‘Axis’ failed

and the following statement is highlighted:

         [B].MinimumScale = 0.7[/B]

Regards.

 

[xenou]

yes, well. At least we are now getting the same error in the same place. I can't understand this one. When I explore the chart axis members, I don't find that a category axis has a minimum scale that can be set. So I don't know how this works.

Maybe this *is* something that has changed in the new chart object model. What kind of data is in this chart? What's on this axis? [Note: At this point it may be useful to try to record this change as you make it "by hand" and see how the macro recorder treats it - but, as I said, I couldn't find this either in code or in the XL2010 Chart options on screen].

 

[monirg]

Hi xenou;

What kind of data is in this chart? What's on this axis?

It is an XY Chart, multiple series, numerical data, linear scale, with the min and max X-Axis values as specified in the code.
The rest is automatic.

I'm searching the XL 2010 VBA Help again on Axes (xlCategory). I haven't found yet the method/property that returns or sets the min or max of the category axis in a chart. I'm sure it is there somewhere.
Even pressing F1 on MinimumScale or MaximumScale in the code doesn't return any helpful info.

Regards.

 

[NdNoviceHlp]

This works for me in every version and MS OS. HTH. Dave

With Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlCategory)
.MinimumScale = Format(Sheets("Sheet1").Cells(1, 1).Value, "###0")
.MaximumScale = Format(Sheets("Sheet1").Cells(Lastrow, 1).Value + 1, "###0")
End With

 

[monirg]

Hi Dave;

Thank you for your suggestion.
I’m afraid it also produced the same run-time error reported earlier in post # 22:
“Method ‘MinimumScale’ of object ‘Axis’ failed”

For whatever reason, it appears that XL 2010 doesn’t like setting the xlCategory by the Axis.MinimumScale property.
I suspect the property works fine with the value axis as described in "Excel 2010 Help".

Regards.

 

[NdNoviceHlp]

The code works for XL2010 for me? Dave

 

[xenou]

Okay, going for me too when I use an XY chart (I was able to see these options in the axis dialogue once I changed the chart type to XY, and then record a macro too).

 

[monirg]

Hello;

If it is not too much trouble, could you please replace your XY-chart recorded macro code with either of the following code segments to see if either runs error free in your XL 2010 ??
It would be greatly appreciated!

1) Code A:
--> In Monir XL 2003 (Win XP 32-bit):
..... Works perfectly
--> In Monir XL 2010 (Full version, Home & Student, 32-bit, Win 7 Pro 64-bit):

Run-time error ‘1104’:
Application-defined or object-defined error
... with ref to the highlighted statement in the code

[B][SIZE=3]ActiveSheet.ChartObjects("Chart 1").Activate[/SIZE][/B]
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory)
        If Range("J41") = "TIP" Then
            .MinimumScale = 0.7
            .MaximumScale = 1.05
        Else
            .MinimumScale = 0.15
            .MaximumScale = 0.71
        End If
    End With

2) Code B:
--> In Monir XL 2010 (Full version, Home & Student, 32-bit, Win 7 Pro 64-bit):

Run-time error ‘-2147467259 (80004005)’:
Method ‘MinimumScale’ of object ‘Axis’ failed
... with ref to the highlighted statement in the code

Dim ws As Worksheet
Dim chrt As Chart
  Set ws = ActiveSheet
  set chrt = ws.ChartObjects("Chart 1").Chart
  With chrt.Axes(xlCategory)
     If ws.Range("J41") = "TIP" Then
        [B][SIZE=3].MinimumScale = 0.7[/SIZE][/B]
        .MaximumScale = 1.05
     Else
        .MinimumScale = 0.15
        .MaximumScale = 0.71
     End If
  End With

Regards.

 

[xenou]

yes. Works. I think you'll have to give some sample data that is being plotted. I think it has to to with the arrangement of the chart. Sorry I can't understand it better - I've just spent 10 minutes trying to work out some x-y chart that seems to fit what you are doing and realized I'm not getting anywhere (though I could run your code on my stupid chart that probably is nothing like what you have). Charts can be tricky beasts.