Our firm deploys different versions of Microsoft Office: Office 2000, Office
XP and now Office 2005. How do I code in VBA from Office & Excel XP (2003) to
be compatible across the object models of these versions of Excel?

There seems to be differences in the object models and functions and/or
constants that are available for setting properties. I am having problems
with borders, colors and formatting of cells from one version to the next.
VBA based macros that work in one version generate a run-time error in a
different version.

2nd question: Is there a method to reduce the size of a workbook with many
sheets and macros. The workbook size is above 1.5 mb; the user community
desires smaller sizes to enable faster email upload/download times.

Thank You for your consideration.
--
John

Hi John,

Our firm deploys different versions of Microsoft Office: Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office & Excel XP (2003)
to be compatible across the object models of these versions of Excel?

You should always code to the lowest common denominator, i.e. develop your
code in the lowest version which may be encountered.

If, conversely, code is developed in a later version, then use of new
features will, necessarily, cause run-time errors when the code is run under
an older version.

2nd question: Is there a method to reduce the size of a workbook with many
sheets and macros. The workbook size is above 1.5 mb; the user community
desires smaller sizes to enable faster email upload/download times.

If the workbook size seems disproportionately large:
(1) Consider downloading Rob Bovey's CodeCleaner addin which is freely
downloadable at:

http://www.appspro.com/Utilities/Utilities.htm

(2) See Debra Dalgleish's notes on the UsedRange and filesize at:

http://www.contextures.com/xlfaqApp.html#Unused


You might also wish to consider moving code from the workbook to an addin.

---
Regards,
Norman



"Wboson" wrote in message
...
Our firm deploys different versions of Microsoft Office: Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office & Excel XP (2003)
to
be compatible across the object models of these versions of Excel?

There seems to be differences in the object models and functions and/or
constants that are available for setting properties. I am having problems
with borders, colors and formatting of cells from one version to the next.
VBA based macros that work in one version generate a run-time error in a
different version.

2nd question: Is there a method to reduce the size of a workbook with many
sheets and macros. The workbook size is above 1.5 mb; the user community
desires smaller sizes to enable faster email upload/download times.

Thank You for your consideration.
--
John

Thank You Norman.

The reason I tried using the Excel 2002 (XP) version w/VBA macros is because
people with Excel 2002 had errors running the workbook created in Excel 2000.
This is because Microsoft changed the underlying object model between the two
versions. There we no runtime errors in the Excel 2000 workbook version
except when used with Excel 2002.

Thus the lower common denominator did not work for those who upgraded.

There lies my dilemna; how do I support two different object models w/VBA
code?
--
John



"Norman Jones" wrote:

Hi John,

Our firm deploys different versions of Microsoft Office: Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office & Excel XP (2003)
to be compatible across the object models of these versions of Excel?

You should always code to the lowest common denominator, i.e. develop your
code in the lowest version which may be encountered.

If, conversely, code is developed in a later version, then use of new
features will, necessarily, cause run-time errors when the code is run under
an older version.

2nd question: Is there a method to reduce the size of a workbook with many
sheets and macros. The workbook size is above 1.5 mb; the user community
desires smaller sizes to enable faster email upload/download times.

If the workbook size seems disproportionately large:
(1) Consider downloading Rob Bovey's CodeCleaner addin which is freely
downloadable at:

http://www.appspro.com/Utilities/Utilities.htm

(2) See Debra Dalgleish's notes on the UsedRange and filesize at:

http://www.contextures.com/xlfaqApp.html#Unused


You might also wish to consider moving code from the workbook to an addin.

---
Regards,
Norman



"Wboson" wrote in message
...
Our firm deploys different versions of Microsoft Office: Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office & Excel XP (2003)
to
be compatible across the object models of these versions of Excel?

There seems to be differences in the object models and functions and/or
constants that are available for setting properties. I am having problems
with borders, colors and formatting of cells from one version to the next.
VBA based macros that work in one version generate a run-time error in a
different version.

2nd question: Is there a method to reduce the size of a workbook with many
sheets and macros. The workbook size is above 1.5 mb; the user community
desires smaller sizes to enable faster email upload/download times.

Thank You for your consideration.
--
John

Hi John,

If development is conducted under the latest version of Excel, then users
working with earlier versions are extremely likely to encounter run-time
errors, as the earlier version will be incapable of interpreting methods or
properties associated with new or updated features.

Conversely, it is, in my experience, rare for later versions to be similarly
inconvenienced by code developed in earlier versions. Microsoft has an
overwhelming imperative to (attempt to) ensure backward compatibility.

I am not asserting that such backward compatibility is equivalent to
complete compatibility, but perhaps you could furnish some specifics of
instances that you have encountered where such compatibility proved
inadequate.

---
Regards,
Norman



"Wboson" wrote in message
...
Thank You Norman.

The reason I tried using the Excel 2002 (XP) version w/VBA macros is
because
people with Excel 2002 had errors running the workbook created in Excel
2000.
This is because Microsoft changed the underlying object model between the
two
versions. There we no runtime errors in the Excel 2000 workbook version
except when used with Excel 2002.

Thus the lower common denominator did not work for those who upgraded.

There lies my dilemna; how do I support two different object models w/VBA
code?
--
John



"Norman Jones" wrote:

Hi John,

Our firm deploys different versions of Microsoft Office: Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office & Excel XP
(2003)
to be compatible across the object models of these versions of Excel?

You should always code to the lowest common denominator, i.e. develop
your
code in the lowest version which may be encountered.

If, conversely, code is developed in a later version, then use of new
features will, necessarily, cause run-time errors when the code is run
under
an older version.

2nd question: Is there a method to reduce the size of a workbook with
many
sheets and macros. The workbook size is above 1.5 mb; the user
community
desires smaller sizes to enable faster email upload/download times.

If the workbook size seems disproportionately large:
(1) Consider downloading Rob Bovey's CodeCleaner addin which is
freely
downloadable at:

http://www.appspro.com/Utilities/Utilities.htm

(2) See Debra Dalgleish's notes on the UsedRange and filesize at:

http://www.contextures.com/xlfaqApp.html#Unused


You might also wish to consider moving code from the workbook to an
addin.

---
Regards,
Norman



"Wboson" wrote in message
...
Our firm deploys different versions of Microsoft Office: Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office & Excel XP
(2003)
to
be compatible across the object models of these versions of Excel?

There seems to be differences in the object models and functions and/or
constants that are available for setting properties. I am having
problems
with borders, colors and formatting of cells from one version to the
next.
VBA based macros that work in one version generate a run-time error in
a
different version.

2nd question: Is there a method to reduce the size of a workbook with
many
sheets and macros. The workbook size is above 1.5 mb; the user
community
desires smaller sizes to enable faster email upload/download times.

Thank You for your consideration.
--
John

Could you provide some example code that works in 2000 and bombs
in 2002?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Wboson" wrote in message
...
Thank You Norman.

The reason I tried using the Excel 2002 (XP) version w/VBA
macros is because
people with Excel 2002 had errors running the workbook created
in Excel 2000.
This is because Microsoft changed the underlying object model
between the two
versions. There we no runtime errors in the Excel 2000 workbook
version
except when used with Excel 2002.

Thus the lower common denominator did not work for those who
upgraded.

There lies my dilemna; how do I support two different object
models w/VBA
code?
--
John



"Norman Jones" wrote:

Hi John,

Our firm deploys different versions of Microsoft Office:
Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office &
Excel XP (2003)
to be compatible across the object models of these versions
of Excel?

You should always code to the lowest common denominator, i.e.
develop your
code in the lowest version which may be encountered.

If, conversely, code is developed in a later version, then use
of new
features will, necessarily, cause run-time errors when the
code is run under
an older version.

2nd question: Is there a method to reduce the size of a
workbook with many
sheets and macros. The workbook size is above 1.5 mb; the
user community
desires smaller sizes to enable faster email upload/download
times.

If the workbook size seems disproportionately large:
(1) Consider downloading Rob Bovey's CodeCleaner addin
which is freely
downloadable at:

http://www.appspro.com/Utilities/Utilities.htm

(2) See Debra Dalgleish's notes on the UsedRange and
filesize at:

http://www.contextures.com/xlfaqApp.html#Unused


You might also wish to consider moving code from the workbook
to an addin.

---
Regards,
Norman



"Wboson" wrote in message
...
Our firm deploys different versions of Microsoft Office:
Office 2000,
Office
XP and now Office 2005. How do I code in VBA from Office &
Excel XP (2003)
to
be compatible across the object models of these versions of
Excel?

There seems to be differences in the object models and
functions and/or
constants that are available for setting properties. I am
having problems
with borders, colors and formatting of cells from one
version to the next.
VBA based macros that work in one version generate a
run-time error in a
different version.

2nd question: Is there a method to reduce the size of a
workbook with many
sheets and macros. The workbook size is above 1.5 mb; the
user community
desires smaller sizes to enable faster email upload/download
times.

Thank You for your consideration.
--
John

Hi Chip,

Could you provide some example code that works in 2000 and bombs
in 2002?

I think:

Debug.Print oRng.Value(iRow, iCol)

(Because MS added the optional XMLValueType to the Range object's
Value property.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev