Excel 2007 won't execute 2003 VBA code

[Lee]

I have a macro that was created with Excel 2003 VBA code but sometimes Excel
2007 won't execute the code and sometimes it will. Any ideas what might be
happening? Is Excel 2007 supposed to be backwards compatible? I can't use
the new Excel 2007 code because not all my users have Excel 2007 yet. Most
are still using Excel 2003.

Excel 2003 Code:
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = strTitle + Chr(10) + strSubTitle
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = strXAxis
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYAxis
End With

However, this code does the same thing in Excel 2007 and it always works:
Excel 2007 code:
With ActiveChart
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = strTitle + Chr(10) + strSubTitle
.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
.Axes(xlCategory, xlPrimary).AxisTitle.Text = strXAxis
.SetElement (msoElementPrimaryValueAxisTitleRotated)
.Axes(xlValue, xlPrimary).AxisTitle.Text = strYAxis
End With

Thanks in advance,
Lee

 

[Don Guillett]

I'm not familiar with the problems with the particular code shown. If all
else fails, you could put in a version test

 

[Lee]

"Application.Version" will give me the version number but I can't use that to
direct the software to the appropriate 2003 or 2007 code because the newer
2007 code won't compile in Excel 2003. Also "conditional compilation" is
pretty much useless because I can't expect the user to go into the VBE and
set conditional constants.

What I really need is for Excel 2007 to be backward compatible with 2003 VBA
code or else I'll have to write two programs, one for Excel 2003 users and
one for Excel 2007 users. Does anyone know if it is backward compatible?

 

[Bob Phillips]

"Application.Version" will give me the version number but I can't use that
to
direct the software to the appropriate 2003 or 2007 code because the newer
2007 code won't compile in Excel 2003. Also "conditional compilation" is
pretty much useless because I can't expect the user to go into the VBE and
set conditional constants.

It won't, but if you put the 2007 code in a separate module the compiler
won't complain.

What I really need is for Excel 2007 to be backward compatible with 2003
VBA
code or else I'll have to write two programs, one for Excel 2003 users and
one for Excel 2007 users. Does anyone know if it is backward compatible?

Probably a problem as the charting engine was re-written (badly!).

 

[Martin Brown]

"Application.Version" will give me the version number but I can't use that to
direct the software to the appropriate 2003 or 2007 code because the newer
2007 code won't compile in Excel 2003. Also "conditional compilation" is
pretty much useless because I can't expect the user to go into the VBE and
set conditional constants.

The simplest solution is to fork the codebase and have a specifically
2007 version that is carefully bodged until it actually works in
practice. Even if you get the syntactic sugar exactly right you will
still experience intermittent race conditions where axes try to be
annotated before they have been drawn and crazy things like that without
judicious placement of delays. These timing faults are data length
dependent That is part of the reason why XL2007 charting is so glacially
slow.

I think you can sort of get away with it if there is a stub to decide
which version to run and the XL2007 code is isolated in a module that is
never run by XL2003. You may need to be careful only to use XL2003
syntax that XL2007 will tolerate so that the spreadsheet cannot be
wrecked by your user saving it in .XLSM native form.

The all new defects in XL2007 graphic model are annoying. Gratuitious
differences in the specification of objects are amongst the worst of the
incompatibilities they introduced. The new polynomial fit on graphs now
gives that same *wrong* answer as LINEST for difficult problems.

What I really need is for Excel 2007 to be backward compatible with 2003 VBA
code or else I'll have to write two programs, one for Excel 2003 users and
one for Excel 2007 users. Does anyone know if it is backward compatible?

XL2007 graphics object and charting are an unmitigated disaster. Avoid
and advise your customers to stick with XL2003 until XL2007 is up to
spec. No point in wasting money on a defective product. Even with SP2
applied it is better but still not really good.

My copy of Word 2007 is an even bigger crock of unstable sh*t. Somehow
it has corrupted its European paper sizes to essentially random values
that bear no relation to the true dimensions. It all works OK on US
Letter size but everything else is pretty well hopeless. What you see is
never what you get. So much for progress in Office 2007.

Regards,
Martin Brown

Very sensible of them!!!