Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Measuring remaining free memory within Excel (xl97,xl2k)

    I've had a couple of xl applications in which quite a few charts needed to be created via VBA from data on related worksheets. Sometimes, as has been noted by others already, Excel runs out of available memory before completing all of a large number of charts.

    It would be helpful to the application if the amount of memory remaining could be monitored and a graceful stop could be made before crashing the program because it was out of memory.

    Is there any way of getting a valid measure of remaining Excel memory via VBA ?

    TIA,
    ~~~Paul

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Measuring remaining free memory within Excel (xl97,xl2k)

    How about the "MemoryFree" property?

    Application.MemoryFree
    <hr>Returns the amount of memory that's still available for Microsoft Excel to use, in bytes. Read-only Long.<hr>

    Steve

  3. #3
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Measuring remaining free memory within Excel (xl97,xl2k)

    I may be mistaken, but I think I saw some time ago that the MemoryFree call did not in fact provide a valid value. Has that been fixed?

    ~~~Paul

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Measuring remaining free memory within Excel (xl97,xl2k)

    I had not heard this before (though I have never used the memoryfree), but a google search gave me a reference which confirms what you recall:
    <hr>Unfortunately memory available or free does not work properly and always shows a constant 1 megabyte available. Consequently total memory, which shows the sum of memory used and memory free, also does not work properly.<hr>
    It does point to an API call method which can be used to get it.

    Steve

  5. #5
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Measuring remaining free memory within Excel (xl97,xl2k)

    Thanks for researching this for me, Steve. Reviewing some of my past efforts, I HAD explored the Windows API memory-info routine you suggested, but it (again) only reaffirms that there is a LOT of memory there, but (evidently) not available within Excel where I need it.

    Cheers!
    ~~~Paul

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Measuring remaining free memory within Excel (xl97,xl2k)

    Perhaps there is a "workaround" to your problem that just prevents excel from needing so much memory.

    If the problem comes when you create too many charts there are several options:
    create less charts:
    Could the charts be created to be interactive? Instead of creating dozens of "identically setup charts" that only differ in data, create 1 chart and use formulas or a macro to change the data source or where the source gets its data.

    I have used this technique to create 1 chart of cost data over time. The chart had several comboboxes: Year to plot (X), line item to plot(Y) and the chart type (there were 4 ways to represent the data). The 1 chart was literally over a thousand distinct chart possibilities. The pulldowns just affected the data used to plot and different data was grabbed based on the comboboxes using INDEX function in a database.

    You could also create each required chart at runtime when it is requested.

    There are other possibilities but we would need to know more about your setup to give other suggestions.

    Steve

  7. #7
    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: Measuring remaining free memory within Excel (xl97,xl2k)

    In my experience, the main reason for this error when automating chart creation is due to the 'auto scale'. I found that I could create as many charts as I needed if I disabled this item (which is enabled by default). Try that first before venturing into the other solutions offered.
    <pre>Dim cht as ChartObject
    set cht = activesheet.chartsobjects("chtName")
    cht.chart.chartarea.autoscalefont=false</pre>

    Do when you first create the chart else all other items will have their auto-scale parameter set to true as well

    Deb

Posting Permissions

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