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.

 

[monirg]

Hi xenou;

yes. Works.

You mean both Code A and Code B work with no run-time error in your XL 2010 ??

I think you'll have to give some sample data that is being plotted

Agree. I’m working almost non-stop on a sample abbreviated file!

Thank you.

 

[xenou]

Here's my test of concept.
It just shows that it works (nothing fancy).

Note that I found I did have problems when I protected the chart and ran the code, so I believe you have to unprotect, run the changes, then protect. In fact, this gets me the same error, except nothing about methods failing (just "unspecified error"). That could be a coincidence.

Sample file: http://northernocean.net/etc/mrexcel/20111016_monirg.zip
sha256 checksum (zip file): 481abc9c0aafa1ee79b965c6e54eeecd1f6071dc4e281f8b9dfd643ae2391eca

 

[monirg]

Hi xenou;

Thanks again for your help, patience and generous time!

1) Macro “Sheet1.Foo” in your sample w/b *.xlsm (with unprotected w/s, no activation of chart, no selection of chart area, and no selection of axis category) runs fine in my XL 2010.
Similar to your observation: protect the w/s with a password, run the macro, and cancel the “Unprotect sheet” message, and I get “MS VBA” error 400. Again as you noted, nothing about methods failing!

2) I’m re-examining your XL 2010 recorded “macro1” (which has a number of redundant statements as one would expect in a recorded macro!) and compare it with my Code A (post # 29, reproduced below), to see if there’s a particular sequencing in XL 2010 chart code that wasn’t in XL 2003.

3) Code A:
--> Monir XL 2003 (32-bit, Win XP 32-bit), with w/s password protection:
..... Works perfectly
--> Monir XL 2010 (Full version, Home & Student, 32-bit, Win 7 Pro 64-bit), w/s password protected:

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

--> Monir XL 2010 (Full version, Home & Student, 32-bit, Win 7 Pro 64-bit), w/s unprotected:
..... Works perfectly

[SIZE=3][B]ActiveSheet.ChartObjects("Chart 1").Activate[/B][/SIZE]
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

Regards.

 

[xenou]

Oops. I had not intended to have any recorded code in my sample workbook. Remove or ignore Module 1. The code I wrote is in Sheet1. I would not advise (for now) any activation of charts or selection of chart elements.

This is what we are aiming at:

1) Macro “Sheet1.Foo” in your sample w/b *.xlsm (with unprotected w/s, no activation of chart, no selection of chart area, and no selection of axis category) runs fine in my XL 2010.

To me that sounds like the problem is solved now. You only need to unprotect, make your chart changes as needed, and protect the workbook. As far as working or not working in XL2003, who cares - it's an xlsm now running in 2010. Is this going in circles now? What I mean is, I would not try to use the XL2003 code if you are now using XL2010. Write it so it works in XL2010.

ξ

 

[monirg]

Hi xenou;

Remove or ignore Module 1

Done!

Is this going in circles now?

It seems so at some level!

The whole exercise can be summarized in one or two paragraphs.

1) I have 50+ XL 2003 workbooks, each runs perfectly in XL 2003, and I need to transfer them to the recently acquired XL 2010 on a new laptop (4 times faster!).
Each w/b has at least one password protected w/s to preserve the sheet formatting and preventing unintentional changes.
None of those workbooks would run error-free As Is in my XL 2010, producing a run-time error with ref to the XL 2003 chart code.

2) I picked up one of those workbooks as a case study and tried to figure out the cause of the problem.
By pure accident, I manually removed the w/s protection and tried again. The workbook surprisingly worked fine in XL 2010 with no errors. I tried another w/b after unprotecting its w/s, and it also worked perfectly and as desired.

a. Does this mean the chart code (Code A, post # 33) is compatible, and perhaps it’s just a matter of carefully re-checking the “XL 2010 Options” ?? ... Perhaps!
b. Could it be that the XL 2003 VBA is not 100% compatible with XL 2010 VBA (particularly when it comes to chart code; as you’ve suggested earlier) and I need to dig deeper instead of learning XL 2010 on the fly ?? (Needless to say, I’ve learned a lot from the discussion!)
c. Could the issue be something particular to my situation (as you’ve also suggested earlier) ??

3) The simplest/easiest workaround solution for me and for now, though inconvenient, is as follows:
i. open any of the 50+ XL 2003 workbooks in XL 2010
ii.. manually unprotect the w/s
iii. change nothing in the code, and run the macro
iv. if the file is “Read only” and want to save the changes:
--> manually protect the w/s
--> use “Save As” with a new name and the xlsm format
v. otherwise:
--> exit without saving the file
--> it would be still protected and in the old xls (compatible) format, so I’d know which is which!

Regards.

 

[xenou]

i. open any of the 50+ XL 2003 workbooks in XL 2010
ii.. manually unprotect the w/s
iii. change nothing in the code, and run the macro

I would not consider the above easiest (necessarily) - certainly not if involving manually unprotecting anything in 50+ workbooks (i.e., why not add a line to the code to unprotect the chart while you edit it's elements in your routine?). But only you can decide what's best in the overall situation. You've got many variables at play here so it's not a simple thing to evaluate.

If you like, you can try to convince me why you need sheet protection. My personal philosophy is to live without it - so how's that for easy? I find I don't need it about 98% of the time - and never when it gets in the way.

ξ

 

[monirg]

Hi xenou;

xenou wrote:
I would not consider the above easiest (necessarily). But only you can decide what's best in the overall situation.

Believe me, it’s the easiest workaround solution for me by far!
The logic in each chart code in each XL 2003 w/b is different, and having a one-size-fits-all code fix wouldn’t work for me.
More critically, my philosophy in writing codes is rather simple. I write the code, extensively test the procedure under different scenarios, and then move on.
I don’t claim any of my codes to be the most efficient, or you as an excel expert would design them the same way!

xenou wrote:
If you like, you can try to convince me why you need sheet protection.

Well, that’s easy, and may not require too much convincing!
In XL 2010: Can you block cells and hide formulas on the worksheet without protecting the w/s ??
If your answer is in the affirmative, then I’ll never use w/s protection again, period!

Regards.

 

[xenou]

In XL 2010: Can you block cells and hide formulas on the worksheet without protecting the w/s ??
If your answer is in the affirmative, then I’ll never use w/s protection again, period!

Yes. I don't look at them!

 

[monirg]

Hi xenou;

Monir wrote:
In XL 2010: Can you (effectively) block cells and hide formulas on the worksheet without protecting the w/s ??

xenou wrote:
Yes. I don't look at them!

OK. So by not looking at them you can prevent later unintentional changes!
Not sure, but wouldn’t argue.

Regards.
Monir

 

[xenou]

Yes. To be more specific, there are quite a lot of ways you can protect your data. Sorry to be the bearer of bad news but you have probably chosen the weakest one of them all - password protecting a worksheet. I wouldn't mind hearing more about why you have chosen this method and how you think it protects your data. Generally speaking, since protection is weak anyway, for my part I don't bog down my workbooks with such things - I use lightweight methods for data I merely want to keep away from user's eyes, and for data that really must be kept safe I use better strategies than relying on Excel passwords.