Excel 2003 macros don't work in Excel 2000

[Dr Rubick]

I have a spreadsheet with macros that works in Excel 2003 but not in Excel
2000. In each case, Excel 2000 crashes with a failure in an object's
property or method. I know this sounds like an early-binding problem, but a
few things about it don't add up. First, I don't use any inter-application
automation, it's all Excel. Secondly, I've written many excel macros with
extensive use of early bound objects, and everything else works fine on the
Excel 2000 computers in my office - only this one workbook has the problem.

When a copy of the workbook is opened in Excel 2000, it seems to get
progressively more corrupt as it is used. Object references which work fine
the first time you run the macro can give a "Method '{method name}' of object
'{object type}' failed." error the next. In a few cases, I've even seen this
problem on objects declared as generic "Object" types. Any ideas as to what
my problem is?

 

[Jim Thomlinson]

Stupid question but... Does the code compile under 2000? Are all of the
references valid?

 

[Dr Rubick]

I'm not sure what you mean by compiling it. After reading your question I
hit "Debug - Compile VBA Project", and everything worked fine. As for the
references, these macros do not refer to anything outside the workbook, so I
don't know how a reference could be valid in Excel 2003 and invalid in Excel
2000.

 

[Jim Thomlinson]

Like I said. Stupid. That is what I meant by compile. That means that your
syntax is all correct. What methods/properties are breaking down for what
objects?

 

[Dr Rubick]

Some of them are:

Application.CalculationState (though I discovered early on that Excel 2000
doesn't support this, so I commented this one out. The problem still
presists...)

Application.Calculation

Application.Calculate

Application.Cursor (works to change my mouse to an hourglass, but then I
cannot get it to work again to change the mouse back to a pointer - very
annoying).

Application.ScreenUpdating

Application.Worksheetfunction.Isnumber (the error box complained about
"Isnumber")

{worksheet object}.Protect

{worksheet object}.ProtectContents

{range object}.Font.ColorIndex (the error box complained about "Font")

{range object}.Interior.ColorIndex (the error box complained about "Interior")

{range object}.Locked

 

[Rob Bovey]

Choose Tools/References from the Visual Basic Editor menu and see if any
of your selected references are prefixed with MISSING. This is a common
problem with programs written in later versions of Excel being moved to
earlier versions and it can cause the kind of errors you describe (i.e.
errors on lines of code that have nothing to do with the MISSING reference).

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Dr Rubick]

There are five checked references:

"Visual Basic For Applications"
"Microsoft Excel 9.0 Object Library"
"OLE Automation"
"Microsoft Office 9.0 Object Library"
"Microsoft Forms 2.0 Object Library"

None of them are marked "Missing"

 

[Rob Bovey]

Go to my web site, shown below my signature, download a copy of my VBA
Code Cleaner add-in and try running it on this workbook. It's possible these
problems are being caused by some code corruption in the workbook that
cleaning it will fix.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Dr Rubick]

Actually, I found your web site through another of these postings earlier
today and already tried it. It reduced the file size from about 1MB to about
700kB, but it had no effect on this problem.

 

[Rob Bovey]

Is this workbook something you can send me? I'd be interested to have a
look at it.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm