Code to different object models

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" <Wboson@discussions.microsoft.com> wrote in message
news:77CC6AC0-A33D-408C-84D3-AC0CFDEBAEF6@microsoft.com...
> 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" <Wboson@discussions.microsoft.com> wrote in message
> news:77CC6AC0-A33D-408C-84D3-AC0CFDEBAEF6@microsoft.com...
> > 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" <Wboson@discussions.microsoft.com> wrote in message
news:3716A203-0B77-4CE0-A9C7-1D8C3391A1DD@microsoft.com...
> 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" <Wboson@discussions.microsoft.com> wrote in message
>> news:77CC6AC0-A33D-408C-84D3-AC0CFDEBAEF6@microsoft.com...
>> > 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" <Wboson@discussions.microsoft.com> wrote in message
news:3716A203-0B77-4CE0-A9C7-1D8C3391A1DD@microsoft.com...
> 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" <Wboson@discussions.microsoft.com> wrote in message
>> news:77CC6AC0-A33D-408C-84D3-AC0CFDEBAEF6@microsoft.com...
>> > 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