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?

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

"Dr Rubick" wrote:

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?

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" wrote:

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

"Dr Rubick" wrote:

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?

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" wrote:

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" wrote:

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

"Dr Rubick" wrote:

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?

Some of them a

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

"Jim Thomlinson" wrote:

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" wrote:

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" wrote:

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

"Dr Rubick" wrote:

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?

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" wrote in message
...
Some of them a

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

"Jim Thomlinson" wrote:

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" wrote:

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" wrote:

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

"Dr Rubick" wrote:

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?

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" wrote:


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" wrote in message
...
Some of them a

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

"Jim Thomlinson" wrote:

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" wrote:

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" wrote:

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

"Dr Rubick" wrote:

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?

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" wrote in message
...
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" wrote:


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" wrote in message
...
Some of them a

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

"Jim Thomlinson" wrote:

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" wrote:

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" wrote:

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

"Dr Rubick" wrote:

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?

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" wrote:


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" wrote in message
...
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" wrote:


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" wrote in message
...
Some of them a

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

"Jim Thomlinson" wrote:

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" wrote:

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" wrote:

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

"Dr Rubick" wrote:

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?

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

"Dr Rubick" wrote in message
...
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" wrote:


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" wrote in message
...
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" wrote:


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" wrote in message
...
Some of them a

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

"Jim Thomlinson" wrote:

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" wrote:

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" wrote:

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

"Dr Rubick" wrote:

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?