+ Reply to Thread
Results 1 to 4 of 4

I've created many charts using VBA - workbook becomes so painfully slow, it hurts.

  1. #1
    Registered User
    Join Date
    07-05-2013
    Location
    pooville
    MS-Off Ver
    Excel 2003
    Posts
    11

    I've created many charts using VBA - workbook becomes so painfully slow, it hurts.

    I've created charts that plot a big block of monthly data. In my worksheet columns, I have the months listed from jan 2000 to dec 2010, i.e. around 132 months. Each column contains data to be plotted for each of these 132 months, around 100 data points. I need to create a line chart of these 100 data points (i.e. going down each column), and then I need to do this for every month, i.e. 132 charts.

    I wrote some code that does exactly what I need to do, i.e. loops thru each column and plots a chart for that column's data series. I also set a whole bunch of properties for each chart (axis format etc), and get each chart positioned exactly how I want. So far so good. I haven't included the code, because it works fine.

    The problem is that once the 132 charts have been created, the spreadsheet becomes large (20MB), and becomes so slow, I essentially can't use it. Seriously, I can barely touch the sheet without it having a fit. If I try to flick between worksheets that contain graphs, each graph is recontructed point by point before my eyes before I can do anything. Same thing happens if I try to copy a worksheet, set print area, scroll the worksheet - anything I do, even select a cell, and all the charts disappear, and are then reconstructed point by point. I find that most of my CPU is used by Excel when I try to do anything.

    It's such a shame, because the code works fine, and I have done what I need, but I can't use the result.

    I have investigated - apparently Excel 2007 (my version) is known to be slow with charts in general (rendering, apparently). So as a workaround, I tried running the code in Excel 2003. The charts that get created are definitely faster to view and scroll etc, however, after creating about 30 or so charts, the code crashes, and I get an error (no new fonts may be applied in this workbook). I have looked into this limitation of Excel 2003. For some reason, Jon Peltier's page on this can't be loaded

    http://peltiertech.com/Excel/Charts/FixFonts.html

    The MS support site recommends that I set the autoscale of fonts feature to FALSE. This has had no effect for me.

    In short, I am stuck in 2007 cuz the workbook becomes too slow to use. I am stuck in 2003 because of the "no new fonts" error.

    Any help would be gratefully received.

    Thanks,
    I.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: I've created many charts using VBA - workbook becomes so painfully slow, it hurts.

    Why don't you save the charts on separate workbooks?
    You could have tabs on your master workbooks that have the same name as your chart workbooks
    Then a workbook macro that runs whenever you activate a new tab
    And that macro opens the chart that you selected.
    That charts has two tabs, the chart and the master sheet
    Selecting the master closes the chart sheet and selects the master workbook.
    Should be quite easy to code.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    07-05-2013
    Location
    pooville
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: I've created many charts using VBA - workbook becomes so painfully slow, it hurts.

    Hi Mehmet, thanks for the suggestion, and for taking the time to reply.

    I actually did something that goes some way towards what you suggested. In my code, I arranged the charts to appear on a different worksheet for each year, i.e. i wrote a loop that created worksheets called "2000," "2001," ... , "2010." Then I placed the twelve corresponding monthly charts on each of these sheets, i.e. Jan 2010, Feb 2010, ..., Dec 2010 charts went into worksheet "2010" and so on.

    I then copied each of these worksheets into new workbooks by hand. I figured by splitting so many charts into smaller groups, the whole thing wouldn't be so slow.

    Actually, it barely makes a difference. Even after I've copied each worksheet of 12 charts into a new workbook, the resulting workbook is still very slow (e.g. takes 10 minutes just to set print area etc.).

    So to be honest, I'm still a bit stuck. I can't figure out why a relatively small number of charts seems to slow Excel down so much.

    Thanks again for your advice.

    I.

  4. #4
    Registered User
    Join Date
    07-05-2013
    Location
    pooville
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: I've created many charts using VBA - workbook becomes so painfully slow, it hurts.

    Update - I actually figured out the root cause of the problem. It was a problem with my coding - the code did what I wanted (scan across columns, and plot the data in each column), but it did so in a very inefficient way.

    Reason - each columns has a different number of data points, so my code first figured out the number of valid data points in each column, and then plotted these as a series. originally, my code scanned down each column to see where the first empty cell was, and then plotted only those cells populated with data. unfortunately, i had my create chart object at the wrong point in the loop, so i was creating a chart after going down each cell in a column. this was unnecessary, i.e. multiple times per column. this was unnecessary, i only had to plot the chart once for each columns after figuring out how many valid data points there were. so all it took was a reordering of the process, to ensure a chart is plotted only once for each column.

    after making this change, the excel workbook stopped becoming painfully slow, and everything is now sorted.

    thank all for your help.

    I.
    Last edited by eiasbk; 05-26-2015 at 06:06 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel Painfully slow, how do you speed it up?
    By sgrondines in forum Excel General
    Replies: 3
    Last Post: 02-04-2014, 07:02 AM
  2. [SOLVED] Wait for Workbook to be created from outside program and then continue once created
    By ggilzow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2013, 02:41 PM
  3. Formatting Excel charts created with VBA
    By neil@invidion.co.uk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2006, 07:25 AM
  4. Extra series in charts when created using vba
    By JacksonRJones in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2006, 07:30 AM
  5. Painfully simple... help?
    By bhofsetz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2005, 03:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1