forwards/backward compatibility Excel 2003-2007

[c1802362]

I posted on a similiar topic yesterday about code running fine on
Excel 2003/XP and not running on Excel 2007/Win7.

Well, I got everything to run on 2007 if the Excel 2003 file is saved
in .xlsm format (xlsx bombs)

Now the problem is the .xlsm file sent back to Excel 2003 loads fine,
except the custom menu pulldown can no longer find the macros linked
to it. If the user launches the macros from the the Tools/Macro/Macros
path, everything works fine. However, the custom menu on the menubar
is broken.

So, to recap. The .xls file runs fine on Excel 2003, but the not on
2007. The .xlsm file runs fine on 2007, but not on 2003. The code is
identical in both versions.

Does anyone have a method to enable cross version compatibility (so
that I only relelase and maintain one file) or do I have to caution
the user base to use a version aligned to their version of Excel (and
distribute .xls and .xlsm versions)?

Art

 

[Martin Brown]

I posted on a similiar topic yesterday about code running fine on
Excel 2003/XP and not running on Excel 2007/Win7.

Well, I got everything to run on 2007 if the Excel 2003 file is saved
in .xlsm format (xlsx bombs)

Now the problem is the .xlsm file sent back to Excel 2003 loads fine,
except the custom menu pulldown can no longer find the macros linked
to it. If the user launches the macros from the the Tools/Macro/Macros
path, everything works fine. However, the custom menu on the menubar
is broken.

So, to recap. The .xls file runs fine on Excel 2003, but the not on
2007. The .xlsm file runs fine on 2007, but not on 2003. The code is
identical in both versions.

Does anyone have a method to enable cross version compatibility (so
that I only relelase and maintain one file) or do I have to caution
the user base to use a version aligned to their version of Excel (and
distribute .xls and .xlsm versions)?

Ways and means to isolate the unfortunate incompatibilities inside
modules that are only called after checking that you are running the
right version of Excel have been discussed here before. But if you use
any significant number of graphical objects you will go quite quite mad
trying to have one version that works on both platforms.

Early versions of XL2007 had a number of race conditions in charting
that made it almost impossible to run old XL2003 VBA code on it even if
the code was notionally correct. That is you could have code which would
work on 2007 if you stepped through in the debugger or added judicious
waits, but put it on a faster PC and all hell broke loose.

I chose to fork the distribution to retain my sanity.

 

[GS]

Art,
I was following your original post and IMO the problem is raised when
your XL2003 file was saved to the later version. It could have just as
easily been saved as the early version so it continues to work in both
environments. For example, all my addim projects are XLAs whether they
run in early or late versions of Excel. This is doable because addins
rarely get saved during runtime. Rather, the workbooks they act on get
saved in whatever version the addin runs in. The result is there's no
forward/backward compatibility issues with the VBA project.

Is there any reason why you can't distribute the VBA part of your
project separately from the workbook part it acts on? Normally, addin
solutions will always start out using their own pre-design workbook
templates, and continue in that context each time a user makes a new
file based on the pre-designed template. The addin would normally
handle the template implementation, but if your project doesn't require
a pre-designed file to start with then your even better off to not need
a template. Thus, your VBA solution can act on any workbook the user
chooses.

Going this route will require that you revise your project such that
it's 'version-aware', and so runs code appropriate for the current host
version of Excel. This is a trivial concern since there's really not a
whole lot that's different about core functionality, and so focus needs
only to include extended features in the later versions. IMO, this
approach is much better than having to manage and maintain 2 versions
of your VBA project.

In a nutshell.., I'm trying to convey the idea of separating the
'programming tier' of your solution from the 'business tier' that it
processes!

 

[cubbybear3]

I don't know if this is stuff you already know or not, but the xl2007
extensions now have meaning.
*.xlsx=NO macros;
*.xlsm=Has macros.

One problem I had when going from 2003 to 2007 is I had made the
mistake of using the controls toolbar for buttons in 2003. After
converting them to the Forms toolbar in 2007, they worked fine.

Also, when in 2007 and saving as a 2003, make sure you do "Save as
xl97/xl2003".

HTH.

-pb

 

[GS]

cubbybear3 explained :

I don't know if this is stuff you already know or not, but the xl2007
extensions now have meaning.
*.xlsx=NO macros;
*.xlsm=Has macros.

One problem I had when going from 2003 to 2007 is I had made the
mistake of using the controls toolbar for buttons in 2003. After
converting them to the Forms toolbar in 2007, they worked fine.

Also, when in 2007 and saving as a 2003, make sure you do "Save as
xl97/xl2003".

HTH.

-pb

I rarely use worksheet controls but when I do it's usually Forms
controls. Mostly, I use custom menus/toolbars. Using early XLAs to run
VBA in either environment has no bearing on how my project workbooks
get saved. The point I was attempting to convey here is that if the VBA
is separate from the files the macros act on (termed 'project files' in
Excel developer land<g>) then there's no compatibility issue at all!
The result of my suggestion produces workbooks with no VBA macros and
so the SaveAs process is much simpler because Excel processes how the
file gets saved in its normal fashion same as if you opened a new
workbook and saved it. It's when workbooks being saved include macros
that the issue arises! Ergo.., no code, no compatibility issues!!!

It's imperative that, as you say, we make sure our early files with VBA
get saved as early files if we want them to continue to work in early
versions. In cases where my projects use code in the same workbook that
code acts on, the project manages the save issue via the Auto-Close
procedure I use in place of the Workbook_BeforeClose event. This is a
more convenient way to handle changes to the file so shutdown is a
smooth and graceful process without any nagging prompts. That's made
possible by hooking all of Excel's shutdown/close events so they run my
code in place of the built-in processes.

Another concern with distributing workbooks with VBA is the macro
security. Most corporate clients of my run Group Policy over their
networks to prohibit their staff from opening any files with macros
that are not in the Trust list. Addins don't suffer this anoying
inconvenience because they are handled by the Addins Manager and so do
not fall under macro security scrutiny.

 

[Don Guillett]

cubbybear3 explained :




I rarely use worksheet controls but when I do it's usually Forms
controls. Mostly, I use custom menus/toolbars. Using early XLAs to run
VBA in either environment has no bearing on how my project workbooks
get saved. The point I was attempting to convey here is that if the VBA
is separate from the files the macros act on (termed 'project files' in
Excel developer land<g>) then there's no compatibility issue at all!
The result of my suggestion produces workbooks with no VBA macros and
so the SaveAs process is much simpler because Excel processes how the
file gets saved in its normal fashion same as if you opened a new
workbook and saved it. It's when workbooks being saved include macros
that the issue arises! Ergo.., no code, no compatibility issues!!!

It's imperative that, as you say, we make sure our early files with VBA
get saved as early files if we want them to continue to work in early
versions. In cases where my projects use code in the same workbook that
code acts on, the project manages the save issue via the Auto-Close
procedure I use in place of the Workbook_BeforeClose event. This is a
more convenient way to handle changes to the file so shutdown is a
smooth and graceful process without any nagging prompts. That's made
possible by hooking all of Excel's shutdown/close events so they run my
code in place of the built-in processes.

Another concern with distributing workbooks with VBA is the macro
security. Most corporate clients of my run Group Policy over their
networks to prohibit their staff from opening any files with macros
that are not in the Trust list. Addins don't suffer this anoying
inconvenience because they are handled by the Addins Manager and so do
not fall under macro security scrutiny.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

If you desire, send me your xl2003 version with this msg and I'll take
a look on xl2003>>>

 

[c1802362]

Garry,

its only getting more frustrating. I've solved one issue, namely I can
get the Excel 2003 file with the menus and code as an add-in to work
flawlessly on either Excel 2003/XP or Excel 2007/win7. The user
version will be a template file. Unfortunately, the problem is now
that I don't have a location on our corporate network that will let me
store the add-in for everyone's use (ya, I know, but this is a big
company with lots of security) and my user base has too many neophytes
who won't be able to follow add-in installation instructions if I ship
them the .xla file.

So, I think the workaround is to carry two versions of the code (2003
and 2007) and warn the user base about which one to use until the
entire system is migrated to one version of excel.

Art

 

[GS]

Garry,

its only getting more frustrating. I've solved one issue, namely I can
get the Excel 2003 file with the menus and code as an add-in to work
flawlessly on either Excel 2003/XP or Excel 2007/win7. The user
version will be a template file. Unfortunately, the problem is now
that I don't have a location on our corporate network that will let me
store the add-in for everyone's use (ya, I know, but this is a big
company with lots of security) and my user base has too many neophytes
who won't be able to follow add-in installation instructions if I ship
them the .xla file.

So, I think the workaround is to carry two versions of the code (2003
and 2007) and warn the user base about which one to use until the
entire system is migrated to one version of excel.

Art

Yep.., corporate nonsense drives me nuts too. It's as if they 'think'
they're doing **TOP SECRET** stuff and nobody can do anything without
whatever level of access! It's pretty much a no brainer to create 1
common folder that everyone can access, or better yet, assign a folder
for this use that's covered by network Group Policy.

Even if you do get given a folder to store the addin, it's still going
to be a nightmare making sure everyone installs it properly so they all
use the copy on the server. By default, the Addin Manager will try to
copy it to the local machine so everyone could end up with individual
copies anyway. Might be better to put the addin & template in a
self-extracting utility (zip/exe) that extracts its contents to the
same place on everyone's workstation. (Preferrably C:\Documents and
Settings\<user name>\<addin name>) You can use a Windows utility named
"iexpress.exe" to do this. It's located in the '~WINDOWS\System32\'
folder.

To use iexpress:
Click the 'Next>' button when it opens;
Select Option2 (Extract files only) in the wizard;
Complete the rest of the wizard as desired;
Distribute resulting EXE to users.

When users run the EXE, they are prompted for where to extract the
files. There's no provision for creating a folder on-the-fly so if a
special dedicated folder is desired then the folder must already exist
before running the extractor. This is the best way when users need to
install addins themselves via Excel's Addins Manager because they can
simply browse to the location they just extracted the files to and
select the XLA. Once the addin is installed, updates are a simple
matter of redistributing a revised version of the project. Users must
not have Excel open when the XLA is overwritten, -OR- they must uncheck
it in Addins Manager (so the XLA is not in use) before updating with
the new version.

Note that any custom menus/toolbars you create will be assigned to the
Addins tab for versions of Excel using the MSO Ribbon.

 

[c1802362]

Garry,

thanks for the advice and the shoulder to cry on

Art