Translating Excel 2003 Macros to Excel 2007

Posted on 2007-10-03
Last Modified: 2012-05-05
I currently have many Macro's written in Excel 2003 that I'm testing in Excel 2007.  Most of the macros add chart objects and begin by deleting all of the current charts using - ActiveSheet.ChartObjects.Delete,
2 questions 1.  What would the translation of the ActiveSheet.ChartObject.Delete command be and
2.  Is there any place / references that help in converting Excel 2003 Macro's to Excel 2007.  
Question by:bbroehm
    LVL 85

    Expert Comment

    by:Rory Archibald
    For the most part, as far as I know, the macros should be forward-compatible. The chart engine was completely revised for 2007 though so you may have some issues with those and you will find that the macro recorder does not work with charts in 2007! The only other thing I know of is that Application.FileSearch is discontinued.
    I'm not in front of a machine with 2007 on at the moment to test, but are you getting errors with Chartobjects.Delete?
    LVL 46

    Expert Comment

    by:Wayne Taylor (webtubbs)
    HI bbroehm,

    Have you tried that code in XL2007? As far as I know, it should still work.

    Note that it should be ActiveSheet.ChartObjects.Delete, not ActiveSheet.ChartObject.Delete (note the plural ChartObject).


    LVL 12

    Expert Comment

    Question 1:

        ActiveSheet.ChartObjects("Chart 2").Delete

    Question 2:
    This will show you what the new changes in Excel 2007 are:

    Most macros should work but there are object changes that you may need to tweak.

    Author Comment

    A clarification I grabbed a snippet of code with the actual excel error *****'d about 10 lines down .  This code works fne in excel 2003 but not in excel 2007.  The excel error Run time error '1004'  Application defined or object defined eror.  Note the

        ActiveSheet.Cells(7, 1).Select
        'ActiveSheet.Cells(7, 1).Select
        ActiveSheet.ChartObjects.Delete  *********
    LVL 12

    Expert Comment

    You have to identify the Chart Object to Delete.
    If the ActiveSheet has only 1 chart on it then you can select by index:

    If you are deleting multiple charts on a single sheet:

        For a = 1 To ActiveSheet.ChartObjects.Count
        Next a
    LVL 12

    Accepted Solution

    I tested your line:

    In both versions successfully.
    The reason you may be getting the error is that the charts were created in a previous version of Excel than 2003 and they are not being recognized as charts.  The error messge you are getting means that the object, in your case, ChartObject, does not exist.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now