Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Upgrading macros to 2007 (2003)

    We are about to upgrade to Office 2007 from 2003 and have loads of Macros that have been written as well as recorded. Are we likely to suffer greatly when opening these old spreadsheets?
    Neil Eustice
    Woody Worshipper

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Upgrading macros to 2007 (2003)

    Most macros should work OK. If you have macros that manipulate menus or toolbars, they may not work as intended, because Office 2007 has replaced menus and toolbars with the Ribbon.

  3. #3
    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

    Re: Upgrading macros to 2007 (2003)

    Additionally, if you use Application.Filesearch in your macros, that doesn't exist in 2007.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upgrading macros to 2007 (2003)

    Great. Thanks for the quick reply
    Neil Eustice
    Woody Worshipper

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upgrading macros to 2007 (2003)

    Very useful, I will include that tip in our information to our staff.
    Neil Eustice
    Woody Worshipper

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upgrading macros to 2007 (2003)

    Like said: most will work.

    One thing to take into account is the # of rows and columns. If you have code that points to row 65536 and/or column 256 (IV) you may want to change that.

    Note also that activesheet.cells.count yields an error because the .count property is a long. It will cause an overflow error (sheets in 2007 contain more cells than the max value of a Long).
    You need to use Activesheet.cells.countLarge instead.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upgrading macros to 2007 (2003)

    Another great tip! I will add this to my growing list of what to check for. One question; You say if we have code that points to row 65536 or column 256 (IV) we may want to change it. Presumably this is only if we have used that as an end mark to ensure we have captured all data or similar? You mean this is because the rows and columns now have more than before, rather than they are addressed differently? If we have actually put something in that row or column then we could still refer to it by address.
    Neil Eustice
    Woody Worshipper

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Upgrading macros to 2007 (2003)

    Programmers often use code like this to find the last used row in a column, for example in column C:

    Dim lngLastRow As Long
    lngLastRow = Range("C65536").End(xlUp).Row

    This will still work correctly in Excel 2007, but a worksheet now has 1,048,576 rows instead of 65,536. If the data in your worksheet expand over time beyond row 65,536 the above code will not yield the correct answer any more. You can work around this by using

    lngLastRow = Cells(Rows.Count, 3).End(xlUp).Row

    which will work correctly in all versions of Excel, regardless of the size of the worksheet.

    Similar for columns: to find the last used column in row 37, programmers often used

    Dim lngLastCol As Long
    lngLastCol = Range("IV37").End(xlToLeft).Column

    This can be changed to

    lngLastCol = Cells(37, Columns.Count).End(xlToLeft).Column

  9. #9
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upgrading macros to 2007 (2003)

    Excellent - thanks again.
    Neil Eustice
    Woody Worshipper

Posting Permissions

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