Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Poole, UK
    Posts
    95
    Thanks
    22
    Thanked 2 Times in 2 Posts

    Using macros in Excel 2003 and 2010 and vice-versa!

    I wonder if someone who uses both Excel 2003 and 2010 may be able to help with this question please...?

    In our small office team, we use a number of fairly complex spreadsheets which include numerous macros. At the moment, our Windows pcs run Office 2003. It is vital that we can run copies of all the spreadsheets on all the pcs. One of the pcs is designated as the master pc and does most of the updating etc, then we take copies to run on the other (slave) pcs.

    We are about to get a new pc which will come with Office 2010 (64 bit?) installed and will probably become the new master pc. I gather we should be able to get the existing macros to work on Excel 2010 (they may need some code updating) but my question is whether we shall then be able to copy the files back to the pcs still running Excel 2003 and run them without mishap there?

    Or, are we likely to end up being forced to upgrade the existing pcs to Office 2010?

    On a related matter, some of the existing macros run on toolbar buttons. I know Excel 2010 has the new ribbon interface but presumably one can use the Quick Access Toolbar to create such macro buttons?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Victor,

    The trick is to set the default file type on the 2010 installation to .xls. This way the copying will be no problem. You will have code problems especially if the Excel VBA is calling libraries for other Office products. If this the case beyont checking for versions in the code to handle things like row/column limit differences you'll need to make all use of Office objects "LATE BINDING".
    Post back if you have more or specific questions.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Poole, UK
    Posts
    95
    Thanks
    22
    Thanked 2 Times in 2 Posts
    RG

    Many thanks for that. I had thought of doing that but then read something about having to save files in Excel 2010 as macro enabled (.xlsm) in order for macros to run properly.

    Any thoughts on the macro button issue?

    Will do some research on late binding.

    V

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Victor,

    I don't generally use macro buttons but rather custom menus. Excel 2003 menus will work in 2010 they just show up on the Add-ins tab which means an extra click. I haven't gotten into customizing the Ribbon yet since I'm retired there is no great urgency but I may get to it one of these days. Maybe someone else here who is more actively employed can shed some light on the subject.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can't do ribbon customisation if you are keeping the workbook as an .xls - you need one of the new format files for that. As RG said, your existing buttons will appear on the Addins tab in 2010 anyway.
    You mentioned 64bit Office - I would strongly recommend not installing that and sticking with 32bit Office, even if you are using 64bit Windows. Even Microsoft do not recommend using 64bit Office unless you have a specific need to work with very large files.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Victor,
    I jump from Office 2010 at home to Office 2003 with Spreadsheets heavily saturated with VB coded subroutines/functions. Never had a compatability issue as most code is backwards compatable. I have experience only issues with 2 controls that were not supported by 2010: mscal.ocx calendar activex control and spinbutton. It was a simple conversion/replacemnt that I had to do. I do agree with rory about the 64 bit version of office. Most add-ons and and extensions are 32bit. For example, you cannot run 32bit flash in 64 bit Office as ii is not supported.

  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Poole, UK
    Posts
    95
    Thanks
    22
    Thanked 2 Times in 2 Posts
    Many thanks for all the help and advice. Looks as if I should be ok as long as I stick to the 32 bit version of Office, and set the default save option as .xls.

    V

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    The biggest pain I had when upgrading from 2003 was Microsoft removing Application.Filesearch from vba.
    From 2007 onwards you have to use the FileSystemObject instead.
    Although I very much like the fso, it would have been nice and less trouble if Application.Filesearch was still available for compatibilty.
    I had to rewrite loads of vba routines to convert from 2003 to 2010 to deal with this particular issue.

    zeddy

  9. #9
    Star Lounger
    Join Date
    Dec 2009
    Location
    Poole, UK
    Posts
    95
    Thanks
    22
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by RetiredGeek View Post
    The trick is to set the default file type on the 2010 installation to .xls.
    Sorry to come back on this, but I had a go with a friend's pc yesterday which runs Office 2010 (edited).

    When I opened my existing (Excel 2003) .xls spreadsheets in Excel 2010, the macros were disabled until I saved them as macro-enabled .xlsm files. Is there a (security?) setting which will enable me to keep them as .xls files and still run the macros as normal?

    If not, I guess we'll have to run the spreadsheets on the master pc as .xlsm files, and run them on the slave pcs as .xls copies.

    Thanks,

    V
    Last edited by Victor Delta; 2013-01-10 at 15:56.

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Victor,

    Where did you load the spreadsheets from? If it was not a TRUSTED LOCATION and when you saved them as .xlsm files you saved them to a different location that was TRUSTED it would account for the problem. Office 2012? Do you mean 2013? I don't have that version so I can't test it.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    Star Lounger
    Join Date
    Dec 2009
    Location
    Poole, UK
    Posts
    95
    Thanks
    22
    Thanked 2 Times in 2 Posts
    RG

    Many thanks for that (and apologies, it should have read Office 2010, hence the reference to Excel 2010. I'll edit the post if I can).

    Since I was using a friend's pc, I opened the files from a USB memory stick and then saved the .xlsm files to the same (untrusted?) location. Presumably, from what you say, you can specify trusted locations and then opening xls files from such locations will not automatically disable their macros etc?

    V

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Victor,

    That is correct. In fact my Personal.xls file has been kept in the old format because I use it on a dual boot machine that has Office 2010 on one OS and Office 2003 on the other OS and it works just fine on both. I also keep all of my regularly used workbooks in the old .xls format just for that reason and the work just fine on either version of Excel.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    Star Lounger
    Join Date
    Dec 2009
    Location
    Poole, UK
    Posts
    95
    Thanks
    22
    Thanked 2 Times in 2 Posts
    RG

    Great. Many thanks for clarifying that.

    V

  14. #14
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    We are about to get a new pc which will come with Office 2010 (64 bit?) installed and will probably become the new master pc. I gather we should be able to get the existing macros to work on Excel 2010 (they may need some code updating) but my question is whether we shall then be able to copy the files back to the pcs still running Excel 2003 and run them without mishap there?
    Unless you order it with 64 bit Office, your machine will probably come with the 32 bit edition.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •