Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Durham, North Carolina, USA
    Posts
    140
    Thanks
    5
    Thanked 1 Time in 1 Post

    Not enough memory error (Excel 97 VBA)

    Howdy.

    I'm trying to de-activate Font AutoScale for the Chart Area of every chart in a workbook. The following code works fine with a some workbooks, but one book (too big to attach) that has a LOT of charts and sheets causes a Not Enough Memory error. Any suggestions?

    TIA, Beej

    Sub Turnoffautoscale()

    Dim ws As Worksheet
    Dim chObj As ChartObject

    For Each ws In ActiveWorkbook.Worksheets
    For Each chObj In ws.ChartObjects
    chObj.Activate
    With ActiveChart.ChartArea
    .AutoScaleFont = False
    End With
    Next chObj
    Next ws

    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not enough memory error (Excel 97 VBA)

    I don't see why that is happening, and without the workbook it is a little difficult to test. Here is one thing to try:

    <pre>Sub Turnoffautoscale()

    Dim ws As Worksheet
    Dim chObj As ChartObject

    For Each ws In ActiveWorkbook.Worksheets
    For Each chObj In ws.ChartObjects
    chObj.Activate
    With ActiveChart.ChartArea
    If .AutoScaleFont Then .AutoScaleFont = False
    End With
    Next chObj
    Next ws

    End Sub
    </pre>


    If that does not work, can you tell if the routine is doing some chart objects and then failing, or is it failing immediately? Does it always fail on the same object. You might want to put in a couple of statements to save the name or index number of the object and an On Error statement that will allow you to see what object it is failing on.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not enough memory error (Excel 97 VBA)

    Don't know if it will make a hill of beans of difference, but I spotted just a bit of inefficiency in your code.

    Replace:
    chObj.Activate
    With ActiveChart.ChartArea
    .AutoScaleFont = False
    End With

    with:

    chObj.Chart.ChartArea.AutoScaleFont = False
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not enough memory error (Excel 97 VBA)

    AutoScale is a HUGE memory hog and I have disabled it on my Excel project. Since you have it on for all charts and now run out of memory while trying to turn it off, you might need to remove some charts then selectively turn off AutoScale for the rest. The reasoning is that you'd be freeing up some memory such that you can turn off the feature and then slowly add back your other charts.

    I had this problem too and was told by a few people (and read it on a few other Excel sites) about the memory problem.

    Deb <img src=/S/catty.gif border=0 alt=catty width=19 height=18>

Posting Permissions

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